Designing a SQL table with hierarchy/sub-categories

Question

I have a table that looks something like this:

ID | Keyword | Category | Sub-Category | Sub-Sub-Category | Sub-Sub-Sub-Category

Do i need to split it up in two tables (a keyword table and a categories table with parent id) if one keyword can only belong to one category,sub-category...etc. meaning there are no repetition. is there still a need to split it up?

Answer 1

I'd do it in two tables with each foreign key coming from the Categories table:

Keywords 
id (PK)
keyword
category_id (FK)

Categories
category_id (PK)
category
parent_category_id (FK)

The data in the Categories table would look like:

category_id    category    parent_category_id
1              Food        null
2              meat        1
3              organic     1
4              fruit       3

and that data in the Keywords table would look like:

id     keyword    category_id
1      grapes     4
2      chicken    2

Answer 2

You only need one table to represent a 1-1 mapping. To represent 1-many or many-many mappings, you should use multiple tables.

If a keyword can only correspond to one category/sub-category/sub-sub-category, your current layout should be fine.

One caveat: if you want to search based on the keyword, there could be performance gains for separating the tables. It's much faster to perform an integer search.

The discussion of storing the keyword values in another table coarsely corresponds to this discussion of storing country names (which are mostly static) in another table. Some key advantages of using another table might be things like (spoken) language independence, fast searching, and ease of updating later on.

Answer 3

I'd use a two tables like this.

   Categories
-------------------
PK,FK1 | CategoryID
       | Keyword 
       | Category 

  SubCategories
--------------------
PK,FK1 | CategoryID
PK,FK1 | SubCategoryID