Multiple tables for many-to-many mappings or one table with multiple columns?

Question

I'm using Postgres 14 and I have a tags table with a many-to-many relationship with other entities such as posts, comments, pages, etc. I've currently been representing these with tables like posts__tags which have id, tag_id, post_id. It seems easier to just have one table like tagged_entities with columns id, tag_id, post_id, comment_id, page_id, .... What are the reasons not to take this approach? It would simplify the associated application entities and logic and require less work as new taggable entities are added. As I understand it, the pros and cons are:

Table-per-mapping

Pros

• Each row has only three columns, each of which is non-null

Cons

• Adds table "bloat"
• Extra effort to add new mappings

Single table

Pros

• Conceptually simpler
• Less effort to maintain and add new mappings

Cons

• More space taken up by each row

Is there anything else I'm missing? Every column would be indexed in the single-table approach, but if null values aren't included in an index, I wouldn't expect the performance due to indexing to be any different.

Answer 1

Your list of Pros and Cons need further refinement

Table-per-mapping

This does not need an id column, you can create a primary key across the other two.

Pros

• Each row has only three two columns, each of which is non-null
• No need to take the existing table offline for changes, or to modify every existing row.

Cons

• Adds table "bloat" — yes, but reduces column bloat
• Extra effort to add new mappings — not really, see below

Single table

This is going to need a complex CHECK constraints which will need to be updated for each new column, of the form:

CHECK ((
    CASE WHEN post_id IS NULL THEN 0 ELSE 1 END +
    CASE WHEN comment_id IS NULL THEN 0 ELSE 1 END +
    CASE WHEN page_id IS NULL THEN 0 ELSE 1 END 
  ) = 1
)

Pros

• Conceptually simpler — is it? It's going to be a confusing table to query, with a lot of column bloat.
• Less effort to maintain and add new mappings — given the effort involved in changing the CHECK and adding extra indexes on alive table, probably not.

Cons

• More space taken up by each row — No, because null values don't take space, unless you have a lot.
• Each combination of columns requires a separate index, effectively duplicating the table so it becomes the equivalent of Table-per-Mapping.
• Requires taking the table offline for changes, and every existing row needs to be modified.
• Does not meet proper normalization standards.

---

All-in-all, sounds like the first option is much better. All you would need to add a new mapping to an entity named x is

CREATE TABLE tag_x (
    tag_id bigint REFERENCES tag (tag_id),
    x_id bigint   REFERENCES x   (x_id),
    PRIMARY KEY (tag_id, x_id),
    UNIQUE (x_id, tag_id)
);

Whereas for the other option, you would need to add the column and foreign key, but also modify the CHECK constraint and add indexes, and probably do this during a maintenance window.