mysql - How can I store price of something for every day of the year in database? -

i working on project have problem designing of database. need store price of every day in year need able change prices specific day.

do need create column every day, in approach must create 365 columns in table.

do have better solution problem, appreciated, thank you.

you should create table 6 columns.

create table if not exists pricehistory (     `price` decimal(8,2) not null,     `date` datetime,     `productid` varchar(50),     `id` int not null auto_increment primary key,     `createdat` timestamp default now(),     `updatedat` timestamp ) engine = innodb; 

now can insert date , price every day, columns created_at, updatedat , id automatically inserted (and updatedat automatically updated), don't need bother them more.

if saving prices on daily base , access data later, don't need date column, use createdat which, again, automatically created on insert.

once have data, can query

select * pricehistory date(`date`) = '2015-02-29'; 

you might find mysql's documentation on date functions usefull.

edit

as @murenik mentioned in answer, recomended way create relation table holding product details, might have. this, change productid statement

productid int primary key references products(id), 

this link tables, making future queries easier , more effective.

select ph.*, p.*  products p  inner join pricehistory ph on p.id = ph.productid 

see mysql join.