7

A bit long, but the overall the question is:

  1. Should I have a status field in the table, or should I calculate the status in-memory from other information in the record?
  2. What are the pro/cons of each?

PS. Gray area alert. This is not really a database-design question.

Background

Taking the example of a "Subscription" (i.e. a subscription to a online service for example.).

We could have a database record like this:

status                    string  # not_started, in_trial, active etc.
subscription_starts_at    datetime
trial_starts_at           datetime
trial_ends_at             datetime
subscription_ends_at      datetime

A. Initial record

And say we have this record at the start of a subscription

status                 in_trial
subscription_starts_at 2016-MAR-01
trial_starts_at        2016-MAR-02
trial_ends_at          2016-APR-30
subscription_ends_at   2017-DEC-28

From this we observe:

  1. status tells us that we are in_trial
  2. Current date being today (2016-MAR-12), and today is between trial_starts_at and trial_ends_at, confirms that the status is telling us the truth.

B. Now presume this scenario:

Let us say time passes and it is now 2016-MAY-05. This date is outside the range of trial_starts_at and trial_ends_at. So, trial period should be over now.

However, due to an error in the system (a background job that failed to run, for example), the Status field is still set to in-trial.

From this we observe:

  1. status field and 'calculation' of trial period (current date > trial_ends_at) do not agree.
  2. The current date is later than trial_end.

Now we have a mismatch. Who do we listen to, status or (trial_starts_at and trial_ends_at range)?

To avoid this problem I considered avoiding the use of a status field at all.

Do you think that is wise to skip the status field?

11

Do you think that is wise to skip the status field?

Yes. For several reasons.

  1. The status field is a cached value of something that can be computed. Caching is an optimization, the premature application of which is an known evil. Caches go out of date, and it takes work to keep them accurate. If you can avoid this it is for the best. I would employ caching only if I knew for a fact that I had a performance bottleneck and that the caching solved the problem.

  2. Further, you may find that you need to do "as of" queries to make reports. The status field will not help at all for that, as it only describes the situation as of "now".

  3. Capturing date-sensitive information in the db is probably in general a bad idea: compare a database that stores your age, with one that stores your birthdate. The former is always going out of date, and relies on a common understanding of "now". If you can avoid that (e.g. by capturing a birth date instead) that is better.


Edit:

On the other hand, we should always seek to consider things from the business or domain perspective. In this case, what the authoritative condition is regarding the status of a subscription. Such as if all subscriptions truly expires on the specified date or if there is some other notion of allowing status to be "active" beyond the trail date. These to be considered from a business perspective instead of a programmer's perspective.

|improve this answer|||||
  • Nice reasoning, thank you. Do you know of places on the internet where such "best practices" (esp. in terms of state) are in one place? A book perhaps? – Zabba Mar 12 '16 at 17:44
  • 1
    Regarding state, several thoughts come to mine: Data Normalization, also on state and more broadly programming: Out of the Tar Pit, and lastly, management of state, within programming is a broad topic. See Wikipedia's rich: Programming Paradigm, which I mention because different models treat state differently (functional vs. object-oriented) – Erik Eidt Mar 12 '16 at 17:51
  • Closely related are the ideas of Don't Repeat Yourself and Single Source of Truth. – cbojar Mar 12 '16 at 18:55
  • 1
    @cbojar, very good. Normalization (and caching or denormalizing) relate to those principles. – Erik Eidt Mar 12 '16 at 19:23
  • Re: the edit section; one possibility is that a human might be required to change the activation status manually per procedure. Which would mean the status was authoratative about that but the dates are authoritative about the state of the contract or so. In which case you would probably specifically want some sort of notification that the two had fallen out of sync and an audit log of human changes. Just one way a business might look at it differently. – StarWeaver Mar 15 '16 at 10:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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