Re: NULL values or not?

Hi all,

thanks for the replies. Most of you didn't read my post correctly though,
I know that on our earth we have people with more then one FirstName, and
some with no LastName etc. I stated however that if we had a world where
people had only 1 FirstName and 1 LastName but it was not necessary to
have a PhoneNumber would my first of my second structure be advisable to
create?

> I don't know of any particular, but I can imagine cultures
> where people don't have first or last names. For sure alot of
> people on this earth don't have a mailing address that'd fit
> into the "usual" schema. Don't make the schema too scattered
> just to avoid NULLs. Wether you like them or not doesn't
> matter, use them where they are handy.
>
The schema too scattered. That is exactly the way I like to build my
tables, the question was is it advisable? I can still get my (although
fairly low amount of data) statements to work, takes about 1 second to
start the search from the webbpage until the document is done again. That
is fairly fast and well below the time limit that people tend to find
annoying when searching for something.

So in effect my databasestructure works nicely, no NULLS and lots of
tables.

Again, I will include my schema and functions as attachments.

The question I asked, and will ask again, why should I add a column with
NULL values rather then having a new table with that info instead? When
will the NULL value be worth it. I take it there is less cost of simply
having one table scan then joining two tables with a left join in order
to retrive all rows.

What if the PhoneNumber is an ID to another table that would look like
this:

CREATE TABLE phonenumber
(
phonenumber_id int2,
phonenumber
);

with all keys etc.

If I have to retrieve FirstName, LastName and PhoneNumber I would still
have to join the two tables with a left join, since phonenumber_id has to
match in both tables.

Archie