Re: [Gramps-devel] DB tables

[Jochen T. <jo...@re...>]

On Thu, Mar 17, 2005 at 07:33:01AM -0700, Don Allingham wrote:
> I'll be the first to admit that I am not a database expert. So, when an
> issue on person/witness relationships came it mind, I thought that I
> would try to see how a traditional SQL database would address the
> problem. I didn't get too far due to a basic misunderstanding of SQL
> databases.
> 
> So, I was hoping someone could give me some insight.
> 
> In traditional data structures or object orient design, I can use lists
> with an object to keep track of things. For example, a Person has a list
> of the families with which it is associated. A Family has a list of
> people that belong to it.
> 
> Person -> many Family instances
> Family -> many Person instances
> 
> However, SQL doesn't seem to have lists. How would you represent the
> above? How would you represent that a Person can be in many families and
> a Family can contain many Persons?

You need three tables:

  +---------+       +--------------+       +--------+
  | Person  |       | is_member_of |       | Family |
  +---------+       +--------------+       +--------+
  | ID      |<------| PersonID     |   +-->| ID     |
  | Name    |       | FamilyID     |---+   | ...    |
  | ...     |       +--------------+       +--------+
  +---------+

The Arrows are foreign key constraints.

In the case of GRAMPS I am not sure whether you actually need the
Family-Table though, because a family in and of itself doesn't really
exist. A family is just a thing created out of the relationships
between people. The family itself doesn't have any attributes. So it
would be enough to have a Link to the father and mother for everybody
and to create the family on the fly from all the people who have the
same father and mother.

Jochen
-- 
Jochen Topf  jo...@re...  http://www.remote.org/jochen/  +49-721-388298