One or individual table(s) for all taggable subjects

Hello there!

I’ll asking a table-design-question, which should (hopefully :slight_smile:) be answered regarding database-performance, cakephp performance and cakephp handling.

It’s about tags. I have a table called “tags” with id | title | type. Type could be of “workflow_tag”, “dms_tag”, “custom_tag” (whatever, doesn’t matter).

And in the whole ecosystem of my app, I have many subjects, that are taggable (e.g. address, file, task, note etc.pp.).

Now: Should I create just one associating/join-table with id | tag_id | subject_id | subject_section, where “subject_section” will tell cake if this tag belongs to address, file, task etc…
or
should I create multiple join-tables like addresses_tags, files_tags etc.pp.

I know, the “one-table-version” isn’t normalized and could hold multiple milion rows (1-5 milion), but the “multiple-table-version” seems to be oversized/redundant also regarding the rest-api.

What’s your favor? What are best practices? Thanks a lot in advance!

When I’ve had things like this, I’ve tended towards the single table with an extra column to indicate the association. Easily managed by adding conditions when defining the associations between the tables.

Thanks for your reply. In the meantime I did it the same way. If there will be problems lateron it can be separeted some time. Thanks.