Program Pricing Requirement

Program Pricing Requirement & History Management

Purpose

The Program Pricing system defines how course and resource fees are managed, tracked, and versioned. The system must handle:

• Flat course fees

• Resource-based fees (labs, exams, materials)

• Price changes over time

• Full historical tracking

This ensures billing accuracy, auditability, and immutability of past charges.

---

Problem Statement

Pricing is not static. Over time:

• Course flat fees may increase or decrease

• Lab fees may change

• Resource fees may be added, removed, or updated

Overwriting pricing records leads to:

• Incorrect student billing

• Broken financial reports

• Loss of audit trail

Solution: Preserve pricing history via versioned records.

---

Design Principles

1. Prices are time-based facts

2. Historical prices are never overwritten

3. Every price change creates a new record

4. Historical prices must always be queryable

5. Pricing setup is separate from enrollment records

---

Pricing Scope

The system supports:

• Flat course fees

• Lab fees

• Resource/material fees

• Exam fees

• Any additional chargeable items

All fees follow the same versioning rules.

---

Initial ProgramPricing Table (Early Design)

The ProgramPricing table was initially designed to store all pricing types and maintain history.

CREATE TABLE ProgramPricing (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    ProgramID INT NOT NULL,                 -- Reference to the program the price belongs to
    PricingScope VARCHAR(20) NOT NULL,      -- COURSE or RESOURCE
    ScopeReferenceID INT NOT NULL,          -- ProgramCourse or ProgramResource
    PricingType VARCHAR(20) NOT NULL,       -- FLAT, LAB, EXAM, MATERIAL, etc.
    Price DECIMAL(10,2) NOT NULL,
    IsMandatory BOOLEAN DEFAULT TRUE,
    EffectiveFrom DATE NOT NULL,
    EffectiveTo DATE DEFAULT NULL,
    CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

This version worked conceptually but introduced ambiguity and tight coupling as the system evolved.

---

Overview (Final Design)

This document describes the finalized pricing system design based strictly on the current implementation.
It explains why certain fields were removed, why new fields were introduced, and how the model logically converged into its final form.

The goal of this design is clarity, auditability, and long-term scalability — not short-term convenience.

---

Core Design Goal

Pricing is treated as:

A time-based financial fact attached to an entity

Not a configuration flag.
Not a business rule.
Not an enrollment decision.

---

Why ProgramID Was Removed and parent_id Introduced

Original Assumption

Early designs assumed:

• All pricing belongs to a program

• Courses, labs, and resources are secondary

This assumption failed quickly.

Problems Encountered

• Course fees depend on program + course

• Lab fees depend on course, not program

• Resource fees may belong to labs or courses

• ProgramID had only one real use but introduced:

  • Ambiguous ownership

  • Hard-coded hierarchy

  • Increasing query complexity

Final Decision

Remove ProgramID.

Introduce parent_id in the ProgramCourses table:

• parent_id indicates the owning entity of the course pricing.

• For courses: parent is the program it belongs to.

• For labs/resources: parent is the course it belongs to.

This allows hierarchical ownership without hardcoding assumptions.
It makes joins deterministic and enables storing all entity types in a single table.

Introduce a generic ownership reference in pricing:

ref_id → ProgramCourses.id

Pricing now attaches to the actual owning entity.

Entity | Ownership -- | -- Program tuition | PROGRAM Course tuition | COURSE Lab fee | LAB Resource fee | RESOURCE

Hierarchy resolves ownership — not column assumptions.

---

Why is_mandatory Was Removed

Reality During Design

At the pricing layer:

• “Mandatory” had no stable definition

• It varied by:

  • intake

  • enrollment rules

  • scholarships

  • policy changes

Embedding it early would:

• Freeze business assumptions

• Force schema changes later

• Mix enrollment logic into pricing

Final Decision

Remove is_mandatory.

Pricing answers one question only:

What was the price at a given point in time?

Mandatory vs optional belongs to enrollment rules, not pricing.

---

Why ref_type Was Added (Critical)

Problem Without ref_type

With only ref_id:

• A pricing record exists

• But its meaning is unclear:

  • Program?

  • Course?

  • Lab?

  • Resource?

This leads to:

• Unsafe joins

• Application-side guessing

• Hidden coupling

Final Solution

Add explicit semantic context:

ref_type ENUM('PROGRAM','COURSE','LAB','RESOURCE')

Now each pricing record is:

• Self-describing

• Query-safe

• Deterministic

Without ref_type, the table is ambiguous.
With ref_type, the table explains itself.

---

Final Pricing Table (Implemented)

ProgramCoursePricings
---------------------
id (PK)
parent_id             -- refers to parent of the record. course stores program id, lab stores course id
price_type            -- TUITION_FEE, LAB_FEE, MATERIAL_FEE, EXAM_FEE, FLAT_FEE, OTHER_FEE
price
effective_date
effective_to          -- NULL = current active price
ref_id                -- FK → ProgramCourses.id to determine whether its lab or course or program
ref_type              -- PROGRAM / COURSE / LAB / RESOURCE
createdAt
updatedAt

---

Unique Constraint (Version Control)

(ref_id, price_type, effective_date)

This enforces:

• One price per entity per type per date

• Clean historical timelines

• Append-only pricing history

Prices are never overwritten — only superseded.

---

Final Outcome

This pricing system now:

• Treats pricing as immutable historical data

• Attaches prices to entities, not assumptions

• Preserves financial auditability

• Avoids premature business rules

• Scales without schema rewrites

No magic columns.
No hidden logic.
No future cleanup migrations.

---

Final Design Principle

Pricing is not a property.
It is a historical event attached to an entity.

This design is optimized to survive years of change, not just pass initial implementation.