Re: empty text fields

On Wednesday 28. June 2006 21:33, Karsten Hilbert wrote:
>On Wed, Jun 28, 2006 at 06:25:22PM +0200, Leif B. Kristensen wrote:
>> >> event_date CHAR(18) NOT NULL DEFAULT
>>
>> 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.
>
>How and where do you handle the fuzziness of it ? In the
>application ? We have to deal with the same thing in medical
>history data and haven't yet satisfactorily solved it.

For now, I'm handling it in the application. But as I'm very much into
moving as much as possible into views, functions and triggers of the
db, I'm investigating how to do it in a more efficient way.

The construct has been lifted pretty much unmodified from "The Master
Genealogist" (TMG), the genealogy program I was using until I decided
to write my own. The format of the string is YYYYMMDDAYYYYMMDDB. The
YYYMMDD is a "normal" date, where the day may be set to 00 if the month
is known, and the month likewise set to 00 if only the year is known.
The A is a qualifier, which can take the following values:

0 = before (date1),
1 = say (date1),
2 = ca. (date1),
3 = exact (date1),
4 = after (date1),
5 = between (date1) and (date2),
6 = (date1) or (date2),
7 = from (date1) to (date2).

The B can be either 0 for BC or 1 for AD.

>> 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.
>
>A composite type comes to mind. Tagged types (google for
>"tagged_type") would probably help, too.
>
>A "full-blown" implementation of a fuzzy timestamp type which
>
>a) preserves the input/update timestamp
>b) allows setting the accuracy of the value per row
>c) allows for known "modifiers" and "terms" ("mid-summer", "second
> half of ...") d) allows for an arbitrary textual addition ("ca.",
> "probably")
>
>would be great. I know I should be helping to write one
>instead of hoping someone does it for me. But I lack the
>expertise to do it properly. I am willing to help, at any
>rate, though.

I haven't felt any need to enter more irregular dates than those
described above. I think that if a date is that much undetermined, it
would be better to render it textually in a free-text note.
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE