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:
- Each row has only three columns, each of which is non-null
- Adds table "bloat"
- Extra effort to add new mappings
- Conceptually simpler
- Less effort to maintain and add new mappings
- 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.