Calculate different prices for different user types

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: $500
Unit 2: $500
Unit 3: $500
All Units: $1200

Website users with membership but with debt on the annual fee:
Unit 1: $450
Unit 2: $450
Unit 3: $450
All Units: $1100

Website users with membership (no debt):
Unit 1: $400
Unit 2: $400
Unit 3: $400
All Units: $900

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

The simplest option is probably to add an outstanding_debt column, and ensure all payments and subscriptions update this number in addition to wherever you’re currently keeping billing information (I couldn’t really understand your description of that part). Your database probably has a constraints feature you can use to ensure these numbers remain in sync. After that, dealing with the debt rule is just as trivial as the membership rule.

Definitely implement the rules in PHP. SQL is for data, not business logic or other behavior. There is a third option, implement rules as expressions stored in the DB and have PHP evaluate them, but that way madness lies. The rules should be pre-defined, not defined when configuring a course’s pricing in a domain-specific language.

Early on, you should decide whether to model the different price options as discounts (an amount is subtracted from the course’s base price) or differential pricing (price is associated with each rule; a course has no base price). You can also combine the two by supporting relative pricings (e.g. “75%”, “-$50”) in addition to absolute pricings (e.g. “$300”), in which case courses have base prices, but they’re not referenced outside of pricings. Which you pick shouldn’t have a major impact overall, but will have small affects and will inform the design.

As for implementation, you could use something like the strategy pattern, with pricings like strategies and courses like contexts. The primary Pricing method (e.g. __invoke()) would take a course & a user and return an amount or, if the user isn’t eligible for the pricing, NULL. Alternatively, have two primary methods: one to tell whether the user is eligible for the pricing, and another to return the amount (less strategy-like, but might make more sense for your overall design). In addition to the special pricings requested by the client, you’d have an Always pricing that represents no discount and is always applicable. A Course has a collection of pricings (which is one thing that makes this not follow the strategy pattern). Determining whether a user is in good standing (i.e. doesn’t owe anything on the annual fee) would be handled by a User method (or by a method of whatever class is responsible for managing a user’s transactions, e.g. Account: $user->account->inGoodStanding()).

To determine the final price, you’ll need to determine a strategy precedence. One option is to define in code, implicitly or explicitly, a total order for pricings (Always would be the minimum). Another would be to rely on a sequence type for Course->pricings, and apply the pricings in this sequence. To ensure every course has a final price, end the sequence with an Always. With Pricing::__invoke() returning NULL, you can think of this as a coalescing operation. The latter precedence is easier to implement and more flexible, but potentially allows for errors (courses without an applied pricing, or with unreachable pricings). You can prevent some (all?) precedence errors by having & using a course’s base price as a fallback instead of an Always sentinal.

Note that some pricings could be based on others (e.g. is-a-member could be implemented as has-been-a-member-for(0)). Whether & when to do this depends on other design details. Be careful to follow proper OO principles if you do this. For example, use inheritance only if overridden properties are effectively immutable so as not to violate LSP. If not, you could end up with an is-a-member that has a non-zero period.

When it comes to the DB design, you can leave it to an ORM. Since data outlasts applications, you want to use an ORM that properly accounts for object-relational impedance mismatch (or procedural-relational impedance mismatch, as strategies can be viewed as procedures encapsulated by objects), which is the biggest problem with this approach. Specifically, different strategies have different arities, and dealing with a variable number of parameters/properties can be cumbersome in the relational model.

Even though an ORM should handle the schema, it’s useful to consider designs so as to know what to expect from the ORM. One way of handling a variable number of properties is to have entity-attribute-value tables. However, this approach has several issues, such as it doesn’t handle types very well (the values must have the same type in the DB, but in all likelihood have different types as properties). A more appropriate approach is to model inheritance relationally by giving each class its own table, with a column that refers to the parent table. The parent table should include a column that identifies the particular descendent (it functions both to identify the runtime class and identifies which table holds additional data). In particular, there’d be a base table for Pricing and a separate table for each pricing descendent. (To cut down on tables, this might be a case where you’d implement is-a-member as has-been-a-member-for(0), so as not to introduce a separate table for is-a-member. Alternatively, you could not have tables for nullary pricings.) Course->pricings is a one-to-many relationship, so you’d have a table associating the two; if it’s a sequence, the table would also record the sequence position for each pricing. All together, the schema would look something like:

