pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNULL values or not?
13+ messages / 7 participants
[nested] [flat]
* NULL values or not?
@ 2001-12-21 09:53 Archibald Zimonyi <archie@netg.se>
2001-12-21 12:29 ` Re: NULL values or not? Jan Wieck <janwieck@yahoo.com>
2001-12-21 12:37 ` Re: NULL values or not? D'Arcy J.M. Cain <darcy@druid.net>
2001-12-21 12:41 ` Re: NULL values or not? Aasmund Midttun Godal <postgresql@envisity.com>
2001-12-22 06:32 ` Re: NULL values or not? Andrew G. Hammond <drew@xyzzy.dhs.org>
0 siblings, 4 replies; 13+ messages in thread
From: Archibald Zimonyi @ 2001-12-21 09:53 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: NULL values or not?
2001-12-21 09:53 NULL values or not? Archibald Zimonyi <archie@netg.se>
@ 2001-12-21 12:29 ` Jan Wieck <janwieck@yahoo.com>
2001-12-21 15:08 ` Re: NULL values or not? Archibald Zimonyi <archie@netg.se>
3 siblings, 1 reply; 13+ messages in thread
From: Jan Wieck @ 2001-12-21 12:29 UTC (permalink / raw)
To: Archibald Zimonyi <archie@netg.se>; +Cc: pgsql-sql
Archibald Zimonyi 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.
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.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: NULL values or not?
2001-12-21 09:53 NULL values or not? Archibald Zimonyi <archie@netg.se>
2001-12-21 12:29 ` Re: NULL values or not? Jan Wieck <janwieck@yahoo.com>
@ 2001-12-21 15:08 ` Archibald Zimonyi <archie@netg.se>
2001-12-21 15:24 ` Re: NULL values or not? Aasmund Midttun Godal <postgresql@envisity.com>
0 siblings, 1 reply; 13+ messages in thread
From: Archibald Zimonyi @ 2001-12-21 15:08 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: NULL values or not?
2001-12-21 09:53 NULL values or not? Archibald Zimonyi <archie@netg.se>
2001-12-21 12:29 ` Re: NULL values or not? Jan Wieck <janwieck@yahoo.com>
2001-12-21 15:08 ` Re: NULL values or not? Archibald Zimonyi <archie@netg.se>
@ 2001-12-21 15:24 ` Aasmund Midttun Godal <postgresql@envisity.com>
2001-12-21 15:48 ` Re: NULL values or not? Archibald Zimonyi <archie@netg.se>
0 siblings, 1 reply; 13+ messages in thread
From: Aasmund Midttun Godal @ 2001-12-21 15:24 UTC (permalink / raw)
To: archie@netg.se; +Cc: pgsql-sql
What is the problem with nulls? why are they bad??
Regards,
Aasmund.
On Fri, 21 Dec 2001 16:08:07 +0100 (CET), Archibald Zimonyi <archie@netg.se> wrote:
>
> 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?
>
> 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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
Aasmund Midttun Godal
aasmund@godal.com - http://www.godal.com/
+47 40 45 20 46
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: NULL values or not?
2001-12-21 09:53 NULL values or not? Archibald Zimonyi <archie@netg.se>
2001-12-21 12:29 ` Re: NULL values or not? Jan Wieck <janwieck@yahoo.com>
2001-12-21 15:08 ` Re: NULL values or not? Archibald Zimonyi <archie@netg.se>
2001-12-21 15:24 ` Re: NULL values or not? Aasmund Midttun Godal <postgresql@envisity.com>
@ 2001-12-21 15:48 ` Archibald Zimonyi <archie@netg.se>
2001-12-21 16:55 ` Re: NULL values or not? Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 13+ messages in thread
From: Archibald Zimonyi @ 2001-12-21 15:48 UTC (permalink / raw)
To: Aasmund Midttun Godal <postgresql@envisity.com>; +Cc: pgsql-sql
> What is the problem with nulls? why are they bad??
>
NULLs are not bad, I have never claimed them to be. I try to avoid them
personally, which tends to make my number of tables many, but with fewer
lines usually since I don't have any NULLS.
However the current structure I have takes too long time (at least I think
so) and when I used VACUUM ANALYZE I went from roughly a 1 second query to
a 1 minute query. In my opinion that should not happen.
And if I have a structure with the following data:
CREATE TABLE person
(
person_id int2,
FirstName text,
LastName text,
phone_id int2
);
CREATE TABLE phonenumbers
(
phone_id int2,
phonenumber
);
where in table person phone_id could be NULL.
I would still have to join together my tables in order to retrieve all
info with a left join.
So again, I have no problems with NULLS, I just would like someone to
share when they use them contra when they don't.
Archie
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: NULL values or not?
2001-12-21 09:53 NULL values or not? Archibald Zimonyi <archie@netg.se>
2001-12-21 12:29 ` Re: NULL values or not? Jan Wieck <janwieck@yahoo.com>
2001-12-21 15:08 ` Re: NULL values or not? Archibald Zimonyi <archie@netg.se>
2001-12-21 15:24 ` Re: NULL values or not? Aasmund Midttun Godal <postgresql@envisity.com>
2001-12-21 15:48 ` Re: NULL values or not? Archibald Zimonyi <archie@netg.se>
@ 2001-12-21 16:55 ` Josh Berkus <josh@agliodbs.com>
2001-12-21 17:22 ` Re: NULL values or not? Archibald Zimonyi <archie@netg.se>
2001-12-21 21:37 ` Re: NULL values or not? Wei Weng <wweng@kencast.com>
0 siblings, 2 replies; 13+ messages in thread
From: Josh Berkus @ 2001-12-21 16:55 UTC (permalink / raw)
To: Archibald Zimonyi <archie@netg.se>; Aasmund Midttun Godal <postgresql@envisity.com>; +Cc: pgsql-sql
Archibald,
> So again, I have no problems with NULLS, I just would like someone to
> share when they use them contra when they don't.
Thank you for bringing a theory question onto the list! We spend much
of our time on pgsql-sql focused on practical minutia. Thus several of
the respondants who siezed on your hypothetical example rather than
answering the question. I'd love to see some of the more experienced DB
theorists weigh in on this issue (Tom? Joe C.?).
Relationally speaking, NULLs are a necessary evil. You are right to
approach them with caution. I cannot count the number of times one of
my database rescue attempts has involved eliminating, sub-tabling, or
consolidating NULLable columns.
By coincidence, yesterday I posted this to PGSQL-NOVICE:
============================================================
> 3) Use of NULL
>
> It would facilitate entering data into TABLE clone if seq, qual...
> were defined as NULL even though values for these columns will/does
> exist. If I defined these columns as NULL I would not have to have
> ALL of the data together at one time, but could enter it in steps.
> Is this an ill conceived notion?
Well, this purpose is what NULL is for. Cases where data is unknown or
not yet available. That being said, allowing NULLs takes data integrity
out of the table design and moves it elsewhere in the software. If you
allow NULLs for seq, then you will need to create a data integrity
report that searches for old entries with NULL in the seq column.
Otherwise, you risk having some required data never filled in.
Also, remember that you can't JOIN on a NULL value. For example, if you
allow NULLs in, say clones.gb_id, then if you do a report on clones
JOIN
gb_accessions, the clones who are missing GB will not show up with a
blank GB, instead they will not show up at all! You can get around this
with OUTER JOINS, but OUTER JOINs are not dependable for multi-column
joins.
Instead, I recommend that everywhere it is possible, you have an actual
value that indicates why the data has not been filled in. FOr example,
you could create a gb_accession with the ID of zero (0) (and accn_no,
gi_no of 0 as well) which would indicate (to you) "gb not run yet".
This gives you more information than NULL (which could indicate a
number
of things: GB not run, GB lost, data error, program error, etc.), as
well as supporting JOINs cleanly. You could even have more than one
such value to indicate different reasons for missing info.
=====================================================
For a more in-depth discussion of NULLs and their problems and
workarounds, see Fabian Pascal's "Practical Issues in Database
Management", which has the better part of a chapter on the topic.
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: NULL values or not?
2001-12-21 09:53 NULL values or not? Archibald Zimonyi <archie@netg.se>
2001-12-21 12:29 ` Re: NULL values or not? Jan Wieck <janwieck@yahoo.com>
2001-12-21 15:08 ` Re: NULL values or not? Archibald Zimonyi <archie@netg.se>
2001-12-21 15:24 ` Re: NULL values or not? Aasmund Midttun Godal <postgresql@envisity.com>
2001-12-21 15:48 ` Re: NULL values or not? Archibald Zimonyi <archie@netg.se>
2001-12-21 16:55 ` Re: NULL values or not? Josh Berkus <josh@agliodbs.com>
@ 2001-12-21 17:22 ` Archibald Zimonyi <archie@netg.se>
2001-12-21 21:09 ` Re: NULL values or not? Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 13+ messages in thread
From: Archibald Zimonyi @ 2001-12-21 17:22 UTC (permalink / raw)
To: pgsql-sql
Thank you, thank you, thank you, thank you....
Your post, Josh, was very informative for me. As it is now, my Vampire
schema, which I forgot to add as an attachment, doesn't allow a single
NULL value. I do have a small amount of data, which is why things might
still work as fast as they do.
The start of my problems is still the fact that when I do VACUUM ANALYZE
my main query takes almost a minute (from around a second), which has made
me perlexed by the whole thing, since the EXPLAIN shows relatively low
values (although reading that is still an art, I have a query which
results in more rows and has higher EXPLAIN values but still takes less
time then my main query).
I have read a book which I find immensly useful and good, called:
The Practical SQL Handbook
published by Addison-Wesley. It is this book that has given me my main
view of SQL and database design. I think my database uses a lot of the
normalization rules which is why I try to avoid NULL values. An example
from my own Vampire database follows:
CREATE TABLE cards
(
card_id int2 DEFAULT NEXTVAL('cards_seq'),
cardname text NOT NULL,
PRIMARY KEY(card_id)
);
CREATE TABLE cards_names
(
card_id int2 NOT NULL,
cardname text NOT NULL,
FOREIGN KEY (card_id) REFERENCES cards
);
The table cards include the primary names of all cards. Each card is
unique, giving it a unique id. Some cards have two names, due to the fact
that there were misprints etc.
So the table cards_names includes those few cards which might have two
names.
CREATE TABLE cards_costs
(
card_id int2 NOT NULL,
cost int2 NOT NULL,
pool bool NOT NULL,
FOREIGN KEY (card_id) REFERENCES cards
);
Some cards, but not all, have a certain cost to play. Instead of having a
"cost" column in "cards" and then having NULL values where the cards have
no cost, I once again decided to create a new table connecting the cost to
a card with it's id. So cards_costs also includes less cards then do the
table cards.
It is this I based my questions on, I know why I do it the way I do, but I
would also like to hear what other people think of this solution, and
perhaps why I should use NULLS instead. As I said in an earlier post,
there is a cost of joining the tables, which is higher then having one
table with NULLS.
Once again, thanks for the replies folks,
Archie
On Fri, 21 Dec 2001, Josh Berkus wrote:
> Archibald,
>
> > So again, I have no problems with NULLS, I just would like someone to
> > share when they use them contra when they don't.
>
> Thank you for bringing a theory question onto the list! We spend much
> of our time on pgsql-sql focused on practical minutia. Thus several of
> the respondants who siezed on your hypothetical example rather than
> answering the question. I'd love to see some of the more experienced DB
> theorists weigh in on this issue (Tom? Joe C.?).
>
> Relationally speaking, NULLs are a necessary evil. You are right to
> approach them with caution. I cannot count the number of times one of
> my database rescue attempts has involved eliminating, sub-tabling, or
> consolidating NULLable columns.
>
> By coincidence, yesterday I posted this to PGSQL-NOVICE:
>
> ============================================================
> > 3) Use of NULL
> >
> > It would facilitate entering data into TABLE clone if seq, qual...
> > were defined as NULL even though values for these columns will/does
> > exist. If I defined these columns as NULL I would not have to have
> > ALL of the data together at one time, but could enter it in steps.
> > Is this an ill conceived notion?
>
> Well, this purpose is what NULL is for. Cases where data is unknown or
> not yet available. That being said, allowing NULLs takes data integrity
> out of the table design and moves it elsewhere in the software. If you
> allow NULLs for seq, then you will need to create a data integrity
> report that searches for old entries with NULL in the seq column.
> Otherwise, you risk having some required data never filled in.
>
> Also, remember that you can't JOIN on a NULL value. For example, if you
> allow NULLs in, say clones.gb_id, then if you do a report on clones
> JOIN
> gb_accessions, the clones who are missing GB will not show up with a
> blank GB, instead they will not show up at all! You can get around this
> with OUTER JOINS, but OUTER JOINs are not dependable for multi-column
> joins.
>
> Instead, I recommend that everywhere it is possible, you have an actual
> value that indicates why the data has not been filled in. FOr example,
> you could create a gb_accession with the ID of zero (0) (and accn_no,
> gi_no of 0 as well) which would indicate (to you) "gb not run yet".
> This gives you more information than NULL (which could indicate a
> number
> of things: GB not run, GB lost, data error, program error, etc.), as
> well as supporting JOINs cleanly. You could even have more than one
> such value to indicate different reasons for missing info.
> =====================================================
>
> For a more in-depth discussion of NULLs and their problems and
> workarounds, see Fabian Pascal's "Practical Issues in Database
> Management", which has the better part of a chapter on the topic.
>
> -Josh
>
> ______AGLIO DATABASE SOLUTIONS___________________________
> Josh Berkus
> Complete information technology josh@agliodbs.com
> and data management solutions (415) 565-7293
> for law firms, small businesses fax 621-2533
> and non-profit organizations. San Francisco
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: NULL values or not?
2001-12-21 09:53 NULL values or not? Archibald Zimonyi <archie@netg.se>
2001-12-21 12:29 ` Re: NULL values or not? Jan Wieck <janwieck@yahoo.com>
2001-12-21 15:08 ` Re: NULL values or not? Archibald Zimonyi <archie@netg.se>
2001-12-21 15:24 ` Re: NULL values or not? Aasmund Midttun Godal <postgresql@envisity.com>
2001-12-21 15:48 ` Re: NULL values or not? Archibald Zimonyi <archie@netg.se>
2001-12-21 16:55 ` Re: NULL values or not? Josh Berkus <josh@agliodbs.com>
2001-12-21 17:22 ` Re: NULL values or not? Archibald Zimonyi <archie@netg.se>
@ 2001-12-21 21:09 ` Josh Berkus <josh@agliodbs.com>
2001-12-22 09:30 ` Re: NULL values or not? Archibald Zimonyi <archie@netg.se>
0 siblings, 1 reply; 13+ messages in thread
From: Josh Berkus @ 2001-12-21 21:09 UTC (permalink / raw)
To: Archibald Zimonyi <archie@netg.se>; pgsql-sql
Archibald,
> The start of my problems is still the fact that when I do VACUUM
> ANALYZE
> my main query takes almost a minute (from around a second), which has
> made
> me perlexed by the whole thing, since the EXPLAIN shows relatively
> low
> values (although reading that is still an art, I have a query which
> results in more rows and has higher EXPLAIN values but still takes
> less
> time then my main query).
I believe that this is a seperate problem, not at all related to your
use of NULLs or not. I have queries in some of my databases that join
as many as 18 different tables (some multiple times), and they run fine
and VACUUM speeds them up, as it's supposed to. So I believe that you
have a configuration, system, or version problem that is unrelated to
your schema.
To get help from the core developers, I suggest that you post, in a
seperate e-mail:
1. Your Postgres version (and if it's not 7.1.3 you should maybe
upgrade)
2. Your schema
3. The query that behaves oddly
4. Explain & time before VACUUM
5. Explain & time after VACUUM
6. Your hardware platform data.
> I have read a book which I find immensly useful and good, called:
>
> The Practical SQL Handbook
>
> published by Addison-Wesley. It is this book that has given me my
> main
> view of SQL and database design.
I would very much appreciate it if you sent me a mini-review of the book
for Techdocs Book Reviews. See:
http://techdocs.postgresql.org/bookreviews.php
for format, etc.
> It is this I based my questions on, I know why I do it the way I do,
> but I
> would also like to hear what other people think of this solution, and
> perhaps why I should use NULLS instead. As I said in an earlier post,
> there is a cost of joining the tables, which is higher then having
> one
> table with NULLS.
The cost should not be appreciably higher for your size database.
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: NULL values or not?
2001-12-21 09:53 NULL values or not? Archibald Zimonyi <archie@netg.se>
2001-12-21 12:29 ` Re: NULL values or not? Jan Wieck <janwieck@yahoo.com>
2001-12-21 15:08 ` Re: NULL values or not? Archibald Zimonyi <archie@netg.se>
2001-12-21 15:24 ` Re: NULL values or not? Aasmund Midttun Godal <postgresql@envisity.com>
2001-12-21 15:48 ` Re: NULL values or not? Archibald Zimonyi <archie@netg.se>
2001-12-21 16:55 ` Re: NULL values or not? Josh Berkus <josh@agliodbs.com>
2001-12-21 17:22 ` Re: NULL values or not? Archibald Zimonyi <archie@netg.se>
2001-12-21 21:09 ` Re: NULL values or not? Josh Berkus <josh@agliodbs.com>
@ 2001-12-22 09:30 ` Archibald Zimonyi <archie@netg.se>
0 siblings, 0 replies; 13+ messages in thread
From: Archibald Zimonyi @ 2001-12-22 09:30 UTC (permalink / raw)
To: pgsql-sql
> To get help from the core developers, I suggest that you post, in a
> seperate e-mail:
> 1. Your Postgres version (and if it's not 7.1.3 you should maybe
> upgrade)
> 2. Your schema
> 3. The query that behaves oddly
> 4. Explain & time before VACUUM
> 5. Explain & time after VACUUM
> 6. Your hardware platform data.
>
Will do, thanks again for the input.
Archie
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: NULL values or not?
2001-12-21 09:53 NULL values or not? Archibald Zimonyi <archie@netg.se>
2001-12-21 12:29 ` Re: NULL values or not? Jan Wieck <janwieck@yahoo.com>
2001-12-21 15:08 ` Re: NULL values or not? Archibald Zimonyi <archie@netg.se>
2001-12-21 15:24 ` Re: NULL values or not? Aasmund Midttun Godal <postgresql@envisity.com>
2001-12-21 15:48 ` Re: NULL values or not? Archibald Zimonyi <archie@netg.se>
2001-12-21 16:55 ` Re: NULL values or not? Josh Berkus <josh@agliodbs.com>
@ 2001-12-21 21:37 ` Wei Weng <wweng@kencast.com>
1 sibling, 0 replies; 13+ messages in thread
From: Wei Weng @ 2001-12-21 21:37 UTC (permalink / raw)
To: pgsql-sql
Forgive my ignorance, but what exactly is a "multi-column" join?
<snip>
> gb_accessions, the clones who are missing GB will not show up with a
> blank GB, instead they will not show up at all! You can get around this
> with OUTER JOINS, but OUTER JOINs are not dependable for multi-column
> joins.
<snip>
I also used a lot of NULLs in my table schemas though never in the
primary(foreign) keys. Is that bad?
Thank you.
==========
Wei Weng
Network Software Engineer
KenCast Inc.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: NULL values or not?
2001-12-21 09:53 NULL values or not? Archibald Zimonyi <archie@netg.se>
@ 2001-12-21 12:37 ` D'Arcy J.M. Cain <darcy@druid.net>
3 siblings, 0 replies; 13+ messages in thread
From: darcy@druid.net @ 2001-12-21 12:37 UTC (permalink / raw)
To: archie@netg.se; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: NULL values or not?
2001-12-21 09:53 NULL values or not? Archibald Zimonyi <archie@netg.se>
@ 2001-12-21 12:41 ` Aasmund Midttun Godal <postgresql@envisity.com>
3 siblings, 0 replies; 13+ messages in thread
From: Aasmund Midttun Godal @ 2001-12-21 12:41 UTC (permalink / raw)
To: archie@netg.se; +Cc: pgsql-sql
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@netg.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@postgresql.org
Aasmund Midttun Godal
aasmund@godal.com - http://www.godal.com/
+47 40 45 20 46
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: NULL values or not?
2001-12-21 09:53 NULL values or not? Archibald Zimonyi <archie@netg.se>
@ 2001-12-22 06:32 ` Andrew G. Hammond <drew@xyzzy.dhs.org>
3 siblings, 0 replies; 13+ messages in thread
From: Andrew G. Hammond @ 2001-12-22 06:32 UTC (permalink / raw)
To: Archibald Zimonyi <archie@netg.se>; +Cc: pgsql-sql
> 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.
Using a seperate table for phone numbers makes sense if you need to keep
track of multiple phone numbers per person or multiple people per phone
number. Otherwise it's pointless. You add unnecessary and useless
complexity to your system. Null values to indicate null data make
perfect sense. I'm a strong believer in the KISS principal:
Keep It Short & Simple.
--
Andrew G. Hammond mailto:drew@xyzzy.dhs.org
http://xyzzy.dhs.org/~drew/
56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F
613-389-5481
5CD3 62B0 254B DEB1 86E0 8959 093E F70A B457 84B1
"To blow recursion you must first blow recur" -- me
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2001-12-22 09:30 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-12-21 09:53 NULL values or not? Archibald Zimonyi <archie@netg.se>
2001-12-21 12:29 ` Jan Wieck <janwieck@yahoo.com>
2001-12-21 15:08 ` Archibald Zimonyi <archie@netg.se>
2001-12-21 15:24 ` Aasmund Midttun Godal <postgresql@envisity.com>
2001-12-21 15:48 ` Archibald Zimonyi <archie@netg.se>
2001-12-21 16:55 ` Josh Berkus <josh@agliodbs.com>
2001-12-21 17:22 ` Archibald Zimonyi <archie@netg.se>
2001-12-21 21:09 ` Josh Berkus <josh@agliodbs.com>
2001-12-22 09:30 ` Archibald Zimonyi <archie@netg.se>
2001-12-21 21:37 ` Wei Weng <wweng@kencast.com>
2001-12-21 12:37 ` D'Arcy J.M. Cain <darcy@druid.net>
2001-12-21 12:41 ` Aasmund Midttun Godal <postgresql@envisity.com>
2001-12-22 06:32 ` Andrew G. Hammond <drew@xyzzy.dhs.org>
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