How can I make a tag system like stackoverflow.com?

Question

I'm going to make a website to post with tags. Tags can be up to five number of tags like at stackoverflow.com.

Can anyone tell the tag system of StackOverflow? The relational database system with post and tag.

Should I add a column in post table or should I create separate tag table for it? Tags can be separated by spaces or comma.

Answer 1

Definitely create a table containin the list of available tags.

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

• Foreign key to your post.
• Foreign key to your tag.
• Sequence number showing the order.
• Anything else that might interest you, like who added the tag or when it was added.

You want to use a normalized design because using an denormalized design (adding 5 columns) will break if you ever want to change your business rules to allow fewer or more tags. Also, it doesn't help you if you have other information to keep, such as when the tag was added and by whom.

---

EDIT: DDL

At the OP's 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)
);