From: darcy@druid.net
To: archie@netg.se
Cc: pgsql-sql@postgresql.org
Subject: Re: NULL values or not?
Date: Fri, 21 Dec 2001 07:37:59 -0500 (EST)
Message-ID: <20011221123759.5760E1A69@druid.net> (raw)
In-Reply-To: <Pine.LNX.4.21.0112211045420.15695-100000@valdez.netg.se>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: darcy@druid.net, archie@netg.se
Subject: Re: NULL values or not?
In-Reply-To: <20011221123759.5760E1A69@druid.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox