Re: 3vl 2vl and NULL

From: Leif B. Kristensen <junkmail2_at_solumslekt.org>
Date: Thu, 15 Dec 2005 07:38:11 +0100
Message-ID: <1LydnWtxyPfFkjzeRVnzvA_at_telenor.com>


Gene Wirchenko skrev:

> On Thu, 15 Dec 2005 00:41:51 +0100, Hugo Kornelis wrote:

>>I object to the choice of words "It is unknown", because in fact Uncle
>>Vernon's age IS known. Not to you and me, but it is known to many
>>others.

>
> It is sad. Uncle Vernon wandered into town years ago, apparently
> a severe amnesia case. We do not know who he really is, but someone
> picked the name "Vernon" for him and it stuck. Since he is great with
> kids, he is known as "Uncle Vernon". We think that he is in his late
> 40s, but have no way of being sure. We have been able to measure his
> height and weight though.

This is a very common scenario in genealogy. Thus, genealogy databases must be designed specifically to handle missing, incomplete, or even conflicting data. One way of solving it, is to record every known or documented scap of information as a separate "event", and then connect it to a "person" through a many-to-many entity that might be called "participants".

NULLS tend to crop up, though. In my own genealogy data model, I'm using a "relations" table to keep track of parents. In aggregate reports, missing parents will always be represented as NULL. To work around it, I made a couple of custom functions:

CREATE OR REPLACE FUNCTION find_father(INTEGER) RETURNS INTEGER AS $$ DECLARE
    par INTEGER;
BEGIN
    SELECT parent_fk INTO par FROM relations

        WHERE relation_type = 1 AND child_fk = $1;     IF par IS NULL THEN

        RETURN 0;
    ELSE
        RETURN par;
    END IF;
END;
$$ LANGUAGE plpgsql;

Thus, the application never sees a NULL value.

-- 
Leif Biberg Kristensen
http://solumslekt.org/
Received on Thu Dec 15 2005 - 07:38:11 CET

Original text of this message