Database Design Question - Categories/Subcategories

Database Design Question - Categories / Subcategories

Your design is appropriate. I'm a database guy turned developer, so I can understand the inclination to have Category and SubCategory in one table, but you can never go wrong by KISS.

Unless extreme performance or infinite hierarchy is a requirement (I'm guessing not), you're good to go.

If being able to associate multiple subcategories with a product is a requirement, to @Mikael's point, you would need a set-up like this which creates a many-to-many relationship via a join/intersect table, Product_SubCategory:

CREATE TABLE Product (ProductID int, Description nvarchar(100))
CREATE TABLE Product_SubCategory (ProductID int, SubCategoryID int)
CREATE TABLE SubCategory (SubCategoryID int, CategoryID int, Description nvarchar(100))
CREATE TABLE Category (CategoryID int, Description nvarchar(100))

Hope that helps...

Eric Tarasoff

database design - categories and sub-categories

I would use this structure:

ParentId, CategoryId, Title
null, 1, Home
null, 2, Business
null, 3, Hobbies
1, 4, Gardening
1, 5, Kitchen
1, 6, ...
2, 7, Development
2, 8, Marketing
2, 9, ...
3, 10, Soccer
3, 11, Reading
3, 12, ...

In detail:

• only use one table, which references itself, so that you can have unlimited depth of categories
• use technical ids (using IDENTITY, or similar), so that you can have more than 10 subcategories
• if required add a human readable column for category-numbers as separate field

As long as you are only using two levels of categories you can still select like this:

SELECT BizTitle FROM tblBiz WHERE ParentId = 3 AND CategoryId = 11

---

The new hierarchyid feature of SQL server also looks quite promising: https://msdn.microsoft.com/en-us/library/bb677173.aspx

---

What I don't like about the Nested Set Model:

• Inserting and deleting items in the Nested Set Model is a quite comlicated thing and requires expensive locks.
• One can easily create inconsistencies which is prohibited, if you use the parent field in combination with a foreign key constraint.
  
  
  • Inconsistencies can appear, if rght is lower than lft
  • Inconsistencies can appear, if a value apprears in several rght or lft fields
  • Inconsistencies can appear, if you create gaps
  • Inconsistencies can appear, if you create overlaps
• The Nested Set Model is in my opinion more complex and therefore not as easy to understand. This is absolutely subjective, of course.
• The Nested Set Model requires two fields, instead of one - and so uses more disk space.

Design database for category, subcategory and associated books

There's no reason to have more than one table for "categories", whether it be a top-level category or a sub-category. They're all just "categories".

So, have a single table called "categories", with a parent_id field:

// categories table
id
name
user_id
parent_id

When you want to pull all top level categories, just run your query against the categories table with a condition that parent_id is null.

Then, when you want to pull sub categories, just run the query against the categories table with a condition that parent_id = 123 (or whatever).

Not only does this keep everything a lot cleaner, but it also allows for expansion in case you want to continue adding sub-sub-sub-sub categories...etc.

---

Another option is to use CakePHP's TreeBehavior.

I personally just rather use the way I suggested above, but might just be because I haven't taken the time to really understand this behavior enough.

Database Design - Multiple Category and Multiple Sub Category

Just create another table StoreCategories, that will keep the StoreID and CategoryID to allow each Store to have multiple Categories and each Category to have multiple Stores.

Make sure Foreign Keys are properly placed for each tables.

Another intermediary table can be placed if a SubCategory can be used on multiple Categories.

Obviously to avoid double SubCategories.

How to model a hierarchy of (sub)categories in a Class Diagram and in an SQL Table Model?

A hierarchy (or tree) of categories is modeled with a recursive one-to-many association, which associates the Category class with itself, as shown in the following diagram:

Such an Information Design Model can be transformed into an OOP Class Model and into an SQL Table Model, like so:

Notice how the superCategory column is designated as a foreign key referencing the categories table in the table model: by a UML dependency arrow stereotyped as «fk».

I hope you can figure out yourself how to code the SQL Table Model in an SQL Create Table statement.

p.s.: For more about how to model databases with UML CLass Diagrams seee https://stackoverflow.com/a/21394401/2795909

what is the best way to represent nested subcategories in relational database for say ecommercee store

Categories and sub-categories are the same entity category. Your entity has a relation with itself that you can express as:

category [parent] (0, n) - (0, 1) category [child]

A parent category can have 0 to many child categories.
A child category can have 0 or 1 parent category.

Of course, if a "child category" has no parent, it isn't really a "child" category.

This gives us the max cardinalities of n (many) on the left side (parent) and 1 on the right side (child).
A "One To Many" (or "Many To One") relation implies to add a foreign key in the child entity (to the side where the max cardinality is 1). As a category may not have a parent, this column will be nullable.

Conclusion: add a nullable column in your category table with a foreign key constraint on itself.

Your table could look like this:

---