Family Relation Tables in SQL

Transact-SQL
1

Hi everyone.
I have following table in SQL:

id person
1 Ben Parker
2 May Parker
3 Peter Parker

I am looking for some sort of coding in SQL that gives me following result:

id relationship
1 Ben Parker is the husband of May Parker.
2 Ben Parker is an uncle of Peter Parker.
3 May Parker is the wife of Ben Parker.
4 May Parker is an aunt of Peter Parker.
5 Peter Parker is a nephew of Ben Parker.
6 Peter Parker is a nephew of May Parker.

Can anyone guide me how many tables I will have to create and how to get the desired result??

2

You can't determine those relationships from the table given.

Do you mean you want to create a new table using stated, given relationships to load the new table? That can be done.

3

You can try something like this
Table - person (person_id, person_name) (ie 1, "Ben parker",2 ,"May Parker")
Table - relationship(rel_id,rel_name,rel_person,rel_person2) (ie. 1, "husband",1,2)
I think you can use a case statement and concatenate like
select concat( person_id,person_name,'is the ',rel_name,'of',rel_person2) ..

1 Like
4

dbo.persons (
person_id int NOT NULL PRIMARY KEY,
first_name varchar(50) NOT NULL,
last_name varchar(50) NOT NULL
)
dbo.person_relationships (
id int IDENTITY(1, 1) NOT NULL,
person_id_primary int NOT NULL FOREIGN KEY REFERENCES dbo.persons ( person_id ),
person_id_secondary int NOT NULL FOREIGN KEY REFERENCES dbo.persons ( person_id ),
person_relationship_code smallint NOT NULL,
CHECK(person_id_primary <> person_id_secondary),
PRIMARY KEY ( person_id_primary, person_id_secondary )
)
dbo.person_relationship_codes (
person_relationship_code smallint NOT NULL IDENTITY(1, 1) PRIMARY KEY,
relationship varchar(40) NOT NULL, /'spouse', 'aunt', 'uncle', 'nephew', .../
)

2 Likes
5

Ok this helps a lot but what would be the easiest way to print all the relationships?
I have to write code manually for each relationship or is there another way?

6

Can you please give an example of how to write case statements in this situation?
What is we add more family members in the list later then how many case statements will be there?

7

There's no easy way to plug in all the relationship types. You'll have to decide how much lineage you want (parent/grandparent/great-grandparent/further back) and how much spread (aunt/uncle, cousins, second cousins, step-parent, step-sibling, married vs. unmarried parents, adopted children, possibly intermarriage) and gendered (father/mother vs. parent, son/daughter vs. child).

Many of these relationships can be derived by working the lineage, e.g. grandparents, aunts/uncles, cousins, etc., but then you'd need to recurse over your tables for each generation. Step-relationships will complicate this too. @ScottPletcher 's design makes everything explicit, which is probably better overall, there's less guesswork and chance for ambiguity, at the cost of more data INSERTed. @ScottPletcher 's design also lets you model friendships, co-workers, and other relationships that might become important later.

The intermarriage thing, while it might sound icky, comes up a lot with royalty and other long-established families, where cousins marry and such. In those instances each pair can have multiple relationships with each other, and it's not just the ones getting married either. Recursing a lineage/hierarchy is probably not going to be enough. If you can find an ancestry database schema that can model European royalty from 1600 CE onward, that should cover anything you need to do.

2 Likes
8

You might also add start end datetime columns considering divorce rate these days. So a person can be married x times. :sweat_smile:

1 Like