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 ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org
