How to create a tag system like stackoverflow.com?

Question

I am going to make a site for publishing with tags. Tags can be up to five number tags, for example, at stackoverflow.com.

Can anyone tell the StackOverflow tag system? Relational database system with mail and tag.

Should I add a column to the table column or do I need to create a separate tag table for it? Tags can be separated by spaces or commas.

Answer 1

Definitely create a table contained in the list of available tags .

You should also definitely create a separate applicable tag table containing:

• Foreign key to your message.
• Foreign key to your tag.
• Consistency showing order.
• Anything else that might interest you, for example, who added the tag or when it was added.

You want to use a normalized design because using a denormalized design (adding 5 columns) will break if you ever want to change your business rules to allow fewer or more tags. In addition, it will not help you if you have other information to save, for example, when the tag was added and by whom.

---

EDIT: DDL

In the OP request:

CREATE TABLE post ( id INTEGER IDENTITY , title VARCHAR(1000) NOT NULL , added_date DATETIME NOT NULL , posting_user_id INTEGER NOT NULL , ... (and so forth) ... , PRIMARY KEY (id) , FOREIGN KEY (posting_user_id) REFERENCES posting_user (id) ); CREATE TABLE tag ( id INTEGER IDENTITY , term VARCHAR(20) NOT NULL , description VARCHAR(1000) NULL , ... (and so forth) .... , PRIMARY KEY (id) ); CREATE TABLE applied_tag ( post_id INTEGER NOT NULL , tag_id INTEGER NOT NULL , display_order INTEGER NOT NULL , tagging_user INTEGER NOT NULL , applied_date DATETIME NOT NULL , ... (anything else you want).... , PRIMARY KEY (post_id, tag_id_, display_order) -- Or use an auto-increment, but this is unique. , FOREIGN KEY (post_id) REFERENCES post (id) , FOREIGN KEY (tag_id) REFERENCES tag (id) , FOREIGN KEY (tagging_user) REFERENCES posting_user (id) );