CREATE TABLE courses (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    price DECIMAL(19, 4),
    ...
);

CREATE TABLE pricings (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name ENUM('Always', 'NotAMember', 'Indebted', 'MemberFor')
);

CREATE TABLE courses_pricings (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    course INT UNSIGNED NOT NULL,
    pricing INT UNSIGNED NOT NULL,
    position INT UNSIGNED NOT NULL,
    FOREIGN KEY (`course_pricing`) REFERENCES `courses` (`id`),
    FOREIGN KEY (`pricing`) REFERENCES `pricings` (`id`)
);

CREATE TABLE memberFor (
    pricing INT UNSIGNED NOT NULL,
    `days` INT NOT NULL, -- use whatever unit allows for the finest-grain required by pricing; the MemberFor class can convert it as appropriate
    FOREIGN KEY (`pricing`) REFERENCES `pricings` (`id`)
);

The schema could be denormalized (if profiling warrants) by combining pricings and courses_pricings.

Filed under: softwareengineering - @ 18:02

Thẻ: e-commerce, roles

Calculate different prices for different user types

Website users with no membership:
Unit 1: $500
Unit 2: $500
Unit 3: $500
All Units: $1200

Website users with membership but with debt on the annual fee:
Unit 1: $450
Unit 2: $450
Unit 3: $450
All Units: $1100

Website users with membership (no debt):
Unit 1: $400
Unit 2: $400
Unit 3: $400
All Units: $900

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

I’ll be using PHP & MySQL, but the question is more about the logic than the code.

CREATE TABLE courses (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    price DECIMAL(19, 4),
    ...
);

CREATE TABLE pricings (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name ENUM('Always', 'NotAMember', 'Indebted', 'MemberFor')
);

CREATE TABLE courses_pricings (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    course INT UNSIGNED NOT NULL,
    pricing INT UNSIGNED NOT NULL,
    position INT UNSIGNED NOT NULL,
    FOREIGN KEY (`course_pricing`) REFERENCES `courses` (`id`),
    FOREIGN KEY (`pricing`) REFERENCES `pricings` (`id`)
);

CREATE TABLE memberFor (
    pricing INT UNSIGNED NOT NULL,
    `days` INT NOT NULL, -- use whatever unit allows for the finest-grain required by pricing; the MemberFor class can convert it as appropriate
    FOREIGN KEY (`pricing`) REFERENCES `pricings` (`id`)
);

The schema could be denormalized (if profiling warrants) by combining pricings and courses_pricings.

Filed under: softwareengineering - @ 18:02

Thẻ: e-commerce, roles

Calculate different prices for different user types

Website users with no membership:
Unit 1: $500
Unit 2: $500
Unit 3: $500
All Units: $1200

Website users with membership but with debt on the annual fee:
Unit 1: $450
Unit 2: $450
Unit 3: $450
All Units: $1100

Website users with membership (no debt):
Unit 1: $400
Unit 2: $400
Unit 3: $400
All Units: $900

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

I’ll be using PHP & MySQL, but the question is more about the logic than the code.

CREATE TABLE courses (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    price DECIMAL(19, 4),
    ...
);

CREATE TABLE pricings (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name ENUM('Always', 'NotAMember', 'Indebted', 'MemberFor')
);

CREATE TABLE courses_pricings (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    course INT UNSIGNED NOT NULL,
    pricing INT UNSIGNED NOT NULL,
    position INT UNSIGNED NOT NULL,
    FOREIGN KEY (`course_pricing`) REFERENCES `courses` (`id`),
    FOREIGN KEY (`pricing`) REFERENCES `pricings` (`id`)
);

CREATE TABLE memberFor (
    pricing INT UNSIGNED NOT NULL,
    `days` INT NOT NULL, -- use whatever unit allows for the finest-grain required by pricing; the MemberFor class can convert it as appropriate
    FOREIGN KEY (`pricing`) REFERENCES `pricings` (`id`)
);

The schema could be denormalized (if profiling warrants) by combining pricings and courses_pricings.

Filed under: softwareengineering - @ 18:02

Thẻ: e-commerce, roles

Thiết kế website giá rẻ

Danh mục

Calculate different prices for different user types

Calculate different prices for different user types

Calculate different prices for different user types