Database design for flexible Pricing & Discounts of Student Courses

Using Django
1

I have a Django project for managing my dance school. We have Courses of specific CourseTypes, that are held at a specific Locations. Students can enroll in Courses. Currently my Course model has a simple price attribute - I now want to move to a more flexible pricing design where I can have different prices and discounts based on different variables.

I have come up with the below design, based on advice from “The Data Model Resource Book”.

Database schema
Database schema715×755 63.7 KB

A Course is priced by PriceComponents. Prices are made up of “base” components and “discount” components. For example, a Course might have a “base” price of $400, and multiple “discount” components (either a fixed amount discount or percent discount). I am not planning to normalize ComponentType, instead keep it as an attribute of the PriceComponent model as a ChoiceField.

A PriceComponent can be restricted to different variables. For example, a PriceComponent can be for a specific Location, for Students who are members of a specific PriceGroup, for specific CourseTypes and so on. That lets me price Courses and apply discounts differently, depending on combinations of these variables.

A PriceComponent will be of a PriceType. For example, a Course has a trial class fee, a drop-in fee, a term fee and so on. By having a PriceType attribute, I can derive prices for these different types of pricing.

As this is a major refactoring for my project, I wanted to see if anyone has any input on my design, are there any obvious flaws or design patterns I am breaking?

These are the main queries I foresee using:

  • List of Courses and their derived prices: Ignore any PriceComponents with StudentPriceGroup, as these are relevant only for specific Students. I will need to have some business rules in cases where the are for example multiple “base” PriceComponents.
  • Derive the price of a Course when a Student enrolls. This will be set to “price” in the StudentCourseEnroll record. Here we need to take into account StudentPriceGroup. There might be a different “base” price for a specific PriceGroup, so we need a business rule stating that the lowest base price is used (as an example).
  • I don’t forsee an issue with filtering the PriceComponents, but performance might be an issue as I am not sure I can derive prices fully in a query (use correct base price, subtract discounts [either discount with “price” or “percent”, which are different calculation methods]).

Any input is welcomed!