1

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.

1
  • Thanks @Charlieface, those are good points!
    – mowwwalker
    Jul 17, 2022 at 0:36

1 Answer 1

2

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 mappingsnot 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 mappingsgiven the effort involved in changing the CHECK and adding extra indexes on alive table, probably not.

Cons

  • More space taken up by each rowNo, 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.