Choice between two antipatterns: Overdesign versus Over-optimization

Question

Sometimes I need to keep state, like an IsVerified flag.

There are two options:

boolean (or bit)

Advantage: smallest and fastest

...but in theory "over-optimization" and thus a bad argument.

nullable datetime

IsVerifiedOn -> NULL is not verified, any value means verified.

Advantage: you might have a situation in the future where you suddenly come across a case where it is useful that you actually know when this account had been verified.

...but in theory "over design" (You Aren't Gonna Need It) and you should only add this when you actually need it.

So between these two evils? What is the best one to start with?

Answer 1

I believe you might be "over thinking" it. I've never heard the phrase "over-optimization is an anti-pattern" and I would generally disagree with that.

Rather than worrying too much about anti-patterns, architect to your use cases and be reasonable. If you know you'll never reasonably need the IsVerifiedOn datetime field in the near future then it's probably just fine to use the IsVerified bit field. If there's reasonable potential use in having the IsVerifiedOn field, then utilize such a field instead. There's no right or wrong answer here, and it depends on again what's reasonable to your use cases.

Here's another anti-pattern, one could even consider, that blows the two you mentioned out of the water: it's called "one field multiple purposes". It means what it says, i.e. it's technically considerably an anti-pattern to have one field have multiple purposes, such as your IsVerifiedOn column description suggests. The first purpose being to identify if a row is verified and the second purpose to identify when it was verified.

One reason this is considered an anti-pattern is because one day that field could have a value that fulfills the one purpose well while breaking the logic or not making sense for the other purpose. For example in your case, you may have another table derived off your IsVerifiedOn field. That second table only holds records for the last 10 verified rows in your first table. Now someone decides to unverify your first table by nulling out the IsVerifiedOn field for the last few records but they forget to clear out the last few records from the second table. Now you lose data integrity.

Best practice against this anti-pattern actually advises to implement both an IsVerified field and VerifiedOn field to separately maintain those two purposes. But again, this is also dependent on your realistic use cases within reason, and also doesn't always have to be adhered to. If you always try to design your schema reasonably, you don't have to think so much about anti-patterns.

(P.s. I actually gave a pretty weak example on the "one field multiple purposes" anti-pattern, but there are a lot of realistic data integrity breaking things that can happen as a result of it. So despite my poor example, hopefully it still communicates the idea.)

Answer 2

You may need NULL in the IsVerifiedOn date for another purpose later. If a situation ever arises where you know that something has been verified, but you don't know the date on which it was verified, you will be in trouble, because you can't say that. In general, NULL in a field always means "no data here". Assigning it another meaning can always create potential problems down the road.