Creating a TAG Database

Question

I send tags in the form of tag1, tag2, tag3, ect ... But I'm not sure how to create a database layout in terms of performance and professionalism.

When someone clicks on a tag, I want to request and pull out each page with that tag.

Answer 1

The tags themselves go to the tag table. This table contains only tags.

TAGS id | tagname 1 stack 2 overflow 

You create a search table that matches the tag table in the page table

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

Then you make a connection to query which pages have 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' 

Answer 2

If that's all you need, then:

• tags table with the tag_id and tag fields, possibly some other fields associated with the tag, i.e. description, permissions, ...
• page_tags tag table with tag_id and tag_id fields for storing the relationship between tags and pages (provided that you have a pages table with page_id as the index column), you can also consider any additional fields, such as the date and time the tag was added, who added tag, etc.).

But later, you may want to add things like a tag cloud, which will require some data caching (you do not want to rebuild your tags every time someone signs something, rather than periodically, once every day, for example). To do this, you can add another tags_cloud table with the tag_id and count fields.