?
Solved

Best way to design pricing table

Posted on 2014-09-23
5
Medium Priority
?
249 Views
Last Modified: 2014-09-24
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
Comment
Question by:Razzmataz73
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 35

Expert Comment

by:Dan Craciun
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

by:Razzmataz73
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 35

Expert Comment

by:Dan Craciun
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

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

Accepted Solution

by:
Dan Craciun earned 2000 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question