Re: 3vl 2vl and NULL

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

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/