Parent Category - Sub category and Product database organization

Question

I want to make a product management software. I have many products, each product belong to one category (n-1), each category can have parent category or not (when its a top category). I want to build a "dynamic" database schema which allow me to expand later. Ex: Audi will have 2 sub-category is Audi Sport and Audi Luxury in the future. If a category have sub-category, it cannot contain product itself, but child-categories only. Only the leaf categories can contain

Vehicle

My current schema is:

Category(ID, Name, ParentCategoryID)

Product(ID, CategoryID)

But it seem to not satisfy my request

Answer 1

Since only leaf categories may hold products, I would suggest the following structure:

TblCategory 
(
    Category_Id int PRIMARY KEY,
    Category_Name nvarchar(30) -- or whatever length suits your needs
    Category_Parent_Id int NULL FOREIGN KEY REFERENCE TblCategory(Category_Id),
    CONSTRAINT UC_Category UNIQUE (Category_Name)
)

TblLeafCategory
(
   LeafCategory_Id PRIMARY KEY int,
   LeafCategory__Name nvarchar(30) -- or whatever length suits your needs
   LeafCategory_Parent_Id int NOT NULL FOREIGN KEY REFERENCE TblCategory(Category_Id),
    CONSTRAINT UC_Category UNIQUE (LeafCategory_Name)
)

TblProduct
(
     Product_Id int PRIMARY KEY,
     Product_LeafCategory_Id int NOT NULL FOREIGN KEY REFERENCE TblLeafCategory(LeafCategory_Id),
     -- Other product columns
)

When adding a sub category to any leaf category you will have to first copy it's name and parent_Id to the TblCategory, rename the existing leaf category to it's new name (i.e if the base was Audi and the leaf was Sport, you should add the Sport category to TblCategory, and rename the leaf to Classic) and then add the new leaf category and update the products table that should belong to it (in the cars example, add Modern as a leaf category where it's parent is Sport, and update the products that should go there from the Classic category).

Though it seems like a cumbersome way to handle splitting leaf categories, It can be handled with a single stored procedure (preferably with a transaction inside), and this database structure ensures that only leaf categories can have products.