0

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?

1

1 Answer 1

2

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;
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.