That's very interesting, Paul, and thanks. I'm gonna see if I can apply this concept to the new model. I'll let you know what happens.

>>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

------------------------------------------------
John Koziol, ex-MVP, ex-MS, ex-FoxTeam.  Just call me "X"
"When the going gets weird, the weird turn pro" - Hunter Thompson (Gonzo) RIP 2/19/05