Skip site navigation (1) Skip section navigation (2)

NULL values or not?

From: Archibald Zimonyi <archie(at)netg(dot)se>
To: pgsql-sql(at)postgresql(dot)org
Subject: NULL values or not?
Date: 2001-12-21 09:53:38
Message-ID: Pine.LNX.4.21.0112211045420.15695-100000@valdez.netg.se (view raw or flat)
Thread:
Lists: pgsql-sql
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


In response to

Responses

pgsql-sql by date

Next:From: mallahDate: 2001-12-21 10:42:41
Subject: getting the name of currenlty connected host..
Previous:From: Christopher Kings-LynneDate: 2001-12-21 02:57:55
Subject: Re: Transaction and cascade problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group