Handling expiration in a course registration or ticketing system

database-design

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 sale_item and class_registration.

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 sale_id is 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_time in the database isn't an option; it needs to be calculated based on time_added.
  • (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?

Best Answer

There are two principals that should govern your thinking about you data modeling options for this application:

1. Never Throw Away Important Information

Discarding data that you might need is a terrible idea. It's up to you to decide whether a failed registration is something you might need. I would think it probably is. What would you do if someone showed up for a class saying "hey I registered!"? Wouldn't you want to be able to say "oh, I see your cheque bounced so your registration failed."?

2. Model Close to Reality

If you try to abstract your entity types too much, you will find your model gets brittle and hard to adapt to changing business rules. Your system is tracking a couple of kinds of events. In particular, the act of someone registering for a class and the act of someone paying for a registration. You should have a table for each of these events, along with tables for the people and classes.

Keeping separate tables for registration (which you already have) and for payment (which you don't really have in any of your options) allows you to handle situations like payment by multiple methods, e.g. some cash, some credit card, some promotional coupon, etc. It also lets you deal with the situation of failed payments, credit notes processed, and other real-world exceptions that are probably important to know about.

Instead of keeping columns for registration status, which is ultimately a calculated value, you should be keeping columns (and records) for the various components of the registration status calculation. If your system turns out to have peculiar performance demands during real runtime testing, then consider using denormalization of the calculated registration status value, but beware of all of the potential issues that will raise for data integrity and plan to deal with those issues.

Bonus:

If you keep a REGISTRATION table and a PAYMENT table, as I suggest, and treat registration status as a calculated value, then you can add a column to REGISTRATION that allows you to bend the payment time limit rule. You can do this different ways. For example you could have a flag or code that means "consider this registration valid, even without payment". Alternatively, you could have a numeric or date column that gives a payment extension allowance in number of days or by a different, arbitrary deadline.

Related Question