CODEWITHSUNDEEP
databasedatabase-design
Barış Velioğlu
Barış Velioğlu

Reputation: 5817

Category that can be sub category of more than one category?

I have a different situation that a category can be a sub category of more than one category. How should I design this ?

The tree structure below doesnt solve my problem I guess. Should I do ParentCategoryId as a string and can take multiple category id in it. Then do calculation to resolve parent categories or do you advise me another solution ? What is your optimum solution to solve this problem ?

--Category--

Id
ParentCategoryId
CategoryName

Thanks in advance,

Upvotes: 1

Views: 127

Answers (2)

user1494736
user1494736

Reputation: 2400

In addition to what John Clark said, it's also a good idea to have a precalculated table of all the category of your things like:

Item Direct Categories
+------------+------------+
|itemId      |categoryId  |
+------------+------------+
|1           |1           |
+------------+------------+
|3           |2           |
+------------+------------+

and then

Item All Categories
+------------+------------+
|itemId      |categoryId  |
+------------+------------+
|1           |1           |
+------------+------------+
|3           |2           |
+------------+------------+

In the first one you'll only save the categories of your items, but in the second one you'll also associate which each item the super categories of their categories, so you can check which just a single query if an item has a category or not.

Upvotes: 1

John Clark
John Clark

Reputation: 81

The standard SQL solution for this is a cross table. You would have two tables, categories and categories_x as follows:

categories
+------------+------------+
|categoryId  |name        |
+------------+------------+
|1           |foo         |
+------------+------------+
|2           |bar         |
+------------+------------+
|3           |huh         |
+------------+------------+

categories_x
+------------+------------+
|categoryId  |parentId    |
+------------+------------+
|3           |1           |
+------------+------------+
|3           |2           |
+------------+------------+

To get the parents of category 3 your sql query would look like:

SELECT categoryId, name 
  FROM categories 
  WHERE categoryId IN (SELECT parentId FROM categories_x WHERE categoryId = 3)

Upvotes: 2

Related Questions