Geek Answers Handbook

Best way to store tags in a database?

I have a database that contains two tables:

  • records
  • tags

The entry table contains messages, each of which has one or more tags. The problem is that each post can have any number of tags. In other words, I cannot have the column "tag1", "tag2", etc. And do left joins.

How do I set up posts so that each post can have any number of tags?

+7
sql database tags database-design data-modeling
source share
2 answers

You need a mapping table.

Create a table called entries_tags that contains two columns: entry_id and tag_id , with several keys for both entries.

This is called a many-to-many relationship.

+11
source share

You can also do it in the SO-way, where in addition to the join / map / intersection table, you have a list of tags for the entry that has taggable:

 entries table: post_id: 3539744, .... tags: sql, database, database-design, tags, data-modeling

If you cannot take a performance hit when using the connection table, pulling related tags for writing. Of course, special care must be taken here because you are dealing with denormalized data.

+3
source share

More articles:


All Articles