Null or Default value?

[subnet_rx]

What's the most optimized way to handle fields that aren't required to be filled in at record creation? NULL or give it a default value

[r937]

NULL is better

unless, of course, you know, a default is more appropriate



example of NULL appropriate --

create table employees
( id integer not null primary key auto_increment
, name varchar(99) not null
, hiredate  datetime not null
, termdate  datetime null
);

hiredate must be specified, because unless the employee is hired, we don't want her in the employees table

termination date would be NULL until the employee terminates, then it gets a value

a "default" value in termination date makes no sense whatsoever


example of default appropriate --

create table users
( id integer not null primary key auto_increment
, username varchar(16) not null
, password datetime not null
, status char(1) default 'U'
);

here the user's status defaults to U (ordinary user) so that we don't always have to specify it when adding a user --

insert into users (username,password) values ('curly','woobwoob')
insert into users (username,password) values ('larry','heyfellas')
insert into users (username,password) values ('moe','whyioughta')

insert into users (username,password,status) values ('superuser','iamgod','S')