How Many Tables and Relationships

[TC]

One table for items. This saves values that are common to the item across
all stores; eg. item name, >default< price.

Another table for stores. This saves values that are common to the store,
regardless of items; eg. store name & location.

A junction table containing ItemID and StoreID. This saves values that are
common to a >specific< item/store combination; eg. the actual price of that
item at that store.

The primary key of the junction table is a so-called "composite"
(multi-column) primary key of ItemID and StoreID (ie. two fields in the
primary key).

HTH,
TC


The item table st

 

[XXXXXX]

I am working on a project for school that is asking me to create a database
to use as a grocery list. I have been given few parameters but it is really
throwing me. I've been asked to create a db to capture and report the fol
info:

ItemName (milk, bread, etc)
Quantity
Unit (i.e. litre, doz. lb, etc)
UnitPrice
StoreName
StoreType (butcher shop, grocery, deli, etc)
StoreAddress

Given these info requirements I have been asked to set up as many tables as
I need to capture this info. I need to define relationships and also create
a form to use to input the shopping list and then create a report to use to
go shopping. I know I have to create 'itemID' and 'storeID' fields to use
as primary keys in their respective tables

My problem is how do I handle the issue of multiple items being for sale at
multiple stores, all with potentially different prices? I realize I will
likely need to create a junction table to use a many-to-many relationship
(storeID-storeID / itemID-itemID) but how do I account for the possibility
of the different prices for the same item and different stores?

This sounds like an easy problem, but I think I've just confused myself.
Can anyone help.

Thanks,

SWF
(e-mail address removed)