Design: Categories, Sub-Cats, Sub-Sub-Cats, Sub-Sub-Sub

[redwoodly]

I have been working with Access for many years and databases longer than that. The fact that I'm having so much trouble with the design of Categories, Sub-Categories, and Sub-Sub Categories just kills me! I don't understand my problem....mental block or something.

I have 9 Unit Types.
Then 5 main Categories that ALL apply to ALL Unit Types. (e.g., HW, SW, Firmware)

For each Category, they have Sub-CAtegories (Equipment) that are specific to Unit Types and dependant on the Category selected. (some apply to multiple unit types, multiple Categories like Computer - it has HW, SW, FW).

Then there are sub-sub-categories (components) that are also dependant on Unit Type and Equipment and Category. These are also duplicated across the equipment. However, at this time, this level is only used for the HW Category.

These are then assigned to Help Calls.
each level is optional. I.e., maybe only a Category is applicable & assigned, sometimes it will go two levels down; and sometimes 3 levels down (with hardware).

How to design this?
I started with tblUnitType, tblCat, tblEquip, tblComp.
tblCat is independant of unit type, so I didn't include the "parent" unit type until tblequip. So tblequip has 2 parents: cat & unit type.
tblComp has 1 parent: equip.

then tblHelp has 3 fields: CatID, EquipID and compID.
I don't like this design.
because if they decide that 1 component should be reassigned to a different Equipment, my tblHelp won't reflect this change.

so then it should be tblHelp only records 1 ID. That ID reflects all the levels. (i.e., one large master table with all the fields....). But that goes against the grain with normalization, doesn't it? repeating the equipment and components?

ay yi yi...
recommendations? suggestions?
thank you for any help.

 

[CajunCenturion]

I would start out with five basic tables, each which contains all of the valid entries for that particular type of entity.

tblUnit          tblCategory        tblEquipment
   UnitID           CatID              EquipID
   ...              ...                ...

tblComponent     tblHelp
   CompID           HelpID
   ...              ...

To establish the relationship and valid combinations of Unit, Category, and Equipment, I would have the following table:

tblRelationship
   RelationshipID
   UnitID
   CatID
   EquipID
   CompID

This allows you selectively attach a specific component with a specific piece of equipment up through the category and unit, simply via their ID numbers, thus not having to duplicate either categories or unit data. And it's fairly easy to mix and match these four categories to reflect the full configuration, with each combination having its own unique relationship id. Thus you can reassign a component, by simply changing the EquipID within tblRelationship which will solve at least of the Help calls issues provided that the help call is associted with the RelationshipID.

With respect to the help files, there are several options worth consideration, in addition to the RelationshipID, but I do have a couple of questions. You state that each level is optional. Does that mean that you can have a help call associated to a component without piece of equipment without regards to the category or the unit? Is there an implied heirarchy with respect to what is optional?

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein