Re: NULL values or not?

Thus spake Archibald Zimonyi
> I have a question about NULL values. Lets say that we have a world with
> the following info:
> 
> FirstName
> LastName
> PhoneNumber
> 
> 
> Everyone has to have a FirstName and LastName but not everyone has to have
> a PhoneNumber.
> 
> Personally I don't like NULL values, so I would have created to tables for
> the above world roughly like this:

Why don't you like NULLs?  NULLs are an important part os SQL.  however...

> CREATE TABLE person
> (
>   id int2,
>   firstname text,
>   lastname text
> );
> 
> CREATE TABLE phonenumbers
> (
>   id int2,
>   phonenumber text
> );
> 
> with keys and indexes and such things.
> 
> A SELECT statement to retireve all info from these two tables would look
> like this:
> 
> SELECT firstname, lastname, phonenumber
> FROM person
>   LEFT JOIN phonenumbers USING (id);

How about this?

CREATE TABLE person
(   firstname text DEFAULT '',   lastname text NOT NULL,   phonenumber text DEFAULT ''
);

Now you can simply list everything and the phone number will be blank if
you don't have one.  Note that I do the same for firstname since I am not
as convinced as you are that everyone needs two names.  With your philosopy
you would need to break out another table.  Worse, some people only have
first names and some only have last names.

I also put a NOT NULL constraint on lastname to force an entry there
although you can still insert a blank.

However, don't reject NULLs out of hand.  They can be very important
especially when you have foreign keys and such.  It's much cleaner to
put a NULL in a field when you don't know than to have to add a special
"Unknown" entry in the foreign table.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.