1

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?
Improve this question
1
  • See below for my answer on how I decided to design this. Commented Apr 4, 2013 at 13:10

2 Answers 2

Reset to default
3

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.

Improve this answer
8
  • 2
    And in addition, create views such as REGISTRATION_EXPIRED_WITHOUT_PAYMENT and REGISTRATION_UNPAID_UNEXPIRED. Commented Apr 3, 2013 at 12:58
  • How would a PAYMENT table be different from the sale_header table I already have? Also I forgot to mention that registrations are currently logged when deleted, so when deleted from the class_registration table they're not truly gone, but just letting expired ones stay in the registration table does seem more straightforward. Thanks for the great answer. Commented Apr 3, 2013 at 16:53
  • @MattB. - I can't say for sure how a PAYMENT table might be different from your sale_header table. They might be the same thing, depending on how your table is actually used. My advice is to have a table that reflects as closely as possible the real-world activity of money coming in and out. It's good that you are logging data before deleting it, but that is less desirable than keeping it around in the first place and using queries or, as Erwin Smout suggested, views to filter completed registrations and incomplete registrations. This simplifies data maintenance and troubleshooting. Commented Apr 3, 2013 at 17:06
  • BTW thanks for your answer to the bonus question, that was quite helpful :) Commented Apr 3, 2013 at 17:47
  • @JoelBrown See my answer below...I think it satisfies your two main guidelines, no? Commented Apr 3, 2013 at 17:57
0

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;
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.