7

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.

rene
  • 41,474
  • 78
  • 114
  • 152
cola
  • 12,198
  • 36
  • 105
  • 165
  • There is a related question answered [here](https://stackoverflow.com/questions/1810356/how-to-implement-tag-system) –  Aug 20 '19 at 22:29

1 Answers1

10

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)
);
Joel Brown
  • 14,123
  • 4
  • 52
  • 64
  • Can you post the relational database schemal. That will be helpful to understand. – cola Feb 11 '12 at 18:04
  • That is not how Stackoverflow implemented the system as you can see in [Stack Exchange Data Explorer](https://data.stackexchange.com/stackoverflow/query/new) –  Aug 20 '19 at 16:47
  • @Ivanzinho I suppose it all hinges on what the word "like" means. If you think it means "How did SO do it exactly?" then that's not the question I answered. If you think it means "How can I make something which has a similar function to SO?" then I did answer the question. If you look at the SO schema you will see that their tags work in exactly the way I describe, although I use different table and column names, and, I suppose, my schema also captures additional information about who did what/when and what order should it be shown in. – Joel Brown Aug 20 '19 at 22:13