Centuries ago, Nostradamus foresaw when merlin(dot)moncure(at)rcsonline(dot)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.
> 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
- 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
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
Black holes are where God divided by zero.
In response to
pgsql-advocacy by date
|Next:||From: Shridhar Daithankar||Date: 2003-11-03 06:46:16|
|Subject: [Fwd: [LIH]OpenOffice.org 1.1 <-> PostgreSQL connectivity]|
|Previous:||From: Josh Berkus||Date: 2003-10-31 22:33:30|
|Subject: Re: Use/Abuse of Nulls|