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
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
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
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:
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):
| id | name | fk_category | | -- | --------------- | ----------- | | 1 | Apple | 4 | | 2 | Orange juice | 3 |
| id | name | level | parent | | -- | -------- | ----- | ------ | | 1 | Fruits | 1 | null | | 2 | Domestic | 2 | 1 | | 3 | Juice | 3 | 2 | | 4 | Whatever | 3 | 2 |
%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".
%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
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?