I always used database design using this pattern;
root table with PK id1 child1 table with PK id2 FK id1 child2 table with PK id3 FK id2 child3 table with PK id4 FK id3 etc...
so if i have to get id1 from child5 table I need to traverse all child tables up to the root one (multiple join)
select root.id from root inner join child1 ... inner join child2 ... inner join child3 ... inner join child4 ... inner join child5 ... where child5.id = X
is that "ok" as a design or there is way to optimize it?
I could see a few way to do it;
- bring the idX as a FK in the table that I expect, so I could modified (add the proper FK) down the line to any childX table as needed
- just add the FK as I create new table, this seem wasteful, ex; child5 table would have at mininum 4 FK (id1, id2, id3, id4)
with these options i could simply do;
select child5.rootid from child5 where child5.id = X
- is any of my option above is a good thing to do or there is a better way of doing this?
- is there any name given to this pattern (so i can search online, i can’t think of any)