Is it valid to have a parent table have a reference to a child table?
For example, a table Parents has a child table Children. Children has a foreign key Parent_ID linking back to its parent. But Parent has another column (say favorite_child_id) that is a foreign key back to the Child table. Is this acceptable or is it bad database design to have two tables essentially referencing each other?
Advertisement
Answer
This is not unusual and is a reasonable solution. A common occurrence, for example, are contacts for a company. One of the contacts might be designated the “primary contact”.
The only issue is that you cannot declare both favorite_child_id to be nNOT NULL and child.parent_id to be NOT NULL. If you do so, you are in a situation where you cannot add values into the table.
An alternative is to have an “is favorite” flag in the child table. It can be tricky to ensure that exactly one child has that flag set.