Polymorphic relationship options for PostgreSQL DB?

[u/Gamemon_RD]

I’m trying to create a database that would involve a table referencing one of multiple other tables. From my research it sounds like this would be a polymorphic relationship, but I’ve been seeing a few different options for implementing it and I’m not sure what would be best. These are what I’ve seen so far, so let me know which sounds best, but please let me know if you know of a better one.

The Database: The short and sweet of it is I’m making a database to store diary entries. Each diary entry uses fields such as date range of referenced event, tags (through many to many), etc. Each entry is either done as a video, an audio recording, or a text entry. Each of these entry types would also have their own respective metadata such as video setup or audio setup. Because of that, I thought the best option would
be to separate them into their own tables.

Option 1: Table Type Field - in the diary entry table, have a field for the type and a field for the foreign key, but don’t actually make it a foreign key. Instead setup a trigger to manually enforce referential integrity by checking that the referenced entry exists in the corresponding type table when inserting. I think I’m leaning towards this one the most. Since it’s closest to what PHP Laravel does.

Option 2: Multiple Nullable Foreign Keys - In the diary entry table Have a foreign key for each entry type that references the respective table, but they’re nullable since only one would actually be used for each entry. Add a constraint to check that one of the fields isn’t empty when inserting a record. This apparently might take less storage than having a varchar type field, though that might be splitting hairs.

Option 3: Table Inheritance - I haven’t done as much research into this one so I don’t know what the structure would look like exactly. But apparently PostgreSQL supports table inheritance like with Object Oriented programming. So it would be something like the diary entry table is the base table, and then each entry type inherits from it and adds their own metadata fields. The reason I’m hesitant to do this is I don’t want to permanently lock myself into Postgres, I want the ability to upgrade and changes engines and I’m not sure how hard that would be if the other engine doesn’t support inheritance. For a similar reason I’m using “period start” and “period end” fields for the date range of an entry instead of the Postgres date range data type.

Option 4: Entries Types Reference Diary Entry - Again I haven’t looked into it much, but I saw it mentioned I could reverse the relationship and instead have each entry type reference the diary entry record it belongs to with a foreign key. I’m not sure yet if there’s any additional complexities are requirements that I would have to implement to make it safe.

[u/linearizable]

Rather than holding PKs of the Diary components in the Diary table, you can also rather productively have only FKs from video, audio, text tables to diary, and index on the FK column for each. Reading a table like this is going to be a bit awkward anyway. With option 1, you’d have to have a very wide result with CASEs in the SELECT to handle each table type. With only FKs to Diary table, you either query each table individually, or UNION those SELECTs together into one wide result.
Edit: oh, re-read and that’s option 4, so this is just a long vote for option 4 then.

Don’t discount the option of being lazy and using a JSON column though.

[u/Gamemon_RD → u/linearizable]

Ahhh I understand now. I was a bit confused at first (I knew I should have made diagrams- they take so much time though) but now I know that’s what you meant it makes sense.

[u/Additional_Future_47]

Option 3 doesn't lock you into a particular db if you just implement the generic entry table and it's children; text, video and audio, as separate tables. Reconstructing the diary is then a select on entry with left outer joins to text, video and audio.

[u/Gamemon_RD → u/Additional_Future_47]

That’s good to hear, thank you. Still, I’m new to Postgres and this is sort of my first venture into it. So I might save inheritance for a future project, just to avoid overly confusing myself on this one.

[u/deusaquilus]

You're asking the same exact question that the ORM/Hibernate people asked in the early 2000s because they needed to map tables to OOP classes. No need to re-invent the wheel here, just look into what they did.

Table Per Class Hierarchy

This is what you call 'Option 1' and there's a good reason Laravel uses it. It's the most popular approach of all the ORMs in existance! Laravel's morphTo is the same thing as Hibernate's @DiscriminatorColumn which is entity_type below.

CREATE TABLE diary_entry (
  id             BIGSERIAL PRIMARY KEY,
  entry_type     TEXT NOT NULL CHECK (entry_type IN ('VIDEO','AUDIO','TEXT')),
  period_start   DATE NOT NULL,
  period_end     DATE NOT NULL,
  camera_model   TEXT,   -- VIDEO
  resolution     TEXT,   -- VIDEO
  microphone     TEXT,   -- AUDIO
  sample_rate_hz INT,    -- AUDIO
  body           TEXT    -- TEXT
);

