Need to work out database structure
- by jim smith
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