Database Design Advice

[Keith Ward]

Hi,

Using Access 2002.

Creating a Product database that requires the following:

1. Categories - Table Done
2. Promotion - Table Done (Special Offer, Web only Offer, clearance and so)

3. Products - Now I want to be able to place products into multiple
categories and also have multiple promotion status.

What's the best way to go about this?

My method so far.

Created a table CategoriesWithProductsIn - Table has ID, CategoryID (linked
to categories table) ProductID (linked to Products Table)

Same again for promotion.

Troubles I'm having are creating forms that will display the products and
let me insert new ones.

I want to display products sorted by Category Then By name. I also then want
to be able to edit the products properties including what category they are
in and add them into other categories.


Regards

Keith

 

[tina]

so you have a tblProducts, tblCategories, and tblPromotions. the primary key
field of tblProducts is ProductID; the primary key field of tblCategories is
CategoryID; the primary key field of tblPromotions is PromotionID.

tblProducts has a many-to-many relationship with tblCategories: one product
may fall into many categories, and one category may include many products.
in Access, you can't use a many-to-many relationship. instead, you use a
"linking" (child) table to create two one-to-many relationships, thus
linking the two (parent) tables *indirectly*. tblProductCategories is the
linking table; it contains two foreign key fields - ProductID and
CategoryID, from those tables respectively.

so, tblProducts has a one-to-many relationship with tblProductCategories:
each product record may be related to many product-category records, but
each product-category record is related to only one product record.

tblCategories also has a one-to-many relationship with tblProductCategories:
each category record may be related to many product-category records, but
each product-category record is related to only one category record.

the relationships above are identical for tblProducts - tblPromotions. read
it again, and just replace tblCategories with tblPromotions, and replace
tblProductCategories with tblProductPromotions, and you've got it. the
foreign key fields for tblProductPromotions are ProductID and PromotionID.

from your post, it appears you've already set up the above
tables/relationships; i just used slightly different names. if your setup
does *not* match the above relationships, you need to fix it.

usually the easiest way to present "linking table" data in forms, is with
subforms using combo boxes:
create a mainform bound to tblProducts.
create a subform bound to tblProductCategories. set the LinkChildFields and
LinkMasterFields properties in the subform control to ProductID. bind the
CategoryID field to a combo box. set the combo box RowSource to
tblCategories.
create a second subform, bound to tblProductPromotions. set the
LinkChildFields and LinkMasterFields properties in the subform control to
ProductID. bind the PromotionID field to a combo box. set the combo box
RowSource to tblPromotions.

hth

 

[Keith Ward]

Thanks Tina,

Looks Like I got it spot on then with the table design.

I have the Forms done the way you suggest, but was unable to Order the form
by Categories as they aren't listed in the Products table to Order.

I realised however that I can create a form bound to a query and hey presto
I have a form that does what I want. Well Nearly. When editing or inserting
a new product I would like the form to re-run the query so the products are
displayed properly. This is easily achieved by just ordering my Categories
field again but would be nice if it could do it automatically.

I will probably investigate some code to do this after certain fields are
updated.

Thanks for the advice, its nice to know I got it right as I thought I was
over complicating it.

Regards

Keith