4

Is it a good/bad practice to add a "boolean meaning" to other fields?

For instance, let's say I'm modeling events, that might be recurring or not. I can have:

  • A boolean column that holds if the event is recurring
  • An integer column that holds the repetition interval

Or

  • Just the integer column for the repetition interval, and use zero or null to indicate that the event is not recurring.

The advantage of latter is that we need one field less in the table. The disadvantage that comes to my mind is that at application level is that is more clear to check if(event.isRecurring()) than if(event.getRepetitionInterval() != null).

Is there a best practice in cases like this?

  • 2
    You could still do event.isRecurring() with one column: public boolean isRecurring() { return this.repetitionInterval != null; } – marstato Apr 18 '16 at 12:38
  • There's no reason you can't have: if(event.isRecurring()) in your application, but retrieve an integer instead of of a bit from some database and interpret a Null or zero value as false. – JeffO Apr 18 '16 at 19:03
6

I would tend to the the "Just the integer column for the repetition interval, and use zero or null to indicate that the event is not recurring" option and use NULL.

Semantics of NULL aside, having 2 columns breaks 3rd Normal Form because "repetition interval" depends on the boolean column.

And then you an almost guarantee that someone will use the repetition interval value without checking the boolean.

I'd use NULL instead of a sentinel value: NULL as in "no data" rather then "undefined"

5

Both techniques have their pros and cons.

Using a flag and a value means that you use two fields where one would do. Usually this is only a concern if you're extremely short on space, e.g. in embedded systems. It also means that you're storing a value where it's not obvious that it might not apply. Sooner or later someone will look just at the interval and assume that something happens every 30 minutes, evemn though this isn't true.

Using a sentinel value with a special meaning means more complicated logic, as you said.

Both are routinely done. There is no such thing as a "best practice" about which people agree as much they do about other things (e.g. "don't abuse VARCHAR fields as lists"). Therefore, what to do depends on the particularities of your project. Just how short of space are you? How diligent are the aplication programmers who will reuse this interfacce? You know better than we do.

  • 3
    +1 As a DBA, I'd assume application programmers are dumber than boxes of rocks... – gbn Apr 18 '16 at 12:09
  • @gbn What? What does "application programmer" even mean? All programs are applications. – Robert Harvey Apr 18 '16 at 15:08
  • 1
    @gbn Pfft - DBAs are little more than power users... ;-) – Robbie Dee Apr 18 '16 at 15:27
  • 3
    @gbn an attitude like that just widens the divide. I bet if you actually just had some earnest conversations with those "boxes of rocks", you'd find that they're not nearly as dumb as you think. We should be working together to build the best systems we can, not against each other. – RubberDuck Apr 18 '16 at 16:09
1

I concur with @Kilian's answer, and thought I could add something to the discussion:

What your describing is how to capture what would naturally be a subclass situation in OOP and map that to the relational model.

In OOP, you could have an event (non-recurring), and a subclass recurring event. Or an abstract event with two subclasses of one-time event and a recurring event.

As another example, take a notion of employee records. In OOP we'd have perhaps an abstract employee with two subclasses, one for the CEO who, by definition, doesn't have an identified employee manager, and all other employees who must have one other employee as manager. (Or we could do away with the abstract class).

In relational systems, this kind of simple subclassing causes a lot of headaches, and probably the best mapping is to use a single employee table with a manager field (fk to self), and allow NULL for the CEO's record's manager field.

Codd describes two uses of NULL: "missing and applicable", and "missing and not applicable". The former means it would be valid if we had the data (we just don't have it), while the latter means that this column does not apply to a record of this type.

Sadly, the two usages of NULL are not distinguished in SQL databases.
(Further, there is no formal indication of what the real type of a row is, you just have to infer it from the nulls in the optional columns. If there was, we could perhaps say that the column must be NULL for row(s) of type CEO, and must not be NULL for regular employee rows.)

As @Kilian says, there is no well-agreed upon best practice here.

However, if you were to pose the your question from an OOP perspective, I think you'd find answers recommending using subclassing.

So the issues in my mind is one of mapping to relational. In relational mappings of such situations, NULL is commonly used for optional fields that don't apply because of (sub) typing. In this case, NULL would mean Codd's Missing and Not Applicable because for a one time event the recurrence field is not applicable.

1

If I may make so bold as to tentatively suggest a third solution if you really wanted to limit the number of columns used:

A positive value could be used to indicate that an event is valid. A negative value could mean that an event is not currently valid. The benefit of a negative value is that you could see what the repetition period had initially been set to.

Toggling the flag also becomes as simple as multiplying by -1;

  • Never thought of using the negative number to take the place of the IsActive field as well. – JeffO Apr 18 '16 at 19:10
1

Stick with one. Although is seems easy enough to just return a bit from the db when you just want to know if the task is repeated, you'll need to manage rules in your application to keep these values synchronized. You've really wasted space and made the database more complicated than necessary. This rears its ugly head in the enterprise when integration, reporting, data warehousing puts more demands on the database outside the world of a single application.

0

I think option 2 would suffice since it's clear from your variable name that it's the repititon count and if it's more than 0 then it should recur. I'd use 0 instead of NULL.

  • 1
    Why 0 instead of NULL? – garci560 Apr 19 '16 at 13:29

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.