Designing a SQL table with hierarchy/sub-categories

sql normalization categorization
chips picture chips · Jan 22, 2010 · Viewed 8.2k times · Source

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

rosscj2533 picture rosscj2533 · Jan 23, 2010

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

Related questions

When can I save JSON or XML data in an SQL Table

When using SQL or MySQL (or any relational DB for that matter) - I understand that saving the data in regular columns is better for indexing sake and other purposes... The thing is loading and saving JSON data is sometimes …

Read More
How do I get the ID of multiple inserted rows in MySQL?

I am inserting some words into a two-column table with this command: INSERT IGNORE INTO terms (term) VALUES ('word1'), ('word2'), ('word3'); How can I get the ID (Primary Key) of the row in which each word is …

Read More
Two foreign keys referencing the same primary key

Is this okay to have two foreign keys in one table referencing one primary key of other table? EmployeeID is a primary key in the employee table and appearing as a foreign key twice in the timesheet table. There will …

Read More