How to implement polymorphic associations in an existing database
You could use Option 1 but without an additional surrogate Alternate Key.
Instead, extend the existing Primary Key (of each entity), with an EntityType
column (say CHAR(1)
, that would be E
for Events, P
for Persons, D
for Products).
The compound (EntityId, EntityType)
will become then the Primary Key of table Entity
and the corresponding compounds in the other 3 subtype tables.
(The EntityType
is just an auxilary, reference table, with 3 rows):