Design Question

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?

Lookup tables are the way to go.

HTH
--
-Larry-
--

"karbar" wrote in message
...
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?

On Wed, 1 Oct 2008 12:02:02 -0700, karbar
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
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