Adding Category and Subcategory in Database

Question

I am doing a project in PHP, MySQL. I need to create a database in which there is a category to which there can be many subcategories ( like for a course book I can have physics, computer science, mathematics etc.).

Also there can be some categories which do not have subcategories (say Mythological Books).

The administrator has to add new category or subcategory via a form in PHP page which then updates the database.

How should I design the structure of my table where if admin for following two cases to be incorporated:

1. Admin only wants a subcategory to be added then upon selecting its parent category, it should be done.

2. Admin wants to add a new parent category and there exists no subcategory for it.

Please suggest how should I create my table(s).

Answer 1

I guess you should create table category with fields
id
parent_id
name

if it's root level category, its parent_id = 0,
otherwise its parent_id equals id of parent category.

Answer 2

You are talking about two entities, categories and subcategories. There is no further hierarchy (which would only make matters much more complicated). So I see two tables:

Category

• category_id = primary key
• name = not nullable

Subcategory

• subcategory_id = primary key
• name = not nullable
• category_id = not nullable, foreign key to Category table