Need to work out database structure

Question

Hi, Just need a little kickstart with this.

I have Mysql/PHP, and

I have 5,000 products. I have 30 companies

I need to store some data for those 30 companies for each product as follows:

a) prices

b) stock qty

I also need to store data historically on a daily basis.

So the table...

It makes sense that the records will be the products because there's 5000, and if I put the companies as the columns, I can store the prices, but what about the stock quantities? I could create two columns for each compoany, one for prices, one for qty. Then make the tablename the date for that day...so theer would be a new table for every day with 5000 products in it? is this the correct way?

Some idea on how I'll be retreiving data

the top 5 lowest prices (and the company) by product for a certain date
the price and stock changes in the past 7 days by product

Answer 1

Something like this should work:

Company
-------
CompanyID (PK)
Name

Product
-------
ProductID (PK)
CompanyID (FK)
Name

ProductHistory
--------------
ProductHistoryID (PK)
ProductID (FK)
Date
Price
Quantity