Solved

Best way to design pricing table

Posted on 2014-09-23
5
234 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
mysql ide 10 39
UPDATE query not working in mysqli php 8 49
Display images from mysql blob type (Not working) 9 26
does post require a form or curl to be post 4 29
This article discusses four methods for overlaying images in a container on a web page
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
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 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 …

785 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