Re: empty text fields

On Wednesday 28. June 2006 17:37, David Fetter wrote:
>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?

What's Bad™?

The event_date field is a "fuzzy date" construct. It will allow the
storage of such dates as "1784", "ca. 1810", "May 1852", "1798 or
1799", "between 1820 and 1830" and so on. It's very useful in
historical research to handle such dates meaningfully.

By the way, I was also going to ask sometime if there's a better way to
handle such a construct than an unspecified CHAR(18) column.

>>     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?

Sure. I came to PostgreSQL about a year ago. I've been using MySQL for
read-only data since 2002, but when I started writing my own data-entry
routines, I found the "quiet truncation" misfeature of MySQL to render
it all but useless.

>> 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.

Thanks. I'll think it over.
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE