Re: postgresql vs mysql

If you can remove NULLs without breaking OUTER JOIN, more power to you.

In the vast majority of cases, all fields in a table should have a NOT
NULL constraint.  Storing a NULL value makes little sense, since you're
storing something you don't know.  If you don't know something, why are
you trying to record it?  From a strict relational sense, the existence
of NULL values in your fields indicates that your primary keys are not
truly candidate keys for all your fields.  That means your database
isn't [BCNF] normalized.

Arguments about de-normalization generally result in the basic
limitation in nearly all RDBMS's that they do not allow you to optimize
how data is physically stored on disk.  That is, a generalized SQL
database like Oracle, MySQL, PostgreSQL, etc. sacrifice the ability to
control how data is physically store in order to be a generalized
database that can store generic domains in the form of the most common
datatypes that computer programs use.

This is a basic limitation of using a generalized database engine, and
if your application demands higher performance than you can get with a
general RDBMS, you'll have to develop your own task-specific RDBMS or
modify your schema so that the problem can be mitigated.  Schema
de-normalization is a way of purposefully degrading the normal quality
of your schema in order to make up for shortcomings of the database
engine and limitations of computerized data storage.  As long as you
understand that de-normalization is a practical workaround and never a
wise logical design choice from the get-go, you shouldn't feel too bad
about doing it.

--
Brandon Aiken
CS/IT Systems Engineer

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tim Tassonis
Sent: Thursday, February 22, 2007 10:31 AM
To: Rich Shepard
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] postgresql vs mysql

Rich Shepard wrote:
> On Thu, 22 Feb 2007, Tim Tassonis wrote:
>
>> I do still think it is a bit of an oddity, the concept of the null
>> column.
>> From my experience, it creates more problems than it actually solves
and
>> generally forces you to code more rather than less in order to
achieve
>> your goals.
>
> Tim,
>
>   Long ago, a lot of database applications used 99, or 999, or -1 to
> indicate an unknown value. However, those don't fit well with a
textual
> field and they will certainly skew results if used in arithmetic
> calculations in numeric fields.

I remember, my first database to write stuff for was an IMB IMS
hierarchical/network one.

>
>   The concept of NULL representing an unknown value, and therefore one
that
> cannot be compared with any other value including other NULLs, is no
> different from the concept of zero which was not in mathematics for
the
> longest time until some insightful Arab mathematician saw the need for
a
> representation of 'nothing' in arithmetic and higher mathematics.
>
>   There was probably resistance to that idea, too, as folks tried to
wrap
> their minds around the idea that 'nothing' could be validly
represented
> by a
> symbol and it was actually necessary to advance beyond what the Greeks
and
> Romans -- and everyone else -- could do. Now, one would be thought a
bit
> strange to question the validity of zero.

That's one point for me, then!. NULL exactly is _not_ the equivalent the

the number 0, but the mentioned strange symbol that has to be treated
specially and does not allow normal calculation, like '0' does in
mathematics. I don't know how many times I had to write a query that
ends with:

- or column is null
- and column is not null

exactly because it is a special symbol. In mathematics, the only special

case for zero that springs to my mind is the division of something by
zero (I'm by no means a mathematician).

As a completely irrelevant sidenote to the discussion, I'm greek and not

arabic, but I certinly do accept the superiority of the arabic notation.

>
>   NULL solves as many intransigent problems with digital data storage
and
> manipulation in databases as zero did in the realm of counting.

As I said, I don't deny it solves some problems (that could be solved in

a different way, too), but in my opinion, it creates more (that also can

  be solved, as above examples show).

Tim


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org/


--------------------------------------------------------------------
** LEGAL DISCLAIMER **
Statements made in this e-mail may or may not reflect the views and
opinions of Wineman Technology, Inc. or its employees.

This e-mail message and any attachments may contain legally privileged,
confidential or proprietary information. If you are not the intended
recipient(s), or the employee or agent responsible for delivery of
this message to the intended recipient(s), you are hereby notified
that any dissemination, distribution or copying of this e-mail
message is strictly prohibited. If you have received this message in
error, please immediately notify the sender and delete this e-mail
message from your computer.