Re: [sqlite] Db design question (so. like a tree)

I am not familiar with breeding business. My suggestion comes from a very different problem that i solved recently. The challenge was to describe a tree and to find the path to the root starting from any leaf or intermediate node. How to do? 1) The entity supported by the node shall support at

Re: [sqlite] Db design question (so. like a tree)

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),

Re: [sqlite] Db design question (so. like a tree)

On 5/06/2009 5:27 PM, 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. "Ancestor" is a *derived*

Re: [sqlite] Db design question (so. like a tree)

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

Re: [sqlite] Db design question (so. like a tree)

On 4 Jun 2009, at 2:48pm, Mark Hamburg wrote: > 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. I don't think it can be done

Re: [sqlite] Db design question (so. like a tree)

John Machin wrote: > On 5/06/2009 12:59 AM, Griggs, Donald wrote: > >> Regarding: >>I could start the id initially with 10 to allocate >> >> That WOULD allow for a bunch of bull.;-) >> > > Don't horse about with IDs with attached meaning; it's a cow of a > concept whose

Re: [sqlite] Db design question (so. like a tree)

On 5/06/2009 12:59 AM, Griggs, Donald wrote: > Regarding: >I could start the id initially with 10 to allocate > > That WOULD allow for a bunch of bull.;-) Don't horse about with IDs with attached meaning; it's a cow of a concept whose outworking could well be catastrophic and

Re: [sqlite] Db design question (so. like a tree)

Regarding: I could start the id initially with 10 to allocate That WOULD allow for a bunch of bull.;-) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Db design question (so. like a tree)

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

Re: [sqlite] Db design question (so. like a tree)

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

Re: [sqlite] Db design question (so. like a tree)

eneral Discussion of SQLite Database > Subject: Re: [sqlite] Db design question (so. like a tree) > > > Jan schrieb: >> Hi, >> >> I am planning a database for animal breeding. I need to store the >> relations between individuals and therefore I have to build someth

Re: [sqlite] Db design question (so. like a tree)

Of Ibrahim A Sent: Thursday, June 04, 2009 5:59 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Db design question (so. like a tree) Jan schrieb: > Hi, > > I am planning a database for animal breeding. I need to store the > relations between individuals and therefore I h

Re: [sqlite] Db design question (so. like a tree)

Jan schrieb: > thx Ibrahim. Give me some time to digest yours and other suggestions. > > But it seems I will end up with a adjunct list PLUS something. Of course > you are right: I need to store many other information for each animal. I > definitely need to use a database (sqlite of course). It

Re: [sqlite] Db design question (so. like a tree)

thx Ibrahim. Give me some time to digest yours and other suggestions. But it seems I will end up with a adjunct list PLUS something. Of course you are right: I need to store many other information for each animal. I definitely need to use a database (sqlite of course). It will be used for

Re: [sqlite] Db design question (so. like a tree)

One further advice : The fastest solution for your Problem would be to create a Array with fixed size Entries to describe the relationship between animals. in C you would simply end up with a struct like : struct ancestors { integer id_father ; integer id_mother ; } ; If you have a

Re: [sqlite] Db design question (so. like a tree)

Jan schrieb: > Hi, > > I am planning a database for animal breeding. I need to store the > relations between individuals and therefore I have to build something > like a tree structure. But of course with two parents (There wont be > cloned animals in the database .-) afaik) > > I read a little

Re: [sqlite] Db design question (so. like a tree)

Thanks Dennis and Jay. I'll go through your posts. It seems your approach "extends" the list model i'd like to use. All other approaches seem to be very (too) complex. It is somehow strange that something as universal and simple like a family tree is so hard to maintain/create with a database.

Re: [sqlite] Db design question (so. like a tree)

Jay A. Kreibich wrote: > > You can't with just SQL. This is the whole issue with adjacency lists. > Most basic operations, like finding ancestor lists, counting tree depths, > finding a list of all children or descendants, etc., require some > kind of loop. > > I have posted about

