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 trying to 'conceive' the business logic of this website that have many activities, that the users can build their "combo" and get discounts depending on their choices and how long they are willing to pay for their plan (1, 3, 6 and 12 month plans).

I'm having a hard time trying to come up with a solution, while keeping the database normalized and with proper relations, without having to resort to JSON encoded data in the database fields. The system must stay generic enough to fit many businesses types that relies on plans / activities. I need to know how to do the structure of my tables.

Scenario: For example, in case of a gym, will have bodybuilding, yoga, boxing, body pump. If the person choose bodybuilding and boxing, they will have a discount, if they add yoga, boxing and bodybuilding will keep the discount but will add the yoga without any discount to the price, but if the person decides to pay 12 months upfront, they will get a bigger discount.

Bodybuilding + Body Pump

  • Bodybuilding..$70 | 28% discount
  • Body Pump.....$70 |
  • Total.........$100

  • 1 month - $100 / month

  • 3 months - $91 / month
  • 6 months - $75 / month

Bodybuilding + Body Pump + Yoga

  • Bodybuilding..$70.00 | 28% discount
  • Body pump.....$70.00 |
  • Yoga..........$90.00 | No discount
  • Total.........$190

  • 1 month - $190 / month

  • 3 months - $171 / month
  • 6 months - $158 / month

I'll be using PHP and MYSQL, but that doesn't matter much, only the RDBMS part.


Edit for clarification: What I really looking for is a DB Schema for Packaging products (or in this case services) together under a single price\Offer. Each product must also exist in the systems as a stand alone product.

I Still need the ability to report on sales (& Profit) by product even if that product was sold as part of a package.

I would Need the ability to report on Package performance.

share|improve this question
1  
The root of your question is a business logic question that pertains to discounts; not programming. Therefore, I'm voting to close as "off-topic". –  Jim G. Dec 30 '12 at 3:45
    
I'm trying to understand how to create my database structure having that in mind... –  pocesar Dec 30 '12 at 3:46
    
Can you list the prices in a tabular format? If you can, then we can help you with the algorithm. –  Jim G. Dec 30 '12 at 4:04
    
I'll try to put that on my question –  pocesar Dec 30 '12 at 4:32
3  
Hi pocesar, we are discussing the closure of your question in our Meta site, feel free to join the discussion. With a few more details I can see it getting re-opened soon (closing isn't permanent). –  Yannis Rizos Dec 30 '12 at 13:18

1 Answer 1

up vote 2 down vote accepted

At a Minimum You need:

  1. A Promo Header Table - Holds exactly what you get with the Promotion (Free item, free shipping, $$ off)
  2. A Promo-Requirements Tables (I to meany to Promo's, Hold all the requirement if the promo) Each record is one Requirement, Requirements can be "must be and item of Brand X" or must be SKU 1234, or order total must be >50$ (When i did this is had a row type flag that told me what kid if Requirement it was.)
  3. A module that is good a checking if an order meets a promotion's Requirements.

Unfortunately, I can't give you an exact ERD, because the promo conditions vary so much from business to business, so it's up to you make it as complex or simple as you need. For example, do you need Promos by SKU?, By Category, by subcategory?, by brand?, do you need to exclude certain SKUs?, brands? Category's? shipping locations?

Lastly make sure you make this thing easy to maintain and adjust, because just when you think you covered all your bases the business team will come up with some new crazier promo the no customer on earth will understand how to use.


EDIT: *Now that I better understand your question here what you need:*

Package Header Table and a Line Items Table, The Package Name and Description is stored on the header, The Lines hold the Items (including the Item prices when sold as part of that package).

You need a way to adding a Package to order. When that is done add then each item in the package as a regular line item but have a extra fields to specifies that the item is part of a Package and the PackageID.

It is then up to you to decide how you want to code the order Print out, Either just print a package total, so the customer doesn't know the line item prices, or print it normally but add with the package description.

share|improve this answer
    
the discounts are a "side" effect of the system. Having 15+ activities to choose from and build his monthly plan. If the user wants to do all 15 activities per month, he would pay a lot, but he would. If he wants to do only one activity, so be it. –  pocesar Dec 30 '12 at 4:31
    
the second part of your answer pinpointed it. so basicaly, I need to keep the packages 'invisible' to the user, so if there's any match, then the discount happens. thanks –  pocesar Jan 7 '13 at 6:46

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.