Database design question

[audmkamp]

I'm creating an access database that I will use to track prices of two items from each of 25 competitors. Prices for the two items from all 25 companies will be entered twice weekly. In the past the company has kept the price records by printing out a page in table format every day. I want to be able to put all 5 years of the price history in an access database (good intern project for this summer!)



I want to be able use the price data to create charts showing price history by company & compare to ours, compare current prices to 5-year averages, etc.

I have experience in modifying existing Access databases, and creating forms from existing tables with relationships, but struggle with setting up the initial tables and relationships from scratch.

Can someone suggest how many tables to include and what items should be in each?



Ideally I want to create a form to make the updates to the database that would look much like our printouts now to make data entry easier. What I envision is shown below with the date at the top, list the 25 competitors down the side and have columns for the two price items. Does this seem reasonable/doable?

Date ______
------------------Item1 ------Item2
Competitor1
Competitior2
Competitior3
Competitor....

[alansidman]

PriceTable
----------
RecordID PK autonumber
CompetitorID FK Integer
PriceDate Date/Time
Item FK Integer
Price Currency

competitorrTable
------------
CompID PK autonumber
CompName Text
Other Pertinent comp Fields

ItemTable
---------
ItemID PK autonumber
ItemDescription
Other Item fields as necessary

Based upon what you have indicated, this is the approach I would take. I would then build the forms based upon these tables. Your queries which will be the basis for your charts can then be easily built and analyzed. Using this approach, if you decide to add more items to track it is readily available.

Here is a good read on table normalization and db design

http://www.deeptraining.com/litwin/d...aseDesign.aspx

Alan

[audmkamp]

Thanks for your response. Should give me a good start. Will work on it in the coming days & leave further questions here if any.