SQL Database design question

I have a task to design a SQL database that will record values for the company's commodity products (sku numbers). The company has 7600 unique product items that need to be tracked, and each product will have approximately 200 values over the period of a year (one value per product 开发者_如何学Cper day, over the period of a year).

My first guess is that the sku numbers go top to bottom (each sku has a row) and each date is a column.

The data will be used to view in chart / graph format and additional calculations will be displayed against those (such as percentage profit margin etc)

My question is:

- is this layout advisable?

- do I have to be cautious of anything, if this type of data goes back about 15 yrs (each table will represent a year)

Any suggestions?

It better to have 3 columns only - instead of many as you are suggesting:

sku    date         value
-------------------------
1      2011-01-01   10
1      2011-01-02   12
2      2011-01-01   5

This way you can easily add another column if you want to record something else about a given product per date.

I would suggest a table for your products, and a table for the historical values. Maybe create an index for the historical values based on date if you plan to select for specific time periods.

create table products (
 id number primary key,
 sku number,
 name text,
 desc text);

create table values (
 id number primary key,
 product_id number,
 timestamp date,
 value number,
 foreign key fk_prod_price product_id on product.id);

create index idx_price on values.timestamp;

NOTE: not actual sql, you will have to write your own

If you do like @fiver wrote, you don't have to have a table for each year either. Everything in one table. And add indexes on sku/date for faster searching