Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?
share|improve this question
    
See below for my answer on how I decided to design this. –  Matt Browne Apr 4 '13 at 13:10
add comment

migrated from stackoverflow.com Apr 2 '13 at 23:15

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 3 down vote accepted

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.

share|improve this answer
2  
And in addition, create views such as REGISTRATION_EXPIRED_WITHOUT_PAYMENT and REGISTRATION_UNPAID_UNEXPIRED. –  Erwin Smout Apr 3 '13 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. –  Matt Browne Apr 3 '13 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. –  Joel Brown Apr 3 '13 at 17:06
    
BTW thanks for your answer to the bonus question, that was quite helpful :) –  Matt Browne Apr 3 '13 at 17:47
    
@JoelBrown See my answer below...I think it satisfies your two main guidelines, no? –  Matt Browne Apr 3 '13 at 17:57
show 4 more comments

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;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.