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?