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@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Tim Tassonis Sent: Thursday, February 22, 2007 10:31 AM To: Rich Shepard Cc: pgsql-general@xxxxxxxxxxxxxx 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.
