Create Categories for sections

Databases
1

Lets take for example the following tables


Posts
-------------------------
postID (pk)
title
categoryID (fk)
etc etc

Products
-----------------------
productID (pk)
name
categoryID (fk)
etc etc

Faqs
---------------------
faqID (pk)
question
categoryID (fk)

All 3 tables contain a field categoryID, so basicle each post-product-question belong to a specific category. I’d like to know which is the best way to create my categories.

  1. Create n-different category tables each time, such as posts_categories, products_categories, faq_categories etc etc, so basicly there all will be indipendent.

  2. Create a single table for all categories with the following fields


Categories
--------------------
categoryID
category
type * // values (probably set as ENUM type - from a dropdown menu): posts, products, faqs etc etc

Examples
[table=“width: 500”]
[tr]
[td]categoryID[/td]
[td]category[/td]
[td]type[/td]
[/tr]
[tr]
[td]1[/td]
[td]Science[/td]
[td]posts[/td]
[/tr]
[tr]
[td]2[/td]
[td]Shirts[/td]
[td]products[/td]
[/tr]
[tr]
[td]3[/td]
[td]Design[/td]
[td]faqs[/td]
[/tr]
[/table]

Which do you believe is the best way to organize my categories ???

2

without the type column

do you really think there will never be any subjective similarities between posts and products and faqs?

never?

just have one set of categories for everything

3

Thank you for your reply.

I don’t think there will be similarities between posts, products etc except from the category each record may belong.

4

I thought i should organize all my categories based on each section (type), because there maybe cases where i might need to display some categories, for example on a blogi could show all categories with post type, right? all other types ( products, faqs etc) might not make sence to show them on a blog page.

5

okay, i understand what you’re doing there

my advice is to go with what you were thinking, use a “type” column

except… instead of ENUM, use TINYINT… do it as a personal favour to me :slight_smile:

6

why use TINYINT instead?

7

I may have also found a little problem here. What if there’s a category that appears in more than one sections??

See my example bellow
[table=“width: 500”]
[tr]
[td]categoryID[/td]
[td]category[/td]
[td]type[/td]
[/tr]
[tr]
[td]1[/td]
[td]Science[/td]
[td]posts[/td]
[/tr]
[tr]
[td]2[/td]
[td]Design[/td]
[td]posts[/td]
[/tr]
[tr]
[td]2[/td]
[td]Design[/td]
[td]faqs[/td]
[/tr]
[tr]
[td]3[/td]
[td]Shirts[/td]
[td]products[/td]
[/tr]
[/table]
Do I have to take ‘design’ as two different categories and do 2 INSERTs (something like duplcicate records, but the ‘type’ will be different each time) or is there a better way to do this ?

8

yes, there is

don’t use a “type” column at all

:slight_smile: