Creating a TAG Database

I am submitting tags in the form of tag1,tag2,tag3,ect... But I am unsure 开发者_运维技巧of how to create the database layout in terms of performance and professionalism.

When someone clicks a tag, I want to query and pull up every page that has that tag.

---

The tags themselves go into a tags table. This table only has unique tags

TAGS
id | tagname
1     stack
2     overflow

You create a lookup table to match the tag table to the pages table

PAGES_TAGS
tag_id | page_id
1           13
1           24
1           11
2           12

You then do a join to query which pages have which tags.

SELECT *
  FROM pages p
 INNER JOIN pages_tags pt ON p.id = pt.page_id
 INNER JOIN tags t        ON t.id = pt.tag_id
 WHERE tag.name='overflow'

---

If this is all you want, then having:

1. tags table with tag_id and tag fields, maybe some other fields associated with the tag, i.e. description, permissions, ...
2. page_tags tags table with tag_id and page_id fields to hold the many-to-many relationship between the tags and pages (assuming that you have a pages table with page_id as the index column). You may also consider any additional fields, like date and time when the tag was added, who added the tag, etc.)

But later on you might want to add things like tags cloud, that will require some data caching (you dont want to rebuild your tags could every time someone tags something, instead do it periodically, once a day, for example). To achieve this you could add another table tags_cloud with tag_id and count fields.