Modeling Optional Foreign Key from Composite Primary Key field (Oracle Data Modeler)

The cost of using a simple synthetic integer PK is small, and the benefit in your case would probably be quite considerable.

• As you point out, you'll have a much simpler FK relationship.
• A small PK makes for small (and fast) indices. Your total table space will probably be made less by adding such a column.
• If business rules ever change, you won't have to reorder the table.

The only material downside that comes to mind is that you may lose performance on queries which benefited from clustering on the composite PK. If you think that's likely to be significant, than continue clustering on the composite candidate key, but put the PK on the synthetic key.

It looks to me as though child_1 and child_2 are two instances of a single entity type.

If so, then you have a (second) relationship such that

1. Every parent must have one and only one favourite child
2. Every child may be the favourite of zero or more parents.

The (first) relationship being

1. A parent may have zero or more children (unless, of course you have defined "parent" such that a child is a pre-requisite of being a parent ;-))
2. A child must have exactly two parents (under basic human biology ;-))

Your first relationship may, of course, be different depending on the context.