If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
Design Question
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? |
#3
|
|||
|
|||
Design Question
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 |
#4
|
|||
|
|||
Design Question
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 |
Thread Tools | |
Display Modes | |
|
|