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.