Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I'm working on a new point of sale for a company that products for different prices depending on the product mix.

All products have a base price.

To explain my problem, I will use the following info:

Product         Category        Price
A               1               45
B               1               70
Q               2               20
R               2               27
S               2               15
X               3               17
Y               3               22
Z               3               16

The company has Packages, for example Package "Combo": for product A or B, if you choose 1 of Q or R and 1 of X, Y or Z you get a $20 discount.

Case A: Sometimes customers add on to a base product when placing an order, for example: They wan't one of Product A, and they add Product Q and Product P to that to create a package that has a discounted price. Then they might add that they want 1 of product B with 1 R and 1 Z.

Case B: Sometimes customers will add 1 A and 2 B, 2 Q, 1 S, 2 X and 1 Z. According to the rules set forth by "Combo" Package, only 2 combos would apply because S is not a combo item.

Other promotions are dependent on quantity, so if you buy 2 of B you get 20% off and/or dependent upon time, it is only valid after 5 PM or before 10% off if before 10am. Another promotion might depend on when your last purchase occured or if you have purchase more than $X in Y timeframe.

My Problems:

1) How do I structure the tables so I my create the different packages or promotions in a way that is very flexible to add different types of promotions with different requirements?

2) When they order like Case B (or a mix of Case A and Case B) how do I structure my query so that I can test to see what product mix(es) are in the order, and update the prices/descriptions accordingly? Ultimately, the best result for this query would return which packages and promotions have requirements fulfilled in order of which gives the most benefit to the customer (i.e. maybe what they ordered fulfills requirements for promotion 1 and 3, but promotion 3 is less expensive. This must work with multiple promotions).

Thanks in advance for the help!

UPDATE #1

To better describe the problems at hand and to update the work done thus far to solve them I'm incluiding an ERD of the Product Model limited to the entities and attributes that affect the issue (i.e. inventory is not at play here, so no inventory entities are present).

enter image description here

I'm also including sample data from the entities and attributes that affect this question (to simplify reading the data, I'm putting in name/descriptions in place of Foreign Keys):

Here is a link to a flow chart giving an example of a combo, a fast and visual way to understand the table structure.

PRODUCT
---------
ID  Name
================================
1   Hamburger
2   Cheeseburger
3   Bacon Hamburger
4   Bacon Cheeseburger
5   Orange Juice
6   Apple Juice
7   Coffee
8   Coke
9   French Fries
10  Onion Rings
11  Soup du Jour
12  Hamburger Combo
13  CheeseBurger Combo
14  Bacon Hamburger Combo
15  Bacon Cheeseburger Combo
16  Combo Side
17  Combo Beverage
18  Small Orange Juice
19  Large Orange Juice
20  Small Apple Juice
21  Large Apple Juice
22  Add Extra Patty
23  Add Avocado

PRODUCT COMPONENT
------------------
productFrom                 productTo       
===================================================
Hamburger Combo             Hamburger
Hamburger Combo             Combo Side
Hamburger Combo             Combo Beverage
CheeseBurger Combo          Cheeseburger
CheeseBurger Combo          Combo Side
CheeseBurger Combo          Combo Beverage
Bacon Hamburger Combo       Bacon Hamburger
Bacon Hamburger Combo       Combo Side
Bacon Hamburger Combo       Combo Beverage
Bacon Cheeseburger Combo    Bacon Cheeseburger
Bacon Cheeseburger Combo    Combo Side
Bacon Cheeseburger Combo    Combo Beverage

PRODUCT FEATURE
----------------
ID  Description
=======================
1   Combo Side Option
2   Combo Beverage
3   Juice
4   Orange Juice Size
5   Apple Juice Size
6   Extras

PRODUCT FEATURE APPLICABILITY
------------------------------
product                     productFeature  ProductFeatureApplicabilityType
============================================================================
Hamburger Combo             Combo Side      Required
Hamburger Combo             Juice Flavor    Required
Cheeseburger Combo          Combo Side      Required
Cheeseburger Combo          Juice Flavor    Required
Bacon Hamburger Combo       Combo Side      Required
Bacon Hamburger Combo       Juice Flavor    Required
Bacon Cheeseburger Combo    Combo Side      Required
Bacon Cheeseburger Combo    Juice Flavor    Required


PRODUCT FEATURE APPLICABILITY
------------------------------
productFeature  product             ProductFeatureApplicabilityType
============================================================================
Combo Side      Hamburger Combo             Required
Combo Beverage  Hamburger Combo             Required
Extras          Hamburger Combo             Optional
Combo Side      Cheeseburger Combo          Required
Combo Beverage  Cheeseburger Combo          Required
Extras          Cheeseburger Combo          Optional
Combo Side      Bacon Hamburger Combo       Required
Combo Beverage  Bacon Hamburger Combo       Required
Extras          Bacon Hamburger Combo       Optional
Combo Side      Bacon Cheeseburger Combo    Required
Combo Beverage  Bacon Cheeseburger Combo    Required
Extras          Bacon Cheeseburger Combo    Optional




