It might be the case that my initial design is flawed, but let's start from there and see whether there is a good enough approach.
I have two entities, say, A
and B
that are very similar but not identical so the sets of columns are different. For those reasons, there are two distinct tables, A
and B
.
That said, I would like to create another table that should describe both A
and B
with a set of fields that are applicable to both A
and B
. Let's call this table entity_settings
.
Its schema is not intuitive to figure out:
My first idea is to use some kind of
entity_type
flag andentity_id
from either A or B depending on the type with a UNIQUE constraint on(entity_type, entity_id)
. Such a definition makes it impossible to have a normal foreign constraint because thisentity_id
consists of ids from two tables.Another option is having
A_id
andB_id
that can be null but reference the ID fields from the corresponding tables. This makes sense and provides referential integrity, but inserts into this table are going to be pretty clumsy.The only option I am also considering is having two separate tables for
A
andB
so that each table has its own child. This looks a bit wasteful in terms of the number of tables, but is probably the cleanest option in terms of design.
As almost always, I have a feeling that there is something I am missing and would highly appreciate it if someone could suggest any other options.
EDIT:
In the second option, it also makes sense to add a CHECK constraint like CHECK((A_id IS NOT NULL AND B_id IS NULL)) OR (A_id IS NULL AND B IS NOT NULL))