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

Remember to vote!