storing preferred entry in a 1-to-n database relation

Question

I'm looking how to (best) model a relational database so that it can flag that (0 or 1) entries in a 1:n relation are preferred.

(it's actually to be implemented in mariadb 10.2, but that should not matter all that much)

For simplicity, assume I've two tables:

parent:

• id
• name

child:

• id
• name
• parentid (FK -> parent(id) )

The problem is that I'd need to model a way to store the preferred child

I see two ways, but I don't like either all that much:

1. add a field in the parent that lists the preferred child's id and make that a FK to the child.
2. add a boolean column in the child to indicate a row is the preferred child.

Things I'm seeing:

only 1 preferred child:

• In case of solution 1, I can only have a max. of one preferred child (=good)

• In case of solution 2, I need to rely on the application to make only one preferred child per parent (=bad)

-> this pushes me to solution 1

Maintainability

Solution 1 creates FKs in both directions between the tables parent and child. I'm not confident how such a construction could last through e.g. a backup/restore cycle if it ever were needed as there's no more order in which to create the tables, they need to be created without FKs and those need then to be added later on. Even if that's all covered by tools, I'm still quite afraid of long term maintainability.

-> this pushes me to solution 2

So before we pick a solution, neither of which I'm not convinced is a good one, anybody got other solutions to consider ?

Anything else to consider that I overlooked ?

I'm not sure how to consider this with regards to normalization.

EDIT:

Digging further after typing this up, I figured a third option that seems far superior: Adding a third table to model the preferred child.

Essentially:

preferredchild:

• parentid
• childid

All with a FK such as
FOREIGN KEY (parentid, childid) REFERENCES child (parentid, id)

And probably some more unique keys to make sure it all is unique, but this solves the chicken-egg of my above solution 1 and avoids the mess an application could make in solution 2.

Of this 3rd option I did a quick fiddle: http://sqlfiddle.com/#!9/af77bf/5/0

Answer 1

Option 1 isn't bad, but requires a nullable preferred child_id or temporary disabling of FK checks to populate. Still, it makes effective use of the normal PK and FK indexes when querying for the preferred child.

Option 2 isn't a good solution IMO, since the preferred child flag creates a dependency between rows. Updating the preferred child requires updating multiple rows, creating an opportunity for inconsistency. It can actually be handled in a way in MySQL / MariaDB - if you're willing to use NULL for FALSE, a unique index on (parent_id, is_preferred) will allow only one row with is_preferred = TRUE, and any number of rows with is_preferred = NULL. But then you have to deal with NULLs, which adds a bit more complexity, and there's the risk of non-NULL non-TRUE values.

Option 3 is good. It's simple and avoids the issues that complicate the other options, while retaining the effective use of indexes as long as you have your PK / FK fields indexed.