Apologies if this is an obvious question. I tried searching but found nothing relevant, although I suspect it is a common conundrum.
The situation is a parent-child hierarchy in which each parent row can have a nominated "special" child. I have 3 solutions, the first one is from the old database (this is a rewrite), the second is my first attempt (which caused some problems) and the third is the one that I am going with. I just wondered what people thought and whether there was a standard solution to this situation.
Solution 1 tblParent
The problem with this is that it does not ensure that there is only one special and also changing the special would involve updating 2 rows.
Solution 2 tblParent
This ensures that the special is unique. Note that the FK from child to parent needs to involve both columns to ensure a child is the special of the correct parent. This takes care of the validity of data, but introduces quite a lot of complexity. For example, to save a new set of data, the parent would be inserted without SpecialChildID, then the children would be created, finally the parent would be updated with the SpecialChildID. This is made more difficult when you have less control over updates (i.e. using Microsoft .Net DataSets and Application Blocks, but that's another story).
Solution 3 tblParent
ParentID (PK and FK to tblChild)
ChildID (FK to tblChild)
The FK on this extra table would have to come from 2 columns of tblChild. The PK would be the "inherited" ParentID (to ensure uniqueness of special). All updates could progress linearly (no backwards references).
Note that tblParentSpecialChild is not a cross-reference between tblParent and tblChild since not all combinations are valid. Note also, that tblParentSpecialChild is not a duplicate structure of tblChild in general since I've only shown key columns and tblChild could potentially have a lot more information on it.
So, just to reiterate:
Is solution 3 an acceptable way to do this?
Is there a better way?
Is there a technical term for this situation (I was googling for "special child row")?