For example, a news website might have "categories" such as Politics, Business, Entertainment, Sports, Lifestyle, Tech, Science, Health, ... etc. Each article belongs to only one category, but can have multiple "tags". Tags are just like hashtags we see on social media and can be very detailed.

This StackOverflow question shows the best approach to design a database for "tags". What if I need to add "categories"?

I'm not sure if it's okay to simply record each article's category in its table row. Maybe adding a column named "Category"? If so, is the performance still good when visitors want to search, say, all articles which belong to "Business" category and have "Facebook" tag?

closed as too broad by mustaccio, Erik Darling, Mr.Brownstone, hot2use, McNets Dec 4 '17 at 8:14

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    You might want to look here! Have a post table with a specific given category (main topic). Then have a tag table with every possible subject. Then have a post_tag joining table with post_pk and tag_id as fields! I'll write this up as an answer if you find it helpful! – Vérace Dec 2 '17 at 14:00
  • @Vérace Thanks. I didn't find the answer in that website. But I guess your suggestion matches with my speculation. That is, using three tables: posts, tags, and posts_tags, and placing the category information in the posts table (in the Category column). – Ian Y. Dec 2 '17 at 14:25

If each article has at most one (or one and only one) category, then you would include the category information in the article table.

I would not include the text name of the category in the table. I would have a separate category table with the category name and an integer ID value. As there are presumably a relatively small number of possible categories, this shouldn't have a major impact on performance; and it would prove its value the first time someone had you change the name of a category.

You'll also probably want an index on article.category_id, if searches by tag and category will be very common (as I imagine they would be).

  • Thanks. Will performance be impacted if category names are stored directly in the article table? Usually, category names like Politics, Business, Entertainment, Sports, ... etc would hardly be changed. – Ian Y. Dec 3 '17 at 3:49
  • There'll be more disk space required for the table (you're storing the full name multiple times, rather than an integer value). Query times might be a fraction of a millisecond faster (nothing noticeable); or might in fact be a fraction slower (because they have to read in that text value from every row). Changes might be rare, but would touch a significant percentage of the rows when they did happen, and could take a while to process (vs no noticeable time to update the row I the lookup table). My rule is values that are user-meaningful should not be used as keys, because they will change. – RDFozz Dec 5 '17 at 16:29
  1. Design the SELECTs.
  2. Try them out.
  3. Try them out with a large dataset.
  4. Get rid of AND deleted = 1 -- it is a performance killer! But how?

OK, I have done all that for you. See here.

  • Thanks. Not sure if I understand you, but that doesn't seem to talk about both category and tags. – Ian Y. Dec 3 '17 at 3:43
  • A 'category' is just another name for a 'tag'. Sure, a article has only one "category", but the Lists table works just fine with that, too. – Rick James Dec 3 '17 at 15:34

Not the answer you're looking for? Browse other questions tagged or ask your own question.