Which SQL statements to execute with intersection / junction tables
- by user1455103
Here a simplified database layout
One condo can hold multiple properties (flats, garage boxes, etc) -
1->n relationship
One owner can have multiple properties in the same condo and properties can have more than one owner (m->n changed to 1->n with
the junction table)
One condo can have multiple owners - 1->n
Some additional clarification:
A owner is a member of a condo. A condo is made of properties belonging to owners BUT a owner is not linked to a property directly (there can be no relation between a property and a owner for a certain time BUT there will ALWAYS be a relation between a owner and a condo). Reason for this: the agent managing the condo will first create a list of owners and a list of properties. It is only later thet he will "link" each property to one or multiple owners (or inversely)
I'm quite new to SQL.
What SQL statements should I execute to:
SELECT, for a specific condo (WHERE condition), the properties and their respective owners (all properties should be listed even if owners are null)
SELECT, for a specific condo (WHERE condition), the owners along with their properties (all owners should be listed even if properties are null)
UPDATE / DELETE existing owners (I'm uncertain about how to handle the operation for the junction tables. Should I first check if there is an entry in the junction table or not ?)
UPDATE / DELETE existing properties (same concern)
INSERT new owners (should I use two different SQL statements depending if the owner should be linked to a property or NOT - IF condition ?)
INSERT new properties (same question as above)
Could you be as clear and generic as possible so that it can be reused ? :-)
