Re: empty text fields

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(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!