DateTime Null Or Minimum Value

[shapper]

Hello,

I am working on a authentication system and I have the following:

create table dbo.Users
(
Id int identity not null,
LastLock datetime not null,
LastLogin datetime not null,
LastPasswordChange datetime not null,
LastReset datetime not null,
Username nvarchar(40) not null constraint Users_Username_U unique,
constraint Users_PK primary key clustered(Id)
) -- Users

When a user opens the account there was never a login, it was never
locked or password changed.
So should I insert in this fields a Null value or a "SQL Minimum
Date".

What is your opinion in relation to this?

Thanks,
Miguel

[Gene Wirchenko]

On Wed, 8 Sep 2010 16:48:54 -0700 (PDT), shapper <mdm...@gmail.com>
wrote:

If the LastLogin semantic matters, have it as a separate table
with optional 1:1 mapping.

Sincerely,

Gene Wirchenko

[Sylvain Lafontaine]

An important criteria in the design of anything is to go for what it's
easier to understand not only for you but for the other people as well. If
someone want to know the list of accounts where there was never a login,
it's easy to make - and understand - a query searching for a list of records
with a Null value but less easy to do the same with the SQL minimum date;
especially when this date could change with the exact type of the data.

Do you know that the minimum date is different on the SQL-Server for the
datetime and smalldatetime and that on Access and other database systems,
you have other minimum for the data as well?

Furthermore, accessing these from a GUI or converting these values to
between different databases or to another type of system could lead to some
serious problems. Practically all systems will understand a Null value but
it's not the same with a minimum date value.

So, going with the Null value is definitely the way to go.

--
Sylvain Lafontaine, ing.
MVP - Access
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

"shapper" <mdm...@gmail.com> wrote in message
news:91df2fe9-f0ca-4945...@k30g2000vbn.googlegroups.com...

[--CELKO--]

You are dealing with events that have durations, but do not model
durations in the table. I would change the schema to get a proper
temporal model. Not knowing your business rules, here is a guess.

CREATE TABLE dbo.UserHistory
(user_name NVARCHAR(40) NOT NULL,

lock_start_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
lock_end_time DATETIME,
CHECK (lock_start_time < lock_end_time),

login_start_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
login_end_time DATETIME,
CHECK (login_start_time < login_end_time),

user_password VARCHAR(16) NOT NULL
CHECK (..),
password_start_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
password_end_time DATETIME,
CHECK (password_start_time < password_end_time),

PRIMARY KEY (user_name, lock_start_time));

Create VIEW on this to get the current situation based on the
CURRENT_TIMESTAMP and NULL columns in the end_time columns. Look at
DATETIME2(n) data types, if you need them.

[Erland Sommarskog]

shapper (mdm...@gmail.com) writes:
> I am working on a authentication system and I have the following:
>
> create table dbo.Users
> (
> Id int identity not null,
> LastLock datetime not null,
> LastLogin datetime not null,
> LastPasswordChange datetime not null,
> LastReset datetime not null,
> Username nvarchar(40) not null constraint Users_Username_U unique,
> constraint Users_PK primary key clustered(Id)
> ) -- Users
>
> When a user opens the account there was never a login, it was never
> locked or password changed.
> So should I insert in this fields a Null value or a "SQL Minimum
> Date".

I think LastLock should be nullable, but you could argue that it should
be the creation date, because it was locked before that. But that is
quite stretched.

LastLogin needs to be nullable, because else you need a magic value. And
a magic value is just a more difficult way to spell N-U-L-L.

LastPasswordChange could be set to the creation date, because the password
was set at that time.

LastReset could be NULL or be set to the creation date depending how
you look at tie.

And, yen, CreationDate should probably be a column of its own in the
table.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Gene Wirchenko]

On Fri, 10 Sep 2010 22:56:25 +0200, Erland Sommarskog
<esq...@sommarskog.se> wrote:

>shapper (mdm...@gmail.com) writes:
>> I am working on a authentication system and I have the following:
>>
>> create table dbo.Users
>> (
>> Id int identity not null,
>> LastLock datetime not null,
>> LastLogin datetime not null,
>> LastPasswordChange datetime not null,
>> LastReset datetime not null,
>> Username nvarchar(40) not null constraint Users_Username_U unique,
>> constraint Users_PK primary key clustered(Id)
>> ) -- Users
>>
>> When a user opens the account there was never a login, it was never
>> locked or password changed.
>> So should I insert in this fields a Null value or a "SQL Minimum
>> Date".
>
>I think LastLock should be nullable, but you could argue that it should
>be the creation date, because it was locked before that. But that is
>quite stretched.
>
>LastLogin needs to be nullable, because else you need a magic value. And
>a magic value is just a more difficult way to spell N-U-L-L.

No, you do not. A table with login id and last login would do
it. If there has not been a login under that login id yet, then there
is no row in that table for that login id.

[snip]

Sincerely,

Gene Wirchenko

[Erland Sommarskog]

That's also a more difficult way to spell N-U-L-L.

Such a table would make sense if you want to track all logins, not only the
latest. But having a table for a single value? Makes sense if you have a
number of columns that are NULL most of the times, and are non-NULL
together. But for a single value that is non-NULL only in a special case?
Nope.

[m]

The difference is a null in column space versus a null in row space!

They are both null, but IMHO it is easier to code for a null in row space
than it is for a null in column space (considering the insert / update race
condition) and you get the fringe benefit of tracking all logins. The
problem with this design is that the table will grow quickly and without a
good set of indices, either insert or select performance will be a problem.
These are solvable problems, just like the insert / update race is solvable
with lock hints + transaction isolation ;)

"Erland Sommarskog" <esq...@sommarskog.se> wrote in message
news:Xns9DF077009...@127.0.0.1...