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?
Razzmataz73Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dan CraciunIT ConsultantCommented:
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
Razzmataz73Author Commented:
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
Dan CraciunIT ConsultantCommented:
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
Razzmataz73Author Commented:
So would I have 1 table per product with 52 rows?
0
Dan CraciunIT ConsultantCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.