Relating People to other People in the same table?

397
1
11-01-2018 01:58 PM
AwesomeEvan
Occasional Contributor

I have a table populated with people names and contact... can I relate people in this table to other people in this table.

I would guess I would relate the table to itself with an intermediary table that stores the relation type (brother, mother, cousin), but this doesnt seem possible to have the origin and destination the same table for either a 1-M or M-N.

What would be the schema for relating a list of people to each other?

0 Kudos
1 Reply
DanaNolan
Occasional Contributor III

This sounds like a recursive relationship with a twist. A simple recursive situation would be an employee and managers, since managers are also employees. So, if you are interested, try looking for how people deal with these.

But in my experience, database managers don't support this relationship fully. They are included in data models for documentation purposes but don't make it to schemas.

In a database tracking a company's subsidiaries, if you have an owner/parent company its ID may or may not already be in your file and that's probably OK if, for example, you don't want to completely track parentage outside your organization. If it is an internal parent, then you would want to ensure you used a consistent ID (foreign key) for the parent. You can enforce these constraints: after data entry by querying to check if all parent field values are in the table; or during data entry to give the user a warning if the parent is not in the table. The first choice is possible in any database software (you may need a second copy/view of the table because you are essentially joining it to itself), while the second may not be possible without some coding.

Your situation has a twist in that the relationships could be redundant and overlapping since if you track the sibling, the parent and other relationships are implied. So you need to think about how to deal with that hierarchy. 

0 Kudos