Return to Answer

Ok, so it seems like my last option (from the original question) is closest to what would achieve @JoelBrown's guideline to "Model Close to Reality." To be clear, here's the full schema for that approach:

Update: I added an admin override flag to the registration table, following one of Joel's suggestions for this.

sale_header ( PK id FK account_id total payment_method payment_time ) sale_item ( PK sale_item_id FK sale_header_id price ) class_registration ( PK (camper_id, class_id) FK sale_item_id time_added expiration_overridden (bool) ) 

This keeps all pricing info in the sales_ tables. Registration expiration can be determined by comparing the time_added column of class_registration to the current date/time for registrations whose sale_item_id is NULL (meaning payment was never completed).

Here's how class availability could be determined given this schema:

-- a view for all valid registrations, including both paid and unpaid as long as it hasn't expired CREATE VIEW view_valid_registrations AS SELECT cr.*, cls.name, cmp.id, cmp.first_name, cmp.last_name, ... FROM class_registration cr JOIN camper cmp ON cr.camper_id=cmp.id JOIN class cls ON cr.class_id=cls.id WHERE (sale_item_id = NULL AND NOW() - time_added < @unpaid_expiration_time) OR expiration_overridden=1; -- query to get number of spots remaining in each class SELECT c.id, c.name, (SELECT COUNT(camper_id) FROM view_valid_registrations WHERE class_id=c.id) as num_students_registered, c.capacity - num_students_registered as num_spots_remaining FROM class c; 

Ok, so it seems like my last option (from the original question) is closest to what would achieve @JoelBrown's guideline to "Model Close to Reality." To be clear, here's the full schema for that approach:

Update: I added an admin override flag to the registration table, following one of Joel's suggestions for this.

sale_header ( PK id FK account_id total payment_method payment_time ) sale_item ( PK id FK sale_header_id price ) class_registration ( [Note: There is deliberately no unique key (student_id, class_id) because  there can in fact be duplicates due to expired registrations, so duplicate checking has to be done by the application] PK id FK camper_id  FK class_id FK sale_item_id time_added expiration_overridden (bool) ) 

This keeps all pricing info in the sales_ tables. Registration expiration can be determined by comparing the time_added column of class_registration to the current date/time for registrations whose sale_item_id is NULL (meaning payment was never completed).

Here's how class availability could be determined given this schema:

-- a view for all valid registrations, including both paid and unpaid as long as it hasn't expired CREATE VIEW view_valid_registrations AS SELECT cr.*, cls.name, cmp.id, cmp.first_name, cmp.last_name, ... FROM class_registration cr JOIN camper cmp ON cr.camper_id=cmp.id JOIN class cls ON cr.class_id=cls.id WHERE sale_item_id IS NOT NULL OR expiration_overridden=1 OR NOW() - time_added < @unpaid_expiration_time; -- query to get number of spots remaining in each class SELECT c.id, c.title, COUNT(cr.camper_id) as num_students_registered, c.capacity - COUNT(cr.camper_id) as num_spots_remaining FROM class c LEFT JOIN view_valid_registrations cr ON c.id = cr.class_id GROUP BY c.id; 

Ok, so it seems like my last option (from the original question) is closest to what would achieve @JoelBrown's guideline to "Model Close to Reality." To be clear, here's the full schema for that approach:

Update: I added an admin override flag to the registration table, following one of Joel's suggestions for this.

sale_header ( PK id FK account_id total payment_method payment_time ) sale_item ( PK sale_item_id FK sale_header_id price ) class_registration ( PK (camper_id, class_id) FK sale_item_id time_added expiration_overridden (bool) ) 

This keeps all pricing info in the sales_ tables. Registration expiration can be determined by comparing the time_added column of class_registration to the current date/time for registrations whose sale_item_id is NULL (meaning payment was never completed).

Here's how class availability could be determined given this schema:

-- a view for all valid registrations, including both paid and unpaid as long as it hasn't expired CREATE VIEW view_valid_registrations AS SELECT cr.*, cls.name, cmp.id, cmp.first_name, cmp.last_name, ... FROM class_registration cr JOIN camper cmp ON cr.camper_id=cmp.id JOIN class cls ON cr.class_id=cls.id WHERE (sale_item_id = NULL AND NOW() - time_added < @unpaid_expiration_time) OR expiration_overridden=0; -- query to get number of spots remaining in each class SELECT c.id, c.name, (SELECT COUNT(camper_id) FROM view_valid_registrations WHERE class_id=c.id) as num_students_registered, c.capacity - num_students_registered as num_spots_remaining FROM class c; 

