sql - How to make a normalized table of adults and children? -


adults may have 0 or more nephews , nieces. children may have 0 or more aunts , uncles. how can model normalized if adults , children stored in 1 table?

this test question got wrong , clarification on. in advance!

if that's similar how question worded, i'm wondering if it's partially trick question.

all adults , children stored in 1 table

that say, of people's information needs in 1 table, relationship between them not have be?

person --- personid pk gender dateofbirth firstname lastname  personrelationship --- id pk -- not needed, simple primary keys personid fk -- elder descendantid fk -- youngling 

find nieces/nephews of specific person:

select * person p inner join personrelationship pr on p.personid = pr.personid inner join person descendant on pr.descendantid = descendant.personid p.personid = 1 

find aunts/uncles of specific person:

select * person p inner join personrelationship pr on p.personid = pr.descendantid inner join person ancestor on pr.personid = ancestor.personid p.personid = 3 

so example data have.

person 1, 'm', null, 'uncle', 'tom' 2, 'f', null, 'aunt', 'danielle' 3, 'm', null, 'nephew', 'realname'  personrelationship 1, 1, 3 -- 1 ancestor 3, 3 descendant of 1 2, 2, 3 -- 2 ancestor 3, 3 descendant of 2 

Comments

Popular posts from this blog

python - No exponential form of the z-axis in matplotlib-3D-plots -

php - Best Light server (Linux + Web server + Database) for Raspberry Pi -

c# - "Newtonsoft.Json.JsonSerializationException unable to find constructor to use for types" error when deserializing class -