Re: Use/Abuse of Nulls - Mailing list pgsql-advocacy

Centuries ago, Nostradamus foresaw when merlin.moncure@rcsonline.com ("Merlin Moncure") would write:
> Christopher Browne wrote:
>> The problem I see with the "make another table" approach is that you
>> wind up with another table for everyone to manage.  More data to join;
>> more tables to add data to; none of that comes for free, even if it is
>> cheap, performance-wise.
>> <snip>

> That's true.  I submit though that it is the most straightforward
> way to get around the null problem.  The big downside is it
> encourages the use of left/right joins which are usually the hardest
> for the optimizer to get right (in fact, I almost never use
> left/right joins, even if they directly solve the problem, better to
> use union somehow).

One way or the other, there's a problem of managing complexity.

The more "nullable things" that there are, the more complex your model
is, and it seems to me that this is a fact irrespective of how you
deal with it.

- If you allow NULLs in your data model, the application has to cope
  with that.

- If you forbid NULLs, that pushes the complexity over to the notion
  of having to manage additional tables.

In either case, complexity grows.

> That being said, I usually try and model data considering integrity
> first, flexibility second, simplicity third, and performance fourth
> if at all.  The reason for that is that I can usually count on SQL
> wizardry (either my own or others!) to deal with nasty performance
> issues.  If all else fails, I resort to a hack like a lookup table
> or something of that kind.  In fact, the reason why I love pg so
> much is that I've learned to trust the database to allow me to set
> up the data the way *I* want to without making compromises.  This
> helps a lot in developing projects.

That seems like an appropriate way to go irrespective of the tools in
use.

Making performance "Job #4" is about right because it is very likely
that the performance bottlenecks will fall in very specific places.

Here are all of my .fortunes about optimization; they all fit with the
principle of putting optimization off until you KNOW what needs to be
made more efficient.

"Optimization hinders evolution."  -- Alan Perlis

"It is easier  to optimize correct code, than  correct optimized code"
-- Yves Deville

"We should forget about small efficiencies, say about 97% of the time:
premature optimization is the root of all evil."  -- Donald Knuth

"Rules of Optimization:
     Rule 1: Don't do it.
     Rule 2 (for experts only): Don't do it yet."
-- M.A. Jackson

"More computing sins are committed  in the name of efficiency (without
necessarily achieving it) than for any other single reason - including
blind stupidity."  -- W.A. Wulf
--
If this was helpful, <http://svcs.affero.net/rm.php?r=cbbrowne> rate me
http://cbbrowne.com/info/languages.html
Black holes are where God divided by zero.