Re: NULL values or not?

When it comes to database design, there are many books on the topic.

However, here are the principles I use:

1.) The schema should reflect the model (i.e. you should be able to do everything you need to, and not be able to do anything you shouldn't)
2.) It should be easy to understand, maintain and use.

In my opinion null values don't go against any of these principles. Adding all sorts of id's and unnecessary tables goes against #2. If you need to be able to register several numbers on each person, then you need two tables.

Regards,

Aasmund.

On Fri, 21 Dec 2001 10:53:38 +0100 (CET), Archibald Zimonyi <archie(at)netg(dot)se> wrote:
>
> Hi there,
>
> 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:
>
> 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);
>
>
> Another way of doing the same world is the following
>
> CREATE TABLE person
> (
> id int2,
> firstname text,
> lastname text,
> phonenumber text
> );
>
> with all appropriate keys etc.
>
> A SELECT statement would look like this:
>
> SELECT firstname, lastname, phonenumber
> FROM person;
>
> As I wrote, I usually try to avoid NULL values, thus creating my tables as
> the first example. What kind of thumb rules do you use when it comes to
> NULL values? Again, I am referring to my vampire database which I named a
> few days ago (btw, without VACUUM the SELECT statement takes less then a
> second) and I am planning on making less tables where I can. But it still
> feels wrong to add NULL values when I can avoid them.
>
> Could someone give me some input please?
>
> Thanks in advance,
>
> Archie
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org

Aasmund Midttun Godal

aasmund(at)godal(dot)com - http://www.godal.com/
+47 40 45 20 46