-1

I'm currently designing a database for my company's website. I would like to create a table which will hold information (logic) about our discounts so that I can just call a function with a discountID and my php code will calculate the new price. My question is, every discount has different logic. ex. 10% off, $10 off, by one get one free, buy 3 get 50% off...

How can I store this in the database so that my code can calculate the discount on its own?

Diane
  • 119
  • 3
  • 9
  • 1
    [What have you tried?](http://www.whathaveyoutried.com/) – Matt Aug 16 '12 at 16:14
  • `discount_type (PERCENTAGE_OFF, AMOUNT_OFF, BUY_X_GET_ONE_FREE, ... ), discount_value ( ... )`, then add some code... – deceze Aug 16 '12 at 16:16
  • 1
    not up to writing the code yet - first designing the database. But I'm trying to think ahead. – Diane Aug 16 '12 at 16:26

3 Answers3

3

A database does not store logic - only data. Split this into three fields:

  • discount amount (int)
  • discount type (enum; possible values: 'percent', 'amount')
  • buy one get N free (int; the number you get free for every one bought)

So for one discount you might store the following:

| discount amount | discount type | buy one get one free |
----------------------------------------------------------
|       10        |   'percent'   |                      |

For another, where BOGOF applies:

| discount amount | discount type | buy one get N free |
----------------------------------------------------------
|                 |               |         1          |

Then it would be up to your PHP to receive these values and act accordingly.

Mitya
  • 33,629
  • 9
  • 60
  • 107
  • Perhaps you could generalize more for `buy X get Y free/ get Z% off`. – Niloct Aug 16 '12 at 16:22
  • True. This is not an exhaustive answer - I was just trying to get him on the right lines, i.e. keeping logic out of a DB. – Mitya Aug 16 '12 at 16:23
  • Seems like I should have a table with tons of columns specifying all logic. discountType, discountAmount, ExpirationDate, PromoCode... – Diane Aug 16 '12 at 16:29
  • Lots of columns isn't necessarily a bad thing. It's a sign of a well-normalised database. (This also means splitting into different tables, where necessary). Don't think of databases as storing logic - they don't. They store data. Your environment (e.g. PHP) does the logic. – Mitya Aug 16 '12 at 16:45
  • I want to be able to constantly add new discounts and coupons without having to change or rewrite my code everytime. I should just have to enter some data in the database and it's up and running. So where does the logic go? Thanks for your patience. – Diane Aug 16 '12 at 16:56
  • The code. This is the only place for logic. Your wish is unrealistic - you cannot expect a database to handle dynamic situations. You need to first plan the eventualities, and prepare your DB model along those lines, and then write your code to handle those eventualities. If you don't do this up front, you will be faced with changing the code each time. There's no magic bullet. You need cold, hard, abstract logic. – Mitya Aug 16 '12 at 17:08
  • I'm not sure I understand your answer. How do major businesses handle their discounts/promotions? They write code for every new discount? – Diane Aug 16 '12 at 17:18
  • Not every discount, but for every TYPE of discount, yes. So code for buy-one-get-N-free, code for basic percentile discounts. The table structure I provided above allows you to log discounts of both these types. Your code would then need, for the particular product in question, to see which fields had values, then act accordingly. You asked how to store this stuff in the database, which I have answered. It seems you have a separate question about what the code should be to process these concepts. – Mitya Aug 16 '12 at 17:21
  • The table structure was great! Just what I needed to get me started. I just want to generalize as much as possible so that things can happen more automatically. The code itself is for later which I'll hopefully get to very soon. I'll just follow your way for now and see for myself. Thanks for the heads up! – Diane Aug 16 '12 at 17:26
1

You must to create discount table with such fields for example:

  1. discount_id - ID of the discount
  2. percent - % of discount (if 0 - then it's discount of other type)
  3. amount - amount of discount in $ (if 0 - then it's discount of other type)
  4. items - number of free items when buy something (one get - one free in your example)
  5. bought_items - condition, in what case you give a discount (if 0 - no conditions)

Or you can give other names to the fields. According to values you may know what type of discount and what condition to give it.

Dmytro Zarezenko
  • 10,526
  • 11
  • 62
  • 104
1

This is where I would normally start with a multi-tiered approach, since it not only will be for calculating but may be used to show on the actual website. I would start with two tables.

Promotion Table
| id | discount_type | qty_products | discount_amount | free_products |
-----------------------------------------------------------------------
|  1 | BOGO          |       2      |        0        |       1       |


Sales Promo
| id | promo_id | product_id | activation_date | expiration_date |
------------------------------------------------------------------
|  1 |     1    |     222    |   '2012-04-23'  |   '2012-05-12'  |

Then the code would look to see if it was in date range and apply the discount or B1G1/B1G2. Tables are not exact but a start.

James Williams
  • 4,221
  • 1
  • 19
  • 35