Razzmataz73
asked on
Best way to design pricing table
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?
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?
ASKER
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.
:)
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.
:)
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
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
ASKER
So would I have 1 table per product with 52 rows?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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