Solved

Best way to design pricing table

Posted on 2014-09-23
5
229 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
  • 3
  • 2
5 Comments
 
LVL 34

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 34

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 34

Accepted Solution

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
This article discusses four methods for overlaying images in a container on a web page
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
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…

867 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now