We have a database schema that involves a heavy amount of polymorphism/inheritance/subtyping. Certain foreign keys such as a comment subject, or a permission target, point to a large number of tables, and certain properties like globally unique codes are shared by multiple tables.
The way we have this structured is via table-per-class inheritance, where the parent and child tables share a UUID primary key. UUID's were chosen so that tables could be merged and separated to adjust to business needs without worrying about collisions.
Currently there is no explicit discriminator column on the parent types, but I would like to add one for a couple reasons. It would rule out the possibility of erroneously pointing two different concrete subtypes to the same parent type. It would also frequently save joins, as often the child-specific data is not needed, only the knowledge of which subtype matches a given id.
I can think of a few possible approaches:
Just use a plain text field that stores the name of the concrete subtype table.
Do the same as above but with a custom Enum type that lists the possible tables.
Use an "id" field that points to a lookup table.
(2) seems like it would be a nicer option than (1), however it has the rather large downside of not allowing me to drop a value from the Enum without a ton of migration pain. This is particularly painful if this discriminator column shows up on a lot of tables, which it likely will.
(3) is often used for "enums that need to change", however it'd require hardcoding UUID/int id values into the DDL in order to properly handle the foreign keys on the subtypes, which seems like a bit of a deal breaker.
This has me leaning towards (1), but I was wondering if there was a better option. Perhaps even a text type optimized for frequently repeated identifiers with very limited character sets.
In the light of your explanations, I would use the first option.
If you use short strings, that shouldn't waste noticeable performance and storage space (the space taken up by a short string is one byte more than the string itself). You'd have to use check constraints to constrain the strings that can be used, but that's not a big hassle.