Recommended SQL database design for tags or tagging

sql database-design tags data-modeling tagging
dlamblin picture dlamblin · Aug 21, 2008 · Viewed 98.3k times · Source

I've heard of a few ways to implement tagging; using a mapping table between TagID and ItemID (makes sense to me, but does it scale?), adding a fixed number of possible TagID columns to ItemID (seems like a bad idea), Keeping tags in a text column that's comma separated (sounds crazy but could work). I've even heard someone recommend a sparse matrix, but then how do the tag names grow gracefully?

Am I missing a best practice for tags?

Answer

Yaakov Ellis picture Yaakov Ellis · Aug 21, 2008

Three tables (one for storing all items, one for all tags, and one for the relation between the two), properly indexed, with foreign keys set running on a proper database, should work well and scale properly. 

Table: Item
Columns: ItemID, Title, Content

Table: Tag
Columns: TagID, Title

Table: ItemTag
Columns: ItemID, TagID

Related questions

Database Design for Tagging

How would you design a database to support the following tagging features: items can have a large number of tags searches for all items that are tagged with a given set of tags must be quick (the items must have …

Read More
Remove Primary Key in MySQL

I have the following table schema which maps user_customers to permissions on a live MySQL database: mysql> describe user_customer_permission; +------------------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+---------+------+-----+---------+…

Read More
How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?

Can anyone explain how to implement one-to-one, one-to-many and many-to-many relationships while designing tables with some examples?

Read More