For query1 it should be SELECT p.name, and for query 2 it should be SELECT parent.name
I’ve amended my previous post.
At this moment nothing, but it might be needed for later use. Plus, we need at least one column to be unique for referencing purposes. (well, not strictly so, but it’s very handy nonetheless)
Just put it in there multiple times, no problem.
people
id | name | blood_type
------------------------
1 | John | B
2 | Kennth | A
children
id | parent_id | name
---------------------
1 | 1 | Oliver
1 | 1 | Lich
1 | 2 | Oliver
1 | 2 | Lich
In that case John has two children named Oliver and Lich, and Kenneth also has two children named Oliver and Lich.
There is another solution in which you only need to store unique child names once to save storage, but given how dirt cheap disk space is now, plus that it’s less semantically correct, I really wouldn’t opt for that.
Because you don’t know if they have the same child, or a different child who happens to have the same name. When using separate tables this distinction can be made. When using a many-to-many table however it can not.
that method works fine, but it’s needlessly complex
it would store each child name once, right? so that automatically means a separate name table, and then the actual child data row (in its parent-child table) would use a FK to get the name, but it would still have it’s own unique PK separate from the name