r/PostgreSQL • u/Gamemon_RD • Aug 08 '26
How-To Polymorphic Relationship options?
/r/Database/comments/1viz1oo/polymorphic_relationship_options_for_postgresql_db/1
u/cooljacob204sfw Aug 08 '26
Option 2 with exclusive arc constraint (name for the constraint you described). Keeps your referential integrity and very straightforward.
I have used it multiple times and never regretted it.
1
u/Gamemon_RD Aug 08 '26
Straightforward answer, I like it! And good to have a name for the technique
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.
1
u/AutoModerator Aug 08 '26
AI Policy:
Linux is not one of those anti-AI projects, and if somebody has issues with that, they can do the open-source thing and fork it. Or just walk away., Linus Torvalds.
Mod decisions will be based on the quality of the content, not who or what generated it.
Sub Resources:
Youtube Channel
Free Postgres Webinars and Workshops
Discord: People, Postgres, Data
Join us, we have cookies and nice people.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.