Depending on the complexity of the database design you would probably need to use transacations to maintain data consistency between tables as you change data within the system.
Any examples would be very basic as every database schema would be different. This is where having a database or data architect comes in handy for a project.
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.
Best Answer
There is one entity -
financial transaction
, that can be in a various states. Completed, scheduled, postponed, rolled back etc. State is the property of the entity, only part of the whole, like the field is a part of the row.When the state is just a field of the table it is possible to create DB structure and code that are invariant regardless of how many states are possible. In the opposite case each new state produce the new table and lot of code should be rewritten to reflect the changes.