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).
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.
Regards,
Merlin
