Best way to store tags in a database?
I have a database that contains two tables:
- entries
- tags
The entries table contains posts that each have one or more tags. The problem is, each post can have any number of tags. In o开发者_如何学Cther words, I can't have a 'tag1', 'tag2', etc. column and do a LEFT JOIN.
How should I set up entries so that each post can have any number of tags?
You need a mapping table.
Create a table called entries_tags
that contains two columns: entry_id
and tag_id
, with a multi-key on both entries.
This is called a many-to-many relationship.
You can also do it the SO-way, where in addition to having a junction/mapping/intersection table, you have a list of tags on the entry that's taggable:
entries table:
post_id: 3539744, .... tags: sql, database, database-design, tags, data-modeling
If you can't take the performance hit of using a junction table when pulling the associated tags for an entry. Of course extra care has to be taken here because you are dealing with denormalized data.
精彩评论