SQL database design

Question

I am making a database for a program where i am supposed to model some relations in the family. ex: X is father to Y , Y is son to X

So , i have a Members table with all info about each member so i thought about making a many to many relation between the Members table and itself so that the Member_Member bridge table will be having the columns "FK_FromID , FK_ToID" as composite key (is this correct ?) and "FK_RelationType" as a foreign key to RelationTypes table which will have the relation type "Father,mother, son,daughter" , and two relations going as one to many from the Members table to these two foreign keys

My problem is : when deleting if i choose cascading then i will make cycles because if i delete a member then there will be two delete passes to related records in Member_Member bridge , knowing that in the program whenever i insert a father relation i will insert a son relation as well in the Member_Member table , is there a way or a workaround possible to enable cascading so that whenever i delete a member i will delete the related records in Member_member regardless of being recorded in either a to or a from foreign key column

So, i don't know what to do , is this a correct design in the first place or what ? , what should i do about cycling , also what do you think a better design for the same problem should be knowing that i need to specify what kind of relation between the two parties

Thanks a lot for any help , and sorry for bad english Bishoy

Answer 1

SQL does not handle "network" problems like this very well.

The member-member bridge table is a terrible name. It's a "member-parent" (or "parent-child") bridge. Bridge tables should not have a "composite key". Bridge tables have a surrogate key (just a sequential number) and a pair of FK references to other tables. The two FK's should have names like "member" and "parent" to make it perfectly clear what the relationship is in this table.

Everyone has a parent. Not everyone has children. Some parents will not have parents in this database; they're the "top parents".

It's easiest if the top parents have rows in the parent-child bridge with a parent FK of NULL. That way you avoid super-complex outer-joins -- every member has at least one member-parent row; ideally two.

You will find many "cycling" issues since relationships are transitive.

Note that you can't -- in a single standard SQL query -- find all members of the family, or all parents back to the top of the family, or all children and grand-children. There are SQL extensions that make this possible, but the standard doesn't handle it well.

The cascading delete doesn't work out well because the relationships in a family are directed in two ways (parent to children, child to parents), but SQL only has one kind of direction (FK reference).

You can try to assure that every member MUST have at least one (and at most two) rows in "member-parent" and the delete of a member deletes the two rows in "member-parent" and the keys have the proper names, you might make parts of this work.

Note that when you delete a member, this will break their parent relationships. You delete the member-parent row. That's good. What about their children? The other rows which have member-parent referring to this deleted parent are now broken. What does this mean? What should be done?

There's no standard answer. Removing rows from a connected graph leaves elements unconnected. You have to work out some sensible rules for that.

Since SQL does not do this well, all designs will seem to have problems.

Answer 2

1. Assuming each member can only have one mother and one father, you would be able to derive all the relationships by simply keeping a mother_id and father_id field in the members table.

   However this would only work if your database will not have missing information. For example, if member X is the brother of member Y, there would be no way to know this unless the parents of X and Y are stored in the database.

   If your database will not be having missing information, the above might be easier to manage data integrity. Queries might get a bit complex however.

2. The member_member bridge you suggested has one serious problem since it implies a direction of the relationship, so that if X is the father of Y, Y is also the son of X. You suggested to define the relationship twice in both directions, but in general this is not recommended. This is a form of data duplication, and you can struggle to enforce referential integrity with duplication of data. Remember that the DBMS does not know that X is father of Y is the same as Y is the son of X.

I am aware that this is not a complete answer, but just a few observations. I totally agree with S.Lott's answer as in there is no standard way to "solve" this with relational databases.