0

Sorry, maybe it's a well known design principle, but because I don't know a name for this kind of relation, I was not able to find any existing answer.

So, let's say we have a parent which may have many children, but only one child can have some property active (or set). Think about properties like "selected", "current", "active", "default", etc. So, my question is where store this flag?

Variant 1 is to store it as child property, like this:

+---------+----------+--------------+-------------+
| ChildId | ParentId | SomeProperty | _IsCurrent_ |
+---------+----------+--------------+-------------+
|       1 |        1 | A            | False       |
|       2 |        1 | B            | True        |
|       3 |        1 | C            | False       |
|       4 |        2 | A            | True        |
+---------+----------+--------------+-------------+

Variant 2 is to store it as a parent property:

+----+--------------+------------------+
| Id | SomeProperty | _CurrentChildId_ |
+----+--------------+------------------+
|  1 | A            |                2 |
|  2 | B            |                4 |
+----+--------------+------------------+

And variant 3 is to store it in both tables.

So which variant is the right one? I always used variant 1, but now I think maybe this is bad practice because you can end up with 2 current items, etc. And what is the proper name for this kind of property?

P.S. I think it's not relevant, but just in case, I'm using SQL Server.

P.S. 2. I found a similar question here, which have a 2 answers, but none of them are accepted and they suggest opposite things. So if no one will answer this question, I will probably delete it soon. Or moderators can feel free to delete this question any time, if they want.

2
  • The question you linked to, while it doesn't have an accepted answer, really is a duplicate of this question. Commented May 30, 2018 at 12:22
  • 2
    I prefer #2. The property is more closely tied to the parent, not the child. The 1st implies that each child can have its own state. The 2nd implies that each parent can only have a single child selected. Of course, Variant 4 is also possible: Store the link in its own linked table SelectedChild(ParentId, ChildId). That may be best if it's a setting that is typically unrelated to Parent. Commented May 30, 2018 at 12:39

1 Answer 1

3

Variant 2 is a poor choice because it creates a circular reference which should generally be avoided. It also doesn't follow normalization practices because the child id could itself be a key for the parent in one to many scenarios, and it's not directly related to the parent. Deleting children is more problematic in this scenario since you need to update the parent first.

Variant 1 can be done, it still has normalization issues since the isPrimary flag depends on values from other rows. You could manage this with a trigger to always check after changes, this is still a pretty messy solution. Updates of isPrimary would require two statements all the time, and inserting new children requires checking the contents of the table.

The fully normalized way to solve this would be to have a PrimaryChild table that has parent and child id columns. You can enforce uniqueness on the parent id in this table to ensure a parent only has one primary child. this solution while normalized comes with the downside that reading is more complicated as you will always need 2 or 3 tables. Deleting a child record can safely be cascaded, and updates of the primary child are also easier.

The best solution really depends on your individual needs and where you are willing to maintain the complexity. The third option is likely the best unless reading performance becomes a problem, in which case you should choose one of the other 2 options. Which option to choose would depend on your use case and what complexity/potential anomalies you are willing to accept.

Not the answer you're looking for? Browse other questions tagged or ask your own question.