Okay then, let's look at only two levels of my model suggestion to keep things simple:BDKR wrote:Where is the redundancy coming from? You can use one table to store a heirachal structure of infinite depth without redundancy. If there is something I'm missing here, please explain.
Code: Select all
MainCategory { _mctId_, mctName, ... };
SubCategory { _sctId_, sctMainCategoryId_, sctName, ... };Code: Select all
MainCategory
--------------------------------------------------------------------
mctId | mctName
--------------------------------------------------------------------
1 | Books
2 | Music
3 | Cars
--------------------------------------------------------------------
SubCategory
--------------------------------------------------------------------
sctId | sctName | sctMainCategoryId
--------------------------------------------------------------------
1 | Horror | 1
2 | Romantic | 1
3 | Rock | 2
4 | Pop | 2
5 | European | 3
6 | American | 3
--------------------------------------------------------------------Code: Select all
Category
--------------------------------------------------------------------
catId | catSubName | catMainName
--------------------------------------------------------------------
1 | Horror | Books
2 | Romantic | Books
3 | Rock | Music
4 | Pop | Music
5 | European | Cars
6 | American | Cars
--------------------------------------------------------------------But only under the condition that the main category entity does not have further specification of its own!