CODEWITHSUNDEEP
mysqlsqlselect
RecreatioN
RecreatioN

Reputation: 31

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

I am working on a project but a have a problem with designing of database. I need to store the price of something for every day in the year but i need to be able to change the prices for specific day.

Do i need to create a column for every day, in this approach i must create 365 columns in the table.

Do you have any better solution to this problem, any help will be appreciated, thank you.

Upvotes: 1

Views: 6037

Answers (4)

baao
baao

Reputation: 73241

You should create a table with 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 you can insert the date and price for every day, the columns created_at, updatedAt and id are automatically inserted (and updatedAt automatically updated), so you don't need to bother for them any more.

If you are saving those prices on a daily base and access the data later, you don't even need the date column, just use createdAt which, again, is automatically created on INSERT.

Once you have data, you can query for it like 

SELECT * FROM priceHistory WHERE DATE(`date`) = '2015-02-29';

You might also find mysql's documentation on DATE functions usefull.

Edit

As @murenik mentioned in his answer, the recomended way would be to create a relation to the table holding your product details, which you might have. To do this, change the productId statement to 

productId INT PRIMARY KEY REFERENCES products(id),

This will link those tables, making future queries easier and more effective. 

SELECT ph.*, p.* 
FROM products p 
INNER JOIN priceHistory ph on p.id = ph.productId

See mysql JOIN.

Upvotes: 6

Shockwave
Shockwave

Reputation: 412

just make a table for

id of the product | date | price

Upvotes: 0

user5378131
user5378131

Reputation:

We had a complex rate table at one place I worked, and instead of storing the rate for each day, we stored the rate on the first day it changed. So if Unit1 (of a motel for example) was $50 a night from January to April, then $65 a night over the summer during tourist season, then back to $50 a night in the fall, the rate table would have 3 records: 

Start Date         Close Date            Unit #   Rate
------------       -----------------     -------  -------
January 1, 2015    March 30, 2015        Unit1    $50
April 1, 2015      September 30, 2015    Unit1    $65
October 1, 2015    December 21, 2015     Unit1    $50

Then all you would need to do is find a rate record where your chosen date falls between the start and end date.

Upvotes: 1

Mureinik
Mureinik

Reputation: 311428

The classic solution would be to use a junction table, with a price per product per date:

CREATE TABLE product_prices (
    product_id INT REFERENCES products(id),
    price DECIMAL (5, 2),
    date DATE,
    PRIMARY KEY (product_id, date)
);

Upvotes: 2

Related Questions