Consider three tables:
Grandparent
Parent
Child
Each has its on key column (say, “id”).
The parent table has grandparentID column.
The child table has a parented column.
Now, certains operations (the sql) would be simpler if the child table also had the grandparentID column.
Is this a problem? Does it violate some how-to-make-nice-tables rule?
You may create Views and not duplicate data. A child view can expose both parent and grand parent
You could also make a table in between that links the two which only contains ids. If you still want a one to one relationship, just put a constraint on the table so that you can’t have two with the same ids in the same position.
There are many ways to achieve such hierarchy, but in all them having 3 tables seems the worst.
Without knowing about the problem, a person struct as
id, person_name, father_id (default NULL), mother_id (default NULL)
Says you if someone has parents and you can walk his entire ancestry.
Rick is correct.
Another way:
Table of PEOPLE, with ID,NAME,other stuff
Table of RELATIONSHIPS UID, PARENTID, CHILDID, (+ perhaps relationship type - fatherof,motherof,adopterof)
PEOPLE:
0,Grandad Smith
1,Grandad Jones
2,Grandmom Jones
3,Dad
4,Mum (maiden name Smith)
5,Uncle Brian
6, Milkman
7,Little Johnny
8, Little Jane
RELATIONSHIPS
1, PARENTID =1, CHILDID = 3 ,FatherOf (dads parents)
2, PARENTID =2, CHILDID = 3, MotherOf (dads parents)
3,PARENTID =0, CHILDID = 4 , FatherOf (mum’s parent)
6,PARENTID =6, CHILDID = 8 , FatherOf (Little Jane’s dad, but it’s a secret!)
No need of child id. This way you can only have one child. father_id covers all cases.
I’m id 500, my son is id 512, his father_id is 500, my daughter is id 517 but her father_id is also 500.
SELECT person_name WHERE father_id = 500; -- lists my children.
It is not recommended but it is not prohibited. It is usefull and makes things easier for you. Specially if you are talking about a hierarchy of not normalisable thing and not a literal ancestry.
Nope. We don’t know what’s his problem to say something like that.
Things starting wrong can be insanely hard to fix after.
His 3 fixed tables approach can introduce redundancy and future complexities.
Person 1 should exist in 3 of them, for example, as he is son, father and grandfather. This breaks one of such laws of good design.
You are assuming that each child has only one parent record. If these truly are people we’re talking about, they may actually need the ability to do many-to-many.
First, let me apologize. I seem to have implied that the project is about actual people.
The grandparent/parent/child relation is simply the relation of the tables. The contents could be anything.
Also, I have succeeded in getting the sql queries to work without having the “grandparentID” in the child table.
At least in my problem, a child has exactly one parent, and a parent has exactly one grandparent. The grandparent can have multiple rows in the parent table, and the parent can have multiple rows in the child table.
Finally, I’m always impressed at the responses and ideas on the forum. Thanks to everyone.
That is why I said “if you are talking about a hierarchy of not normalisable thing and not a literal ancestry.” instead of simply asume he was talking about people
Wrongly asumed you can mix the 3 entities.
And again. He simple talk about a Grandparent Parent and child TABLES, but those can represent MANY diverse not normalisable things.
No one should point out the best approach for the unknown.
That’s how i handle it and it served me well for over 10 years now.
I put “fixed” data into tables made just for this data and build relations between such tables using additional “relationsship tables”.
Pulling data from various tables at once is sometimes a tiny bit more work, but the “fixed” data is much easier to organize.