Proper table structure

[Guest]

I am designing a database for my wifde to track product orders. she has
hundreds of products that can be divided into about 10 categories. each
category can have several subcategories and then you may be left with 10-20
products (a much easaier amount to sort through) i am unsure of how to set
up tables to acount for this. their is a distinct product
category/subcategory heirarchy that has to be followed. i tried a product
table with category and subcategory fields, but then she could potentially
assign a product a subcategory that didnt belong to a category. then i tried
a products, categories, and subcategories separate tables and this seems more
difficult to work with.

the goal is to have a form where she selects a category from a combo box.
then another combobox is populated only with the subcategories under that
category, and then when a subcategory is selected an list box is populated
with the resulting products. wow, that was a lot. sorry to be so long
winded, but any help is appreciated. i want to make sure i start down the
right path.

 

[Guest]

I think that the product table with category and subcategory fields is the
way to go. Then create separate tables for categories, and subcategories and
set a one-to-many relationship the the corresponding fields in the product
table.

On your data entry form use a combobox with autoexpand set to Yes.

 

[Guest]

how does that help preserve the heirarchy of categoyry--> subcategory

 

[Guest]

What you need is 3 tables
Category:
ID
SubCategory:
ID
CategoryID (points to Category table ID)
Product:
ID
SubCategoryID (points to SubCategory table ID)

There are two sets of relationships SubCategory to Category and Product to
SubCategory both many-to-one.

Of course this assumes that you can't have any products directly under a
Category

-Dorian.

 

[tina]

suggest three tables, as

tblCategories
CategoryID (primary key)
CategoryName
<other fields that describe a category, and nothing else>

tblSubCategories
SubID (primary key)
CategoryID (foreign key from tblCategories)
SubName
<other fields that describe a subcategory, and nothing else>

tblProducts
ProductID (primary key)
SubID (foreign key from tblSubCategories)
ProductName
<other fields that describe a product, and nothing else>

the relationships are
tblCategories.CategoryID 1:n tblSubCategories.CategoryID
tblSubCategories.SubID 1:n tblProducts.SubID

note that there is no direct link between tblCategories and tblProducts;
that is correct, because products are *indirectly* linked to categories
through the direct link between products and subcategories (which are
directly linked to categories).

you should examine the *real world* relationships, though. my first question
would be: can a product fit into more than one category? and/or into more
than one subcategory? if so, then the table setup outlined above is not
appropriate. the bottom line is, YOU know (or your wife knows) more about
the entities involved than anyone who reads your post can. i strongly
recommend that you learn the basics of relational design principles
(normalization); once you've done that, you'll be in the best position to
structure your tables/relationships correctly - and you'll have a much
better understanding of the context of any responses you get here in the
newsgroups. for more information, see
http://home.att.net/~california.db/tips.html#aTip1.

hth

 

[Guest]

OK, that sounds like what i want. a product can only have 1
category/subcategory and a subcategory can only belong to one category.
i am trying to allow the user to select a category from a combobox then have
another combobox populated with the resulting subcategories, and eventually a
listbox populated with the resulting products. i have a query that retuns
the resulting subcategories to another combobox with the category selected on
a form, but if i change the first combobox, the second doesnt repopulate with
the new subcategories as a result of the query changing. i guess, how can i
have a cvhange in the first combobox re-run the query?

 

[tina]

add a line of code to the first combo box control's AfterUpdate event
procedure, as

Me!SecondComboBoxName.Requery

hth