OPTIONAL FEATURE
------------------
productFeatureFrom  Product             ProductFeatureTo        
=============================================================
Combo Side Option   French Fries
Combo Side Option   Onion Rings
Combo Side Option   Soup du Jour
Combo Beverage                          Juice
Combo Beverage      Coffee
Juice                                   Orange Juice Size
Juice                                   Apple Juice Size
Orange Juice Size   Orange Apple Juice
Orange Juice Size   Orange Apple Juice
Apple Juice Size    Small Apple Juice
Apple Juice Size    Large Apple Juice
Extras              Add Extra Patty
Extras              Add Avocado

So, with the research and support provided by the community up until know, I have been able to solve Problem #1. In fact, I have done so with more flexibility than I thought I could do in the first deployment of the system.

All though there have been advances with Problem 2, it is not solved to satisfaction. There have been some ideas on how to do this, Neil McGuilgan asked a great question leading towards a possible solution using Relational Division (dba.stackexchange.com/questions/45829/what-is-the-name-of-this-type-of-query-and-what-is-an-efficient-example) and this book (www.amazon.com/books/dp/0471380237) helped a lot. However this solution at present, and as I understand it, only works with "one" record (combo) at a time. If a customer Walks up and says he wants 2 cheeseburgers, 1 hamburger, 1 small Apple Juice, 1 Coke, 1 French Fries and 2 onion Rings, I need a way to detect that there is only one combo in the mix and add the other products at base price. If there are multiple combo combinations, I'm looking (ideally for an sql query) that can prioritize combo combinations by largest savings.

One idea I've come up with to solve problem two is to add and attribute to PRODUCT COMPONENT flaggin the main product for the combo (i.e. hamburger). Then when running the pricing process, query what products in the order are main products in a "package", relating the query to the discount given by the PRICE COMPONENT table and ordering by that value (descending), and in that order of packages check to see if you can create a "package" with the remaining non-main products with a query and loop the process until there are no more main products or there are no more non-main products in the remainder.

share|improve this question
    
What have you done so far ? Show us some ER diagram. –  user61852 Jul 8 '13 at 15:52
    
@user61852 I do most of my ERDs by hand. Can you recommend a tool to do them on the computer so I can share it? (hopefully a free one :) –  cml Jul 8 '13 at 18:20
    
@user61852 I found gliffy.com which is proving to be rather useful. I'm working on putting together what I've worked on to update my question. –  cml Jul 8 '13 at 22:09
    
@user61852 I have added update #1 to show ERD, flow chart, advances and sample table data. –  cml Jul 9 '13 at 1:58
add comment

1 Answer

This could get complicated...

1) How do I structure the tables so I my create the different packages or promotions in a way that is very flexible to add different types of promotions with different requirements?

You could start with a packaged_with table to determine which products can be grouped and packaged together:

package
-------
  id (PK)
  name

package_group
-------------
  package_id (FK to package.id)
  name

packaged_with
-------------
  package_group_id (FK to package_group.id)
  product_id (FK to product.id)
  can_be_packaged_with (FK to product.id)

package_group refers to a package. packaged_with refers to products and package_groups, so that a row in packaged_with shows which products a product can be packaged with, and a package can be made up of multiple groups.

The data would look like this:

package
-------
ID  |  name
------------
1   |  Combo

package_group
------------
ID | name
---------
1  | QR group
2  | XYZ group

packaged_with
-------------
package_group_id | product_id | can_be_packaged_with
----------------------------------------------
1                | A          | Q 
1                | A          | R
2                | A          | X
2                | A          | Y
2                | A          | Z 
1                | B          | Q 
1                | B          | R
2                | B          | X
2                | B          | Y
2                | B          | Z 

This will help you with the product packages themselves. I have some ideas for the rest of your question, but I don't have time to finish this answer right now...


Promotions

You list many kinds of promotions. You might want to look into some sort of rules-engine, but I'll try to keep things simpler than that, but still... This will get complicated..

Let's start with simple promotions where a price is discounted a certain percentage:

percent_discount
----------------
  id (PK)
  name
  percent_amount

product_promotions
------------------
  id (PK)
  product_id (FK to product.id)
  promotion_id (FK to percent_discount.id)
  start_date
  end_date

Here we have a table that stores what percentage you get off for product. Another table actually links products to the discount rate, and also includes a start and end date so you know if the discount is applicable on any given date.

Ideas for the other promotions coming later...

share|improve this answer
    
I look forward to reading the rest of you answer when you have time write it. Thanks for your help. –  cml Jul 4 '13 at 23:46
    
I'm definitely going to look into "rules-engines"! If you have any good resource links, send them my way! My biggest problem right know is determining what promotions apply to an unordered, ungrouped list of order items (Case B). –  cml Jul 8 '13 at 16:54
    
@cml: I know there are commercial products that probably already have the capabilities you want. One I know is called Hybris, I've heard has a very sophisticated promotions system. –  FrustratedWithFormsDesigner Jul 8 '13 at 17:12
    
I added update #1 –  cml Jul 9 '13 at 1:59
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.