Re: [sqlite] Db design question (so. like a tree)

On Wed, Jun 03, 2009 at 09:11:23PM +0200, Jan scratched on the wall: > I thought about the adjacency lists. The columns would basically look > like this I guess: > > animal_id (PK), animal_id (father), aninmal_id (mother) > > Since I cant do a loop in sql how could I build a trigger securing,

Re: [sqlite] Db design question (so. like a tree)

abstraction from a traditional adjacency list. It is more flexible, but it is also more complex. -j > --- On Wed, 6/3/09, Jay A. Kreibich <j...@kreibi.ch> wrote: > > > From: Jay A. Kreibich <j...@kreibi.ch> > Subject: Re: [sqlite] Db design question (so. like a tree) &g

Re: [sqlite] Db design question (so. like a tree)

I thought about the adjacency lists. The columns would basically look like this I guess: animal_id (PK), animal_id (father), aninmal_id (mother) Since I cant do a loop in sql how could I build a trigger securing, that no child is e.g a father of it's own father (or grand-father and so on)? To

Re: [sqlite] Db design question (so. like a tree)

), (E:G) and if you wanted to get a little insane, (A:D), etc. --- On Wed, 6/3/09, Jay A. Kreibich <j...@kreibi.ch> wrote: From: Jay A. Kreibich <j...@kreibi.ch> Subject: Re: [sqlite] Db design question (so. like a tree) To: "General Discussion of SQLite Database" <sqli

Re: [sqlite] Db design question (so. like a tree)

On Wed, Jun 03, 2009 at 12:25:14AM +0200, Jan scratched on the wall: > Hi, > > If you don't want to update, but you do want to query for entire > > subtrees, do give nested sets more consideration. > > But as Jay pointed out: Nested sets only work with one parent. Do they? You can think of

Re: [sqlite] Db design question (so. like a tree)

Hi, thanks. I'll investigate this possibility. John Stanton schrieb: > Something to investigate is to use an AVL tree structure with rowids as > the pointers. It would stay balanced and you could present family trees > quite simply as well as use SQL to extract data on individuals and sets >

Re: [sqlite] Db design question (so. like a tree)

Hi, > If you don't want to update, but you do want to query for entire > subtrees, do give nested sets more consideration. But as Jay pointed out: Nested sets only work with one parent. Do they? > > The best encoding for intervals I've yet seen is here: > http://arxiv.org/pdf/0806.3115v1 > >

Re: [sqlite] Db design question (so. like a tree)

Something to investigate is to use an AVL tree structure with rowids as the pointers. It would stay balanced and you could present family trees quite simply as well as use SQL to extract data on individuals and sets of individuals. Jay A. Kreibich wrote: > On Tue, Jun 02, 2009 at 11:16:20PM

Re: [sqlite] Db design question (so. like a tree)

On Tue, Jun 2, 2009 at 10:45 PM, Jan wrote: > Sounds good. I think I try that. Although updating is usually not > necessary (once you have a mother/father its usually difficult to get > rid of/update them .-) I read that there is problem with queries that go > deeper in

Re: [sqlite] Db design question (so. like a tree)

>> - adjacency list (not very difficult to understand) > > Also easy to work with two parents, just have a "father" column and a > "mother" column. > > Adjacency lists are quick to update, but many queries can't be done > in (standard) SQL by itself. That usually isn't a problem, and

Re: [sqlite] Db design question (so. like a tree)

On Tue, Jun 02, 2009 at 11:16:20PM +0200, Jan scratched on the wall: > Hi, > > I am planning a database for animal breeding. I need to store the > relations between individuals and therefore I have to build something > like a tree structure. But of course with two parents (There wont be >

[sqlite] Db design question (so. like a tree)

Hi, I am planning a database for animal breeding. I need to store the relations between individuals and therefore I have to build something like a tree structure. But of course with two parents (There wont be cloned animals in the database .-) afaik) I read a little bit about - adjacency