Need to work out database structure
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 productSomething like this should work:
Company
-------
CompanyID (PK)
Name
Product
-------
ProductID (PK)
CompanyID (FK)
Name
ProductHistory
--------------
ProductHistoryID (PK)
ProductID (FK)
Date
Price
Quantity
精彩评论