Here is a relational model that I use for my genealogy. It is in postgresql, but it should work fine in SQLite:
All people are stored in the indi table: CREATE TABLE woodbridge.indi ( indi character varying(10) NOT NULL, lname character varying(30), fname character varying(60), title character varying(20), lname_sndx character varying(4), famc character varying(10), sex character(1), birt_date character varying(20), birt_plac character varying(60), chr_date character varying(20), chr_plac character varying(60), deat_date character varying(20), deat_plac character varying(60), buri_date character varying(20), buri_plac character varying(60), refn character varying(20), note character varying(10), sour character varying(10), CONSTRAINT indi_pkey PRIMARY KEY (indi) ) WITHOUT OIDS; This is the relationship and indi is placed in a family unit as a child of the unit: CREATE TABLE woodbridge.child ( fami character varying(10) NOT NULL, seq integer NOT NULL DEFAULT 0, indi character varying(10) NOT NULL, CONSTRAINT child_pkey PRIMARY KEY (fami, seq) ) WITHOUT OIDS; fams is the relationship where two indi's have a spousal relationship CREATE TABLE woodbridge.fams ( indi character varying(10) NOT NULL, seq integer NOT NULL DEFAULT 0, fami character varying(10) NOT NULL, CONSTRAINT fams_pkey PRIMARY KEY (indi, seq) ) WITHOUT OIDS; fami is the description of a family unit, ie a set of parents and resulting children: CREATE TABLE woodbridge.fami ( fami character varying(10) NOT NULL, husb character varying(10), wife character varying(10), marr_date character varying(20), marr_plac character varying(60), div character(1), div_date character varying(20), div_plac character varying(60), sour character varying(10), CONSTRAINT fami_pkey PRIMARY KEY (fami) ) WITHOUT OIDS; There are additional tables for things like notes and photos. You can see it running here: http://swoodbridge.com/family/Woodbridge/ It should be pretty easy to convert this to tables for livestock breeding. -Steve Francis GAYREL wrote: > To build a consistent oriented tree we need to associate to the nodes a > ranking property such as the birthdate (or any precedence criterion). > Therefore the ancestor of someone is to be selected among older ones. > To make the ancestor allocation more easy the ancestor's list may be > filtered on birthdate credibility. > The ranking property eliminates the circular link concern. > > > Jan a écrit : >> Hi Mark, >> >> I think that wont work: >> >> Scenario: A calf is born from a mother within your flock but from a >> father outside. The father appears for the first time and you are not >> able to gather information on his father (or grand-grand father). >> Therefore his father is NULL. But later you get the information on his >> father and add it to the animal list: The id of the fathers father is >> then greater then the id of his grandchild (the calf). >> I could start the id initially with 100000 to allocate <100000 ids in >> theses cases, but I am unsure if this is a good way to start. >> >> Mark Hamburg schrieb: >> >>> One of the questions that I believe was raised but not answered on >>> this thread was how to make sure that you don't have circular >>> relationships particularly given that SQLite isn't good at scanning >>> the tree. If you can control the id's then simply require that the id >>> of the child be greater than the id's of the parents. >>> >>> Mark >>> >>> _______________________________________________ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >>> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> >> > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
