1

I'm working on a project where we track a person's history with a company. The table has StartDate and EndDate columns.
We won't always know Start Date of a person's history record.
For a person's current employment record, End Date will obviously be unknown.

My question: Is it better in regards to storage, performance, querying, general usability etc to...
a) Make both of these columns nullable?
or
b) Make both columns NOT NULL and have StartDate be a min value (1/1/1753) when unknown and the EndDate a max value (12/31/9999) when unknown?

I understand how to tell if a record is past, current or future with either approach.
I've read the articles on Null vs Empty String, but this is a bit different with it being two associated fields that are often queried together.

Airn5475
  • 2,452
  • 29
  • 51
  • The major benefit of using "negative and positive infinity" is that you can omit explicit `NULL` checks and have queries potentially make better use of indexes. The drawbacks are having to check and filter these special values when actually presenting or entering them in other contexts, or calculating differences, to prevent nonsensical results. Either approach is viable. You can probably find people that swear by one of these as the universally correct thing to do, but I'd say it depends and consider it per database and how it's likely to be used. – Jeroen Mostert Aug 17 '18 at 12:53

0 Answers0