From: Don A. <don...@co...> - 2005-03-17 17:07:19
|
On Thu, 2005-03-17 at 15:48 +0100, Martin Hawlisch wrote: > Hi Don, > > as Database design is one of my main parts at work I can help you ;-) > > Traditional databases are relational database, therefore you specify how > objects are related, by using foreign keys. > > The Family<->Person relation is a typical n-to-m relation for which a > mapping table is used. > > One table holds the persons, each person is identified by a primary key. > The second table holds the family data, identified by a PK. > > The maping is done by using a third table that only consists of two columns > that contain the primary keys of the person and the related family. > In our case a third coulumn can be used so specify the kind of relation > (Child/Father/Mother); Another possibility is to use one mapping table for > each kind of relation. > > Some databases support nested tables, but they are not used in the majority > of cases. > > Cheers, Martin. > > P.S. /join #gramps in IRC > I should probably point out that GRAMPS doesn't use a relational database scheme. It is probably more closely related to an Object Oriented database, similar to Zope. This whole issue came up because Alex asked me for my thoughts on the Witness discussion that you and he had on IRC (sorry I missed it), so I thought I needed to learn more about the SQL approach and traditional database design - basically, I didn't have a good answer, so I thought I needed to learn more. Right now, GRAMPS is using BSDDB as a means of *fast* persistent storage. Each Primary Object (Person, Family, Place, Media, Source, and Event) are stored in unique databases (roughly the BSDDB equivalent of SQL tables) within the GRDB file. Each "table" is simply a key,value pair, where value is a "pickled" python tuple representing the data contained by the Primary Object. All Secondary Objects (Names, Addresses, Attributes, etc.) are embedded within the pickled primary objects. This makes is very easy to store lists in the structures, eliminating the need for the SQL relationship tables. There are a few "secondary index" tables (one of the few database-like features provided by BSDDB) that are used tie some relationships back, but not to the extent of SQL. I'm a bit reluctant to add more, because we have to emulate these features to maintain compatibility with the XML and GEDCOM formats. I think I'm getting the idea of what goes on with real relational databases. And boy, is it different from the object oriented approach :-) This will help. Hopefully it will help me make sure that we don't make any decisions that will prevent a MySQL backend. And maybe I'll understand the Witness question better :-) This has been a good education for a non-database person (I'm actually a VLSI circuit designer). I'm on vacation this week, so I haven't been around a computer much at all. The best I can do is drop an email here and there. I'll be back on IRC starting Sunday. Thanks! Hopefully the discussion will continue. I'm learning a lot. Don -- Don Allingham <don...@co...> |