Idea / Goal
I have two tables: Article
(represents a Product) and Category
. The relation is like this: An Article
can be assigned to only one Category
but a Category
may have at least one Article
s, therefore, 1:n (n >= 1)
.
Now, I want to build a category tree that holds up to 5 dependent Category
entries. The category-hierarchy is constant. I have given each Category
a level
field that tells me how deep I am in the tree (1-5, 1 being the main category, 5 being the last leaf possible). Now I still need to know how to navigate ... I thought that I could just set a parent_category
field that points to the upper category. The main categories, however, do not have a parent_category
respectively they are set to null
.
With this approach, I could query the databases for each level. This would make sense since I want to navigate through my categories and then see the sub-categories on entering a category (imagine a URL like: %host%/category/sub-category
).
Example
Please note: The data represented here is arbitrary. But the structure would be identical to this example below (I have omitted some other fields because they don't matter for this question):
Article
| id | name | fk_category | | -- | --------------- | ----------- | | 1 | Apple | 4 | | 2 | Orange juice | 3 |
Category
| id | name | level | parent | | -- | -------- | ----- | ------ | | 1 | Fruits | 1 | null | | 2 | Domestic | 2 | 1 | | 3 | Juice | 3 | 2 | | 4 | Whatever | 3 | 2 |
The URL %host%/fruits/domestic
would be resolved into a SQL-Query (simplified here) like:
SELECT name
FROM Category
WHERE level = 3
And then give me the names: "Juice" and "Whatever".
The URL %host%/fruits/domestic/whatever
would be resolved into a SQL-Query (simplified here) like:
SELECT name
FROM Article
WHERE fk_category = 4
This should give me "Apple" (... and any other Articles that are within that category).
I am not sure yet how I should implement it that the Category also knows if it is a node without children. I could set a boolean
for has_children
.
Question
How would you model this relationship? Is it okay to model it the way I had in mind? Is there a better alternative? Does it violate any rules?