I want to restructure the database for a web application I inherited for handling summer camp registrations, but my question is relevant to any event registration or ticketing system.
My question is about how to best handle the need for registrations to expire if unpaid for X amount of time.
Currently the database design is something like this (some columns omitted for brevity):
PK = primary key FK = foreign key sale_header ( PK id FK account_id payment_status total payment_method payment_time ) sale_item ( PK (sale_header_id, camper_id) price ) class_registration ( PK (camper_id, class_id) time_added registered (boolean) )
When payment is completed, the
registered flag is set to true. There is a dump script that runs regularly to clear out unpaid registrations by checking for registrations where `class_registration.registered = 0 AND sale_header.payment_status='not paid'" and for which the configured time allowed until expiration has elapsed.
There are currently some bugs related to this, and anyway it seems to me to be a somewhat strange design given that the
registered flag is only set to true when payment is completed, so the
payment_status column seems to be unnecessary; it seems like it would be much cleaner to have the
sale_header table only contain transactions that were actually completed. Also, even if I kept this approach, it seems it would be better if there were a FK linking
I've been researching potential database designs for this scenario and one thought I had was to add price info to the
class_registration table and get rid of the
sale_item table, since the system is only for selling class registrations, nothing else:
sale ( PK id FK account_id total payment_method payment_time ) class_registration ( PK (camper_id, class_id) FK sale_id price time_added )
In this design, the FK
sale_id would initially be null while the parent was still choosing classes for their camper(s), so finding expired, unpaid registrations would be done by querying for registrations where
NULL, and which had exceeded the expiration time.
Another option I thought of, inspired by this example (scroll down to "Data Modeling in the Sample Application"), would be to have a separate
class_availability table, which might be good for querying performance when determining available capacities:
class_registration ( FK camper_id FK class_id FK sale_id price time_added ) class_availability ( FK class_id FK camper_id FK registration_id )
Finally, I considered what it would look like if I kept the
sale_item table, although I'm not sure what would be the advantage of doing so:
class_registration ( PK (camper_id, class_id) FK sale_item_id time_added ) sale_item ( PK sale_item_id FK sale_header_id price )
So my specific concerns are:
- What are the pros and cons of these approaches?
- Should expired registrations stay in the database and just be excluded when querying, or actually be deleted?
- A requirement to keep in mind is that it should be possible to change the configured expiration time in a live system (in case of bugs, which has happened before with this system), so having a column like
expiration_timein the database isn't an option; it needs to be calculated based on
- (Bonus :) Feel free to skip this one and focus on the above questions.) In my new proposed designs (or some other design), how could I handle the possible need for admins to be able to extend the expiration time for a particular camper in case there was some issue with payment that needed to be worked out, and their registration should be held in the meantime?