Database Design – How to Handle Conditional Fields

I would say that if your users are going to need to query the Archive data, then using the bit flag or soft delete is easier. If the users don't need the data any longer, then I would go with the archive tables.

Based on your description above, I would suggest going with the Soft Delete version. I can tell you from experience in one of our systems, we went with an archive schema to move older data to and it lead to nothing but issues because the users needed access to the data. So it lead to using UNION ALL on every query we had to run.

As a result of the issues, we stopped that route and moved to the soft delete, which is much easier.

We added a bit flag to all of the tables it was needed and then we just included this in the WHERE clause when querying the data.

A suggestion would be to make sure that this field has a default value when you INSERT data. If you are using IsArchived then the default value on the column would be false since you do not want it archived immediately.

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.