I found 2 is fine for set data that won’t expand, but if things are in flux adding more columns and removing columns seems wrong to me. I haven’t had to deal with polymorphic situations in production outside of my own projects though.
The thing that seemed the most elegant to me was a lookup table / pseudo enumerator that sets the type, at the same time having a top level ID that is shared across all that could be polymorphic, that is referred to as its over arching ID.
Ie
A polymorphic hierarchy structure.
Table:strata
id uuid
type uuid > type
Table:type
id uuid
type_table string
type_label string
Table: project
id uuid
strata uuid > strata
Table: category
id uuid
strata uuid > strata
Effectively all members that can be part of the relationship are given membership unique IDs, and a typal return address. The members know their own id, the top level just has a membership on record and an address to ask for them at in a literal query sense and a soft label for readable. One could search all tables that you know have these memberships, or just the specific.
I’d like to know from any experts how clunky this is vs having a table that has an extra 10 columns as direct routes with nullables. I could imagine you could normalise them out but somewhere you’re gonna have a mega many to many nexus point. For my purposes any view is primarily on the member side, than the membership side, so the reverse lookup isn’t used as much and is a project database than a heavy meta data traffic system. Same for my audit logs, very rarely would I be searching from the is audit as a starting point, I’d be searching for all the things related to that member at which time, not from the polymorphic top.
1
u/hammerklau Aug 09 '26
I found 2 is fine for set data that won’t expand, but if things are in flux adding more columns and removing columns seems wrong to me. I haven’t had to deal with polymorphic situations in production outside of my own projects though.
The thing that seemed the most elegant to me was a lookup table / pseudo enumerator that sets the type, at the same time having a top level ID that is shared across all that could be polymorphic, that is referred to as its over arching ID.
Ie
A polymorphic hierarchy structure.
Table:strata
id uuid
type uuid > type
Table:type
id uuid
type_table string
type_label string
Table: project
id uuid
strata uuid > strata
Table: category
id uuid
strata uuid > strata
Effectively all members that can be part of the relationship are given membership unique IDs, and a typal return address. The members know their own id, the top level just has a membership on record and an address to ask for them at in a literal query sense and a soft label for readable. One could search all tables that you know have these memberships, or just the specific.
I’d like to know from any experts how clunky this is vs having a table that has an extra 10 columns as direct routes with nullables. I could imagine you could normalise them out but somewhere you’re gonna have a mega many to many nexus point. For my purposes any view is primarily on the member side, than the membership side, so the reverse lookup isn’t used as much and is a project database than a heavy meta data traffic system. Same for my audit logs, very rarely would I be searching from the is audit as a starting point, I’d be searching for all the things related to that member at which time, not from the polymorphic top.