Ok, so it seems like my last option (from the original question) is closest to what would achieve @JoelBrown's guideline to "Model Close to Reality." To be clear, here's the full schema for that approach:

Update: I added an admin override flag to the registration table, following one of Joel's suggestions for this.

sale_header ( PK id FK account_id total payment_method payment_time ) sale_item ( PK sale_item_id FK sale_header_id price ) class_registration ( PK (camper_id, class_id) FK sale_item_id time_added expiration_overridden (bool) ) 

This keeps all pricing info in the sales_ tables. Registration expiration can be determined by comparing the time_added column of class_registration to the current date/time for registrations whose sale_item_id is NULL (meaning payment was never completed).

Here's how class availability could be determined given this schema:

-- a view for all valid registrations, including both paid and unpaid as long as it hasn't expired CREATE VIEW view_valid_registrations AS SELECT cr.*, cls.name, cmp.id, cmp.first_name, cmp.last_name, ... FROM class_registration cr JOIN camper cmp ON cr.camper_id=cmp.id JOIN class cls ON cr.class_id=cls.id WHERE (sale_item_id = NULL AND NOW() - time_added < @unpaid_expiration_time) OR expiration_overridden=1; -- query to get number of spots remaining in each class SELECT c.id, c.name, (SELECT COUNT(camper_id) FROM view_valid_registrations WHERE class_id=c.id) as num_students_registered, c.capacity - num_students_registered as num_spots_remaining FROM class c; 

Ok, so it seems like my last option (from the original question) is closest to what would achieve @JoelBrown's guideline to "Model Close to Reality." To be clear, here's the full schema for that approach:

Update: I added an admin override flag to the registration table, following one of Joel's suggestions for this.

sale_header ( PK id FK account_id total payment_method payment_time ) sale_item ( PK sale_item_id FK sale_header_id price ) class_registration ( PK (camper_id, class_id) FK sale_item_id time_added expiration_overridden (bool) ) 

This keeps all pricing info in the sales_ tables. Registration expiration can be determined by comparing the time_added column of class_registration to the current date/time for registrations whose sale_item_id is NULL (meaning payment was never completed).

Here's how class availability could be determined given this schema:

-- a view for all valid registrations, including both paid and unpaid as long as it hasn't expired CREATE VIEW view_valid_registrations AS SELECT COUNT(camper_id) FROM class_registration WHERE (sale_item_id = NULL AND NOW() - time_added < @unpaid_expiration_time) OR expiration_overridden=0; -- query to get number of spots remaining in each class SELECT c.id, c.name, (SELECT COUNT(camper_id) FROM view_valid_registrations WHERE class_id=c.id) as num_students_registered, c.capacity - num_students_registered as num_spots_remaining FROM class c; 

Ok, so it seems like my last option (from the original question) is closest to what would achieve @JoelBrown's guideline to "Model Close to Reality." To be clear, here's the full schema for that approach:

Update: I added an admin override flag to the registration table, following one of Joel's suggestions for this.

sale_header ( PK id FK account_id total payment_method payment_time ) sale_item ( PK sale_item_id FK sale_header_id price ) class_registration ( PK (camper_id, class_id) FK sale_item_id time_added expiration_overridden (bool) ) 

This keeps all pricing info in the sales_ tables. Registration expiration can be determined by comparing the time_added column of class_registration to the current date/time for registrations whose sale_item_id is NULL (meaning payment was never completed).

Here's how class availability could be determined given this schema:

-- a view for all valid registrations, including both paid and unpaid as long as it hasn't expired CREATE VIEW view_valid_registrations AS SELECT cr.*, cls.name, cmp.id, cmp.first_name, cmp.last_name, ...  FROM class_registration cr JOIN camper cmp ON cr.camper_id=cmp.id JOIN class cls ON cr.class_id=cls.id  WHERE (sale_item_id = NULL AND NOW() - time_added < @unpaid_expiration_time) OR expiration_overridden=0; -- query to get number of spots remaining in each class SELECT c.id, c.name, (SELECT COUNT(camper_id) FROM view_valid_registrations WHERE class_id=c.id) as num_students_registered, c.capacity - num_students_registered as num_spots_remaining FROM class c;