Design Question

On Wed, 1 Oct 2008 12:02:02 -0700, karbar
<karbar DeleteThis @discussions.microsoft.com> wrote:

>I am looking for some database design advice. I am attempting to create a
>inventory type database.
>
>I had envisioned One main table for products, with lookup tables to make it
>easy for the issuer to find what they are looking for when they issue it.
>For example if there were a number of main categories, each of the main
>categories would have sub categories which would further describe the item,
>and become available when a particular category was chosen. (Kind of a drill
>down approach)
>
>ie: Main Category: Shirt
> Second Level Category: Long Sleeve
> Third Level: Men's
> Fourth Level: Size Large
>
>Are Lookup tables for each of the categories and subcategories the wrong way
>to go? It's starting to look like there will be many, many, many lookup
>tables if I continue this way...Could someone suggest another approach to
>this? Am I way off base with the lookup table strategy?

In general, your idea of lookup tables is correct.

However, you may find that thinking of it as a hierarchy with Levels
won't work. For example, may different products might be considered
Men's, but you wouldn't want to create a different Men's value for
each Category and Subcategory, right?

Shirt - Long Sleeve - Men's
Shirt - Short Sleeve - Men's
Shirt - Tank Top - Men's
....

It would probably be better to make some of your lookup tables
independent of each other instead of hierarchical. For example, you
could have a Gender lookup table with Men, Women, Kids, etc. This
would not have anything to do with Category of Shirt, Pants, Belts,
Shoes, etc.

If you think you know what all the lookups are going to be, then you
can put a foreign key for each one in your Product table. You'll lose
the benefit of your cascading comboboxes, but you'll have much more
flexibility in setting up combinations with fewer redundant lookup
values.

But if you think they'll change and expand over time, or if you'll
need to link a Product with more than one lookup value from the same
table, then you'll need to use more of an Attribute approach.

This gets more complex, but basically you have a cross-reference table
that links Products with Attributes. Each Attribute (Men, Shirt)
belongs to an Attribute Group (Gender, Type). With the
cross-reference table you can now link a product with many different
attributes and use flexible queries to find them later.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

Armen and Larry,

Thank you both.

I hadn't thought of a cross-reference table like that, I think after reading
your posts, I will take a "combined" approach, a mix of lookup tables and a
cross ref table.

Many thanks,

Karen



"Armen Stein" wrote:

> On Wed, 1 Oct 2008 12:02:02 -0700, karbar
> <karbar DeleteThis @discussions.microsoft.com> wrote:
>
> >I am looking for some database design advice. I am attempting to create a
> >inventory type database.
> >
> >I had envisioned One main table for products, with lookup tables to make it
> >easy for the issuer to find what they are looking for when they issue it.
> >For example if there were a number of main categories, each of the main
> >categories would have sub categories which would further describe the item,
> >and become available when a particular category was chosen. (Kind of a drill
> >down approach)
> >
> >ie: Main Category: Shirt
> > Second Level Category: Long Sleeve
> > Third Level: Men's
> > Fourth Level: Size Large
> >
> >Are Lookup tables for each of the categories and subcategories the wrong way
> >to go? It's starting to look like there will be many, many, many lookup
> >tables if I continue this way...Could someone suggest another approach to
> >this? Am I way off base with the lookup table strategy?
>
> In general, your idea of lookup tables is correct.
>
> However, you may find that thinking of it as a hierarchy with Levels
> won't work. For example, may different products might be considered
> Men's, but you wouldn't want to create a different Men's value for
> each Category and Subcategory, right?
>
> Shirt - Long Sleeve - Men's
> Shirt - Short Sleeve - Men's
> Shirt - Tank Top - Men's
> ....
>
> It would probably be better to make some of your lookup tables
> independent of each other instead of hierarchical. For example, you
> could have a Gender lookup table with Men, Women, Kids, etc. This
> would not have anything to do with Category of Shirt, Pants, Belts,
> Shoes, etc.
>
> If you think you know what all the lookups are going to be, then you
> can put a foreign key for each one in your Product table. You'll lose
> the benefit of your cascading comboboxes, but you'll have much more
> flexibility in setting up combinations with fewer redundant lookup
> values.
>
> But if you think they'll change and expand over time, or if you'll
> need to link a Product with more than one lookup value from the same
> table, then you'll need to use more of an Attribute approach.
>
> This gets more complex, but basically you have a cross-reference table
> that links Products with Attributes. Each Attribute (Men, Shirt)
> belongs to an Attribute Group (Gender, Type). With the
> cross-reference table you can now link a product with many different
> attributes and use flexible queries to find them later.
>
> Armen Stein
> Microsoft Access MVP
> www.JStreetTech.com
>
>