Designing a SQL table with hierarchy/sub-categories

Question

Hey everyone, 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

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 2

I'd use a two tables like this.

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

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

Answer 3

It might make sense to split it up if you expect to rename or rearrange your categories later:

• if you leave it as it is, you will have to do that rename/reorganize step (changing the category/sub-category/sub-sub-category/sub-sub-sub-category fields) for every row in this table that contains that (((sub)sub)sub)category. that results in a more complex query, and if you have very many rows in this keyword table, it might be a performance issue (=take a while for the database to do); on the other hand, queries (reading) will be as fast as possible.
• if you split it up, then updating (((sub)sub)sub)category will only to be done to less rows, but query (read) will take longer because it has to work with two (or more) tables.

Weigh the cons and pros of both and then make a decision.m

Answer 4

Why not just add a ParentID column and FK to the PK?

Answer 5

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