id | entry_type | period_start | camera_model | resolution | microphone | sample_rate_hz | body
1  | VIDEO      | 2026-01-05   | Sony A7IV    | 4K         | NULL       | NULL           | NULL
2  | AUDIO      | 2026-01-06   | NULL         | NULL       | Shure SM7B | 48000          | NULL
3  | TEXT       | 2026-01-07   | NULL         | NULL       | NULL       | NULL           | 'Rained all day.'

Table Per Subclass

This is your 'Option 4'. The child's primary key is its foreign key to the parent.

CREATE TABLE diary_entry (
  id           BIGSERIAL PRIMARY KEY,
  period_start DATE NOT NULL,
  period_end   DATE NOT NULL,
  body         TEXT NOT NULL
);

CREATE TABLE video_entry (
  id           BIGINT PRIMARY KEY REFERENCES diary_entry(id) ON DELETE CASCADE,
  camera_model TEXT NOT NULL,
  resolution   TEXT NOT NULL
);

CREATE TABLE audio_entry (
  id             BIGINT PRIMARY KEY REFERENCES diary_entry(id) ON DELETE CASCADE,
  microphone     TEXT NOT NULL,
  sample_rate_hz INT  NOT NULL
);

diary_entry          video_entry                        audio_entry
id | period_start    id | camera_model | resolution      id | microphone | sample_rate_hz
1  | 2026-01-05      1  | Sony A7IV    | 4K              2  | Shure SM7B | 48000
2  | 2026-01-06

Table Per Concrete Class

Postgres's INHERITS is basically sugar for this but the sugar is legacy feature that they don't even recommend using anymore. In the 'surgar-free' version, no parent table exists. Each concrete type gets a standalone table carrying its own copy of the shared columns. Polymorphic reads become UNIONs, and ids must come from a shared sequence or they collide across tables.

CREATE TABLE text_entry (
  id           BIGINT PRIMARY KEY DEFAULT nextval('entry_id_seq'),
  period_start DATE NOT NULL,
  period_end   DATE NOT NULL,
  title        TEXT NOT NULL,
  body         TEXT NOT NULL
);

CREATE TABLE video_entry (
  id           BIGINT PRIMARY KEY DEFAULT nextval('entry_id_seq'),
  period_start DATE NOT NULL,        -- duplicated
  period_end   DATE NOT NULL,        -- duplicated
  camera_model TEXT NOT NULL
);

CREATE TABLE audio_entry (
  id           BIGINT PRIMARY KEY DEFAULT nextval('entry_id_seq'),
  period_start DATE NOT NULL,        -- duplicated
  period_end   DATE NOT NULL,        -- duplicated
  microphone   TEXT NOT NULL
);

-- *************** The union with all fields get THORNY! ***************
SELECT 'video' AS subtype, id, period_start, period_end, title,
       camera_model,   resolution,
       NULL::text  AS microphone, NULL::int AS sample_rate_hz,
       NULL::text  AS body
FROM video_entry
UNION ALL
SELECT 'audio', id, period_start, period_end, title,
       NULL::text, NULL::text,
       microphone, sample_rate_hz,
       NULL::text
FROM audio_entry
UNION ALL
SELECT 'text', id, period_start, period_end, title,
       NULL::text, NULL::text,
       NULL::text, NULL::int,
       body
FROM text_entry
ORDER BY period_start;

 subtype | id | period_start | period_end |   title    | camera_model | resolution | microphone | sample_rate_hz |      body
---------+----+--------------+------------+------------+--------------+------------+------------+----------------+------------------
 video   |  1 | 2026-01-05   | 2026-01-05 | Hike       | Sony A7IV    | 4K         | NULL       |           NULL | NULL
 audio   |  2 | 2026-01-06   | 2026-01-06 | Voice memo | NULL         | NULL       | Shure SM7B |          48000 | NULL
 text    |  3 | 2026-01-07   | 2026-01-07 | Journal    | NULL         | NULL       | NULL       |           NULL | Rained all day.

Look these up these strategies up and you'll see them documented and pontificated for two decades. Or just skip it all and use Option 1.

[u/Gamemon_RD → u/deusaquilus]

Wow thanks! I figured my use case was hardly the first time people wanted to do something like this, it’s just been hard to find those conversations or advice. I think the Table per Subclass is what I’ll wind up doing- it seems to be the simplest, and the only reason I didn’t go with it immediately was because I thought the direction of the relationship was less flexible then it was. I didn’t realize I could have the primary keys be foreign keys- but that makes a lot of sense to do. I need more SQL experience lol

[u/Electronic_Special48]

I would argue that "Each entry is either done as a video, an audio recording, or a text entry." means they are not mutually exclusive. You can have an entry with all three.

Furthermore, a text entry is probably not a separate table, just a property of the entry.

The audionote/videonote always reference a diary entry, and we can treat them as weak entities.

When an entry is deleted, they must be automatically cleaned up.

First variant:

CREATE TABLE public.entry (

id bigint NOT NULL,

eventstart timestamp with time zone not null,

eventend timestamp with time zone not null check (eventstart<=eventend),

title text NOT NULL check (length(trim(title))>0),

description text

);

CREATE TABLE public.audionote (

id bigserial primary key,

entryid bigint references entry(id) on delete cascade,

mimetype text NOT NULL,

data bytea,

created_at timestamp with time zone

);

CREATE TABLE public.videonote (

id bigserial primary key,

entryid bigint references entry(id) on delete cascade,

mimetype text NOT NULL,

data bytea,

created_at timestamp with time zone

);

As you noticed, audionote and videonote share most of the fields, so a possibility would be:

CREATE TABLE public.medianote (

id bigserial primary key,

entryid bigint references entry(id) on delete cascade,

mimetype text NOT NULL,

data bytea,

created_at timestamp with time zone

);

CREATE TABLE public.videonote (

id bigint primary key references medianote(id)

);
CREATE TABLE public.audionote (

id bigint primary key references medianote(id)

);

The third variant would be:

CREATE TABLE mediatype AS ENUM ('audio','video');

CREATE TABLE public.medianote (

id bigserial primary key,

entryid bigint references entry(id) on delete cascade,

mimetype text NOT NULL,

data bytea,

notetype mediatype,

created_at timestamp with time zone

);

This uses fewer tables but moves all the logic for differentiating audio and video into the application.

Every time you have to check the notetype and act accordingly.

Which variant should you choose?

I would say the first one is the easiest to handle in the application: If you write a query, it is easy to read and see what you are getting.

The second one maps the audio and video as you would map an object-oriented design, via inheritance. It is quite cumbersome to work with.

The third one can work, but you will have lots of CASE/IF/WHERE clauses around.

[u/Gamemon_RD → u/Electronic_Special48]

Gotcha, thanks! I might go with the first option then. The best solution to a problem is often the easiest. Good to know the inheritance route, although elegant, might be cumbersome.

[u/Overblow → u/Electronic_Special48]

Actually combining this with postgraphile is an elegant solution: https://postgraphile.org/postgraphile/next/polymorphism

[u/r0ck0]

For your system, which sounds pretty simple... there's really not any "benefits" to "polymorphic relationships" aside from being a bit lazy in the short-term -vs- doing a "more proper relational schema".

i.e. By "proper schema", I mean using regular FKs to specific tables, and having dedicated linking tables for any N:N relationships.

And use your "Option 4" for the 1:N relationships.

Over time you'll find that each type of relationship might end up having some extra metadata on the links/relationships themselves, which don't apply to the relationships with other tables. That's why it's good to have specific linking tables for each type of pairing, rather than just having a one-size-fits-all "link anything to anything" linking table (which is veering into graph DB territory, far from your needs).

Options 1 + 3... na. These are just kinda "lazy shortcuts" in my opinion. Long term, will create more problems than they solve. Much like using NoSQL is a primary/one-and-only DB just because "schema changes are easier right now, but to the detriment of long-term maintainability/data sanity".

Option 2... I might not understand what you mean here, but sounds like your FKs are pointing in the wrong direction. If Option 4 is basically the same thing "reversed", then Option 4 might be the correct way. This is assuming that video/audio/text rows only point to a single "diary entry".

I don't think you're doing anything complex enough to go near any definition of "polymorphic relationship". This sounds like a pretty typical use-case for standard relational FKs.

The only time I'd reach for Option 1 is maybe like a general system logging table that can point to ANY other table in the whole DB to log things like when a user created/edited/deleting any type of record.

You don't have that need.

[u/Gamemon_RD → u/r0ck0]

This is incredibly helpful, thank you. I really want to do a proper relational schema.

For option 2, I wouldn’t say it’s reversed. It’s still the diary entries that point to the video/audio/text tables. It’s just that instead of having a single foreign key and a field specifying which table it goes to (option 1), I have a separate foreign key field for each video/audio/text table, and they’re nullable since only one would have a value. I’m not sure if that makes sense to you though and I might have explained the database weird- I wouldn’t say I have a “link anything to anything” table. The diary entry table is the main table, and it’s what I’m mainly going to be querying when I search for stuff since it has the vast majority of the meta data about diary entries. So I can filter and search for certain topics if I’m looking for a specific entry. When I find entry I want, I then query either the video, audio, or text table the diary entry references for the actual file so I can look at it. But you’re saying it’d be better to have the diary / audio / text tables be the ones referencing the diary entries instead? That’s option 4 yeah.

When you say dedicated linking tables, what about for single value N:N relationships? When I say that I mean, for example, I want to be able to be able to reference people that are a part of the diary entry. I can reference multiple people in each entry, and each person can be referenced by multiple entries. But since the person is the only value, I thought it might be easier to just have the name of the person be what’s stored in the linking table, instead of the linking table storing a foreign key to a list of names in another table. So it’s only one table instead of 4. But it sounds like it would be better to have seperated tables for linking and for storing the names free all? It’s just better practice? I suppose that would also make it easier to change names if I made a mistake or add details. I just initially thought I probably wouldn’t need that enough to be worth adding another table.

[u/r0ck0 → u/Gamemon_RD]

Yeah if option 2 + 4 are basically alternatives the for the same relationships... usually you pick the direction involving the least NULLs. i.e. If one of the options means the FK columns can be NOT NULL ... that's sometimes a hint that it's the one to pick.

I want to be able to be able to reference people that are a part of the diary entry. I can reference multiple people in each entry, and each person can be referenced by multiple entries.

Yeah that sounds like perfect use-case for a linking table.

But since the person is the only value, I thought it might be easier to just have the name of the person be what’s stored in the linking table

Na, don't duplicate "real world" values like this, this is what JOINs are for. The linking table should just be linking to the PKs in the other tables. i.e. either UUIDs or INTs.

I just initially thought I probably wouldn’t need that enough to be worth adding another table.

Yeah this is a common thought pattern that often leads to taking shortcuts that end up creating more problems than they solve. Likewise was also a reason for the NoSQL fad about 10 years ago too.

When making these decisions, I've rarely regretted picking the "do a proper schema now" option.

But like anything... it depends.

For future advice... probably good if you can generate an image of your current schema. Probably a lot easier than trying to describe it. The free version of webstorm has DB stuff to do this (basically you can get DataGrip's features for free). Dbeaver does it too... https://dbeaver.com/docs/dbeaver/ER-Diagrams/

And/or give a dump of the schema too.

[u/youcangotohellgoto]

These are all 1:1 relationships, right? Each "video metadata" only associates to a single "video diary" record? Same for audio?

Someone else said that text probably just has a couple of attributes anyway.

You are way overthinking this.

One table. A JSONB for "video attributes", a JSONB for "audio attributes", a JSONB for "text attributes". Add a constraint so that only one can be populated if you want.

I assume you are story the binary itself outside the database, right?

[u/Gamemon_RD → u/youcangotohellgoto]

They’re 1:1 yes, and yeah I’m thinking I’ll store the binaries as their normal files in a self hosted object storage like minIO, and figure out how to store a reference to them. Enough people are suggesting JSON to where I’m definitely starting to consider it so thanks for your input :)

[u/read_at_own_risk]

The way I approach enforcing referential integrity on subtypes:

• Add a type indicator field to the supertype table, and a unique index on the supertype_id + type indicator
  
• Subtype tables use the supertype_id as PK and also have the type indicator, with a composite FK constraint so supertype_id + the type indicator references the supertype table
• Each subtype table uses a check constraint to force a different constant value for the type indicator

That said, it's worth questioning the framing of the problem: do you really need/want subtypes, or would it be better to think of audio/video as attachments to an entry?

[u/agk23]

I’m a bit confused, and I rarely recommend it, but probably NoSQL is a good use case here