>Hi Folks,
>
>I have an interesting situation that I'd like to share for comment. A client of mine sells custom-designed products. Each product belongs to a general category, a subcategory, a sub-sub category, and is then classed in one of 4 ways. There are attributes common to each level and, finally, attributes that will only be needed with certain combinations of categories, subs, subs-subs and classes.
>
>Finally, the attributes vary tremendously on how the product is sold....
>
>I can't get into more specifics because I am under a confidentiality agreement, but has anyone any ideas on how to model this sort of thing in the data and at the UI level?
You might be able to use a structure I came up with that supports "n" number of groups/subgroups. It only requires two tables but is flexible enough so that you can add as many categories/levels as you'd like.
You could extend the "CategoryGroup" table to include the attributes common to all categories. Then maybe add another table that defines the custom attributes for each particular type of group and add a foreign key that points to it in the CategoryGroup table. This would let you define any combination of attributes for any level of any group.
I don't know how workable all of this is for your situation, but hope it helps (and I hope it makes sense).
Here are the table structures:
----------------------------------------------
There is a Categories table:
Categories----------
iid (I4) - Primary
category (C30)
It holds all the possible category names (at any level)
Then a Category Groups table:
CategoryGroups--------------
iid (I4) - Primary
group_id (I4)
fk_categoryid (I4)
level (I4)
This table defines all the possible combinations (groupings).
A sample of the data might look like:
Category
--------
iid category
1, Automobiles
2, GM
3, Ford
4, Daimler-Chrysler
5, Computers
6, Compaq
7, Dell
CategoryGroups
--------------
iid, group_id, fk_categoryid, level
1, 1, 1, (auto) 1
2, 1, 2, (GM) 2
3, 2, 1, (auto) 1
4, 2, 3, (Ford) 2
5, 3, 1, (auto) 1
6, 3, 4, (D-C) 2
7, 4, 5, (Computers) 1
8, 4, 6, (Compaq) 2
9, 5, 5, (Computers) 1
10, 5, 7, (Dell) 2
All entries that are "Level 1" are primary categories, level 2, 3, etc are
subcategories. So the grouping defined by the above data would look like:
Automobiles
- GM
- Ford
- Daimler-Chrysler
Computers
- Compaq
- Dell
It's easy enough to add more levels to any of the above categories. For
instance, if we wanted to add different divisions for GM, we could add:
Categories
----------
iid category
8, Chevy
9, Pontiac
CategoryGroups
--------------
iid, group_id, fk_categoryid, level
8, 6, 1, (Auto) 1
9, 6, 2, (GM) 2
10, 6, 8, (Chevy) 3
11, 7, 1, (Auto) 1
12, 7, 2, (GM) 2
13, 7, 9, (Pontiac) 3
Each CategoryGroup defines a full path from Primary category to ending
subcategory.
* Primary Categories
SELECT (blah blah) WHERE CategoryGroups.level == 1
