question

Damus avatar image
Damus asked

How to Maintain Family relations in table

I need tables to store the blood relations of family. Here i need to store all the primary relations i.e,the subjects like father, mother, son etc in a table and in the second i need to store the relations among themselves. Now i will give a input to a procedure as X is Father of A. ie, relator1,relator2 and the relation as X,A,Father. If in the same table whenever i got another relation for A as B is spouse of A. Now i need to get the relationship between B and X as X is son of B. Kindly, guide me to do this. -- With Regards Damus
tablesrelationships
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image
TimothyAWiseman answered
You have a number of options here. Perhaps the most straight forward is to use two tables. In the first, store the name, an id, and any other information you want. The second will store the first id, the second id, and the relationship. If you are trying to use the database to enforce consistency, one complication with doing it that way is that some relations are symmetric, namely spouses, so a simple unique index will not prevent those relationships from being duplicated. But you can identify the symmetric relationships and use a check constraint to prevent B, A, Spouse from being entered if you already have A, B, Spouse Slightly more complicated is the non-symmetric but implied relationships. For instance you specifically mention Father as a possible relationship, but if A is B's Father then this implies that B is A's child. Again you have a couple of options for enforcing consistency. One is to turn this into a completely defined relationship (instead of permitting things like father and child, instead only permit the relationship parent-child). The other option is to create a trigger to auto-create any missing implied relationships. The option of limiting the relationship type choices is probably the better one, but of course it depends on your final use case.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
You could also simply go with a straight hierarchy structure. Use the hierarchy data type and build out the family that way. The relationship between spouses might be a bit difficult to model that way though. Another option would be to go with three tables, one that defines people, one that defines "families" (represented by one man & one woman), and a third that relates people as the offspring of "families." But you could end up with some nasty triangular joins on that one if you're not careful. The simple part is defining the individual. Then you need to define the spouse relationship and any offspring from that relationship. Once those three things are defined, everything else can be derived through queries.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jon Crawford avatar image
Jon Crawford answered

or, build it as a network, with relationships as edges between the nodes, and that way you can have multiple marriages, derive what your family is by the immediate proximity, and allow for those times when you have multiple people that *might* be the same but you're not sure yet.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.