Re: Entity Overlap and Relationships
Date: 20 Apr 2006 13:25:15 -0700
Message-ID: <1145564715.536782.219630_at_z34g2000cwc.googlegroups.com>
GB wrote:
> 2. What is the best way to handle people-people relationships (from a
> schema perspective - I can see the sarcastic remarks now)? Has anyone
> solved this problem of how to store {father, son, uncle, friend, etc.}?
> Did you have to set up a trigger to update the table to include the
> son relationship entry if the father relationship was added for
> instance? Or would you just store the "parent" and "child" title for
> the relationship, in this case "father" and "son" then the related
> unique ID's for the people with the relationship?
table People (...)
table Parents (
mother_id integer, foreign key to people, null if unknown
father_id integer, foreign key to people, null if unknown
child_id integer, foreign key to people
)
The other relations -- sibling, uncle, etc are derived from the
Parents.
BTW, you don't actually need the concepts of entity and relationship
for designing a good database schema.