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 |
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
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.