Ok so there has been some discussion at work regarding the best way to tackle this solution.

For a given Person...

Option A
PERSON
*PERSON_ID INT
PERMISSION_START_DATE SMALLDATETIME --START DATE PERMISSION CAN START
PERMISSION_END_DATE SMALLDATETIME --END DATE WHEN PERMISSION IS NO LONGER VALID
:
About 20 other columns unrelated to this question

Option B
PERSON
*PERSON_ID INT
PERMISSION_STATUS TINYINT -- 0: INACTIVE | 1: ACTIVE - ALWAYS | 2: ACTIVE - DATEBOUND (USE TWO DATE COLUMNS BELOW)
PERMISSION_START_DATE SMALLDATETIME
PERMISSION_END_DATE SMALLDATETIME
:
About 20 other columns unrelated to this question

Option C
{something different} --perhaps Option B but normalized

My peer wants to set the date values in Option A by setting start = 0 and end = 65535 (which I guess is min_date '1/1/1900' and max_date '6/6/2079')

I am opting for Option B since I don't want garbage dates cluttering our system since I think it reflects poorly when reports are generated or when users need to set explicit dates since we either show them these bogus dates or the application needs to interpret the bogus dates as an Always On status.

Thoughts?

your peer thinks 65535 is a valid SMALLDATETIME? it's a valid INTEGER, that's all

if a "thing" (you didn't mention what this table is about) can be inactive, always active, or datebound, then option A fails because it doesn't allow for those statuses

what do you do about a thing which has several valid datebound ranges?


by the way, a very common and practical way to record "bogus dates" is with NULL

Gasp! Sir, I shall report you to Fabian!

if you can find him

Not sure if it matters but the "thing" in question is a person record, this is a particular type of person - again should be mostly irrelevant.

I will make it less generic in the original post (assuming I can still edit it).

Well in MSFT SQL Server it casts it to the dates I mentioned earlier.

So everyone is on board that storing MIN/MAX dates in date columns is a bad implementation/design, correct?

you did not mention this earlier

no, i'm not

try reading my post like this:

[beginning sentence] - to the dates I mentioned earlier

So you often (or even sometimes) have date columns that show dates such as:

1/1/1900

and

6/6/2079?

Do you interpret these differently (translate the values to something more meaningful) on the UI or in reports?

Why have a status when you can easily find out whether it's active or not based on the start and end dates?

because you need a status to distinguish NULL/NULL meaning inactive, versus NULL/NULL meaning active always

i would never, never (never!!!) have date columns that show dates in that format

sheesh!!!

BTW, my "no, i'm not" was in response to "is everyone on board that..."

try reading my posts from [quoted stuff] - [end]

eh I guess someone misinterpreted a double negative