Help with Table Design

[Mobbbx]

I am trying to create a pricing database. So far, i have 3 tables.

Vendor Table
VendorID
Name
Address

Item Table
Item#
Description

Price Table
PriceID
Item#
VendorID
Price
Date Entered

Each vendor can sell many items and prices change over time. One item can be
sold by different vendors under different prices. i think the table design is
incorrect, but i do not know how to solve it. Any help is greatly appreciated.

 

[Mobbbx]

I would also want past prices to be stored in the database as new prices are
entered. This way, i can analyze the past prices.

Once again, any help would be greatly appreciated.

 

[John Spencer]

As far as the table strucure goes it is correct. I would assume that there
will eventually be other tables to handle SALES to CUSTOMERS for instance.

The structure you have will allow you to have multiple prices per item and
vendor. I guess that Date Entered field (by the way avoid spaces in table
and field names) could be the start date that the price for this item and
vendor becomes effective. That way, when the Vendor changes the price you
have a way to identify which of the price records you want based on the date
of sale.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[tedmi]

To add to John's point about multiple prices: In the PriceTable, you need to
set a unique compound index on Item#, VendorID and DateEntered - this will
prevent the entry of multiple prices for the same Item/Vendor/Date
combination.