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

After thinking about my own problem, I thought I'd think more about yours. I'm imagining your worst-case scenario as a database that contained everything there was to know about everything sold in a department store. Such a client would have to accept a very large number of tables, which would not store Waist and Length in the Lawnmowers table and would not store Engine Displacement in the Pants table. But I suppose that the stuff your client sells is all similar enough that it appears to belong in one table. I gather that different groups need different fields, mainly having to do with "how they're sold" or something. If the custom products all have a lot in common, maybe they can all go in one table, with several additional fields that contain information unique to individual groups and subgroups. You could have "charfield1" through "charfield10", each with length 100 or 255 or something sufficiently roomy, and numfield1 through 10 and so on. That other table described by Paul Mrozowski that describes the groups could also contain metadata that assigns captions, input masks, and validation rules to these fields appropriate for each group, maybe using the actual field properties and changing them in runtime (if that's a good idea - I don't know). For example, numfield4 could contain both the waist size of a pair of pants and the engine displacement of a chainsaw, with different input masks to conveniently input and display an integer like Size 32 or a decimal like 3.1 cu. inch. A client who anticipated making changes to the groups and couldn't afford to keep a programmer might need forms and some little builders that could be used to define groups and make captions and input masks.

You could make a compromise between these two models, if for example there were a few groups that were fairly constant and consistent and very different from each other. They would each get their own table and maybe their own table of metadata.