I'm working on a website that, among other things, sells courses. Each course is segmented in units, to which users can subscribe but is not a requirement that they subscribe to all of them. Now the client asked me to be able to configure different prices to course's units based on the user type and membership status (they can pay an annual fee to become members and get discounts). Also, subscribing to all units of the same course gives the user a discount. An example for a course would be like this:Website users with no membership:Unit 1: $500Unit 2: $500Unit 3: $500All Units: $1200Website users with membership but with debt on the annual fee:Unit 1: $450Unit 2: $450Unit 3: $450All Units: $1100Website users with membership (no debt):Unit 1: $400Unit 2: $400Unit 3: $400All Units: $900Whether an user is member or not is easy because there is a column that defines that. But then there is another table to log the user's payments, which should be used to get the last payment and deduce if the user has a debt. Based on that combination, the correct prices should be presented to the user.The client needs to download then a list of users who subscribed to a specific course/unit, so I can't just have three Unit 1 rows on the database for each price because they should be treated as the same unit.Regarding the All Units price, I can initially keep it simple and add it as another unit to avoid wasting time programming that because it's more important to solve the logic of the different prices.My question is: How should I structure the database, and what is the best way (logic) to deduce which price is shown to the user?.Initially the request was different prices for members/not-members which is easy because I can filter by the is_member column of the users table, but then they add to the request the debt status for each user, making it more complex.I'll be using PHP & MySQL, but the question is more about the logic than the code.One idea that came to my mind was to get from the database the user info + debt status in one query, and all the course's units and prices in another query and then use PHP to check which values should be used, but how can I implement that logic if the rules can be different for each course (another course will have a discount for users which have been members for 2+ years). Should I manually program Classes for each course (all implementing the same Interface) that do the logic and define in the courses table which Class should be used? I think it's not the best way but I can't think of another option to change the PHP logic according to the selected course.
Calculate different prices for different user types
e commerce;roles