What should be the schema of the database if the parent table is being referenced in more than one child table?

Question

I have three entities in my database: Category, SubCategory, and Product. Now the thing is SubCategory is dependent on Category, whereas Product can be dependent on SubCategory or directly either on Category. How should I design the database for such a situation?

As of now, I have created these tables as below:

Category:

• CategoryId
• CategoryName
• HasSubCategory

SubCategory:

• SubCategoryId
• CategoryId
• SubCategoryName

Product:

• ProductId
• CategoryId
• SubCategoryId
• ProductName

Now, while creating a Product, if the selected Category's HasSubCategory is true then SubCategory would be selected by the user. Now consider a situation that the user selects a Category whose HasSubCategory is true, and based on the selected Category, SubCategories will be loaded and after selecting a SubCategory, the user submits the Product record. Now, what if the user modifies the SubCategory record which was selected in the Product, and changes its dependent Category. In this case, I need to update the Product record as well and change its Category as per the new Category selected in the SubCategory record.

Is there any better approach to handle such a situation or I shall manage like this only?

Answer 1

My approach in a model:

-- Categories and subcategories table
CREATE TABLE category (id INT PRIMARY KEY, 
                       name VARCHAR(255), 
                       parent_id INT,
                       FOREIGN KEY (parent_id) REFERENCES category (id));

-- Products table
CREATE TABLE product (id INT PRIMARY KEY,
                      name VARCHAR(255),
                      category_id INT,
                      FOREIGN KEY (category_id) REFERENCES category (id));

How it works: DEMO (MySQL used).

The queries are converted to procedures - only for simplifying their execution call.

If some section is empty we don't display it.

If we want to get the categories list with the info does they have subcategories we simply execute

SELECT *, EXISTS ( SELECT 1
                   FROM category t2
                   WHERE t1.id = t2.parent_id ) has_subcategory
FROM category t1;