Mapping EER to Relational model. Mandatory participation of superclass with (Mandatory; Disjoint) subclasses

Question

I have a Superclass A that has a 1:* (one-to-many) relationship with another entity (X) and the participation of A is mandatory. A has two subclasses with their own distinct attributes, B and C. The superclass-subclass hierarchy is organised in a way that the participation constraint is mandatory and there can't be an overlap, it's either B or C with no 'abstract' A.

While mapping it to relational model, I've created just two separate tables (relations) for B and C, and placed their primary keys as foreign keys to the X entity. Then I've realised that the foreign keys must be nullable since it can be either B or C. However, it has created an undesirable option to have both foreign keys as nulls or to have two of them at the same time.

Another option that I can think of is to create a single relation for both subclasses and add some flags (isB and isC), but it'll be messy if the number of attributes of each of the subclasses will grow. Moreover, it'll generate a lot of null values.

What do I do? What's the best practice to accommodate such kind of situations and map it to relational model?

Link to an example diagram

Answer 1

While mapping, leave just the two subclasses (especially if they have many attributes), leave the foreign keys in the X entity and make them nullable.

A check constraint for your X entity:

CHECK (
    (B_ID IS NOT NULL AND C_ID IS NULL) OR
    (B_ID IS NULL AND C_ID IS NOT NULL)
)