On Wed, Jun 28, 2006 at 05:14:42PM +0200, Leif B. Kristensen wrote:
> On a tangent to the never-ending NULL debate, I've got a table:
>
> CREATE TABLE events (
> event_id INTEGER PRIMARY KEY,
> tag_fk INTEGER REFERENCES tags (tag_id),
> place_fk INTEGER REFERENCES places (place_id),
> event_date CHAR(18) NOT NULL DEFAULT '000000003000000001',
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
This is Badâ¢. What's wrong with TIMESTAMPTZ?
> sort_date DATE NOT NULL DEFAULT '40041024BC',
> event_note TEXT NOT NULL DEFAULT ''
This is generally bad, too. It's got MySQL goo all over it. Do you
want NOT NULL, or do you want a DEFAULT that's meaningful?
> );
>
> The event_note will contain text in roughly 1 out of 5 rows:
>
> pgslekt=> select count(*) from events;
> count
> -------
> 29473
> (1 row)
>
> pgslekt=> select count(*) from events where event_note <> '';
> count
> -------
> 5572
> (1 row)
>
> I wonder if this is sane design, in theory and in practice, or should I
> break out the event_note field in a separate table?
Only if it's a 1:N relationship. In this case, I'd say scrap the NOT
NULL requirement and replace the empty strings with NULLs.
Cheers,
D
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter
Remember to vote!