Re: 3vl 2vl and NULL
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.> a severe amnesia case. We do not know who he really is, but someone
>
> It is sad. Uncle Vernon wandered into town years ago, apparently
> 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