Solved

# Best way to design pricing table

Posted on 2014-09-23
234 Views
I have bookable products whose prices change every week.

Currently they are in a database with 52 pricing field (1  each week of the year).

I am looking for a better design plan.
Is there a better way to do this?  Or do you think that is the best way?
0
Question by:Razzmataz73
• 3
• 2

LVL 34

Expert Comment

ID: 40340754
If you're concerned about speed, having them all in one table and selecting directly the field is the fastest way.

But I would adopt a more flexible way of storing that data:
id
date_start
date_end
price

This way you're no longer constrained by an arbitrary time unit (week) and can select the data like this:
SELECT price WHERE date_start >= '2014-xx-xx' and date_end <= '2014-xx-xx'

HTH,
Dan
0

Author Comment

ID: 40341942
How would that work if I have 52 prices (1 price per week) per product?
Would it be
id
date_start1
date_end1
price1
date_start2
date_end2
price2
date_start3
date_end3
price3

Or am I missing something?
I would like to keep it in one table if possible.
:)
0

LVL 34

Expert Comment

ID: 40341966
No, you would have something like this
id   |   date_start   |    date_end   |   price
1    |   2014-01-01 | 2014-01-07  |  50
2    |  2014-01-08  | 2014-01-14  |  45
3    |  2014-01-15  | 2014-01-21  |  52
etc
0

Author Comment

ID: 40341996
So would I have 1 table per product with 52 rows?
0

LVL 34

Accepted Solution

Dan Craciun earned 500 total points
ID: 40342317
No. If you have multiple products, you could have a products table:
id | name | quantity | bunch of other fields

and then the price table
id |product_id|date_start|date_end| price

You would get the price for a particular date ('20yy-mm-dd') like this

SELECT name,..other fields.., price
FROM product JOIN price
ON product.id = price.product_id AND date_start >= '20yy-mm-dd' AND date_end <= '20yy-mm-dd'

This complicates the queries, but has the merit that you can do the math directly in SQL (I assume now you calculate outside SQL in what week you want the price and then select it)
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question