How to represent the optionality of a many-to-one mandatory relationship from the Parent to its two Child entity types in an ERD?

I have a Parent entity type with two Child entities, with a relationship cardinality of many-to-one (or M:1) from Parent to Child.

The business rules say that:

• It is mandatory that the Parent entity type be connected to either of Child A or Child B in any given process, but also that…

• The Parent cannot be connected to both nor can it be connected to neither during that process.

I am stumped on how to represent the optionality here. It is a mandatory relationship on the Parent side, but not mandatory in the same way on the Child side. It is necessary that at least one Child be present in the relationship at a given time, but I do not feel it is correct to call this mandatory from the Child side, nor do I feel it is correct to represent both Child entities in optional relationships with the Parent.

So:

Is each Child entity in a mandatory or optional relationship with Parent?

This is a theoretical question in my head more-so, but I've ran with the "Who is the parent's favourite child?" example in my diagram as it matches: a child can have many parents favouring them, the parent can have one of either child as their current favourite, and the parent can't have no preference toward one child at a given moment. Cruel, but it works for my example!

I haven't got as far as DB design, though I was assuming that you can set up a column in the Parent table that can accept a Foreign Key from either of Child_A or Child_B but has a Not Null constraint. Or is my assumption off, and as such would this then mean the ERD above can't be implemented like so in the database?

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

• Every parent must have one and only one favourite child

• Every child may be the favourite of zero or more parents.

The (first) relationship being

• 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 ;-))

• A child must have exactly two parents (under basic human biology ;-))

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

StackExchange Database Administrators Q#136319, answer score: 2