Geneology like Relationship Structure

[qqcoisa]

Greetings,

I want to create a database with data stored in a directory/geneology kind of structure. I was thinking in a solution like this:

Table A (and sample data):
Field ID_Person ( 1 , 2 , 3 , 4 , 5 )
Field Person (mary , jane , carl , bob , carmen)

Table B (and sample data):
Field Parent ( 1 , 1 , 1 , 4 )
Field Children ( 2 , 3 , 4 , 5 )

Table C (and sample data):
Field Car (1)
Field Licence Plate (12-34-AB)
Field Owner (3)

This tables let me know everything I want. I know mary is the mother of jane, carl, and bob. And that bob is the father of carmen. So, for each person I can easly see their children, and parents (if instead of relating ID_Person with Parent, I relate ID_Person with Children)

I'm using access and when I create the relationship ID_Person to Parent, I go to table A and by pressing the "plus button" all is fine:

Table A
- 1 mary
2
3
4
+ 2 jane
+ 3 carl
+ 4 bob
+ 5 carmen

However, I want to see the names of the children of mary, and also their own children if any, like this:

Table A
- 1 mary
+ 2 jane
+ 3 carl
- 4 bob
+ 5 carmen
+ 2 jane
+ 3 carl
+ 4 bob
+ 5 carmen

But when I add the relationship ID_Person to children it stops working.

If I add to table A the field "ID_Person2" with the same values of "ID_Person"
and then create the relationships: "ID_Person to parent" and "ID person2 to children (now primary key)", I get the above structure as I itended.

But having two fields with the same value is very very ugly, so I would like to avoid that, but dont know how. Furthermore, I can't make children primary because a children has two parents, therefore, colum children will hold duplicate values).

Thanks Beforehand,
Pedro Vaz

(if it changes anything, please assume a children may have any number of parents. I want to adapt this structure to other contexts without parents=2 restriction)

 

[neileg]

I have an Access database that I use for cat pedigrees. I have to track parents and offspring. I manage this is using only one table, like this:

tblCats
CatID PK Autonumber
CatName
Gender
FatherID Link to CatID of father
MotherID Link to CatID of mother
etc.

So if I want to find the offspring of a cat, I query either the FatherID or MotherID field (depending on gender of the cat) to look for matches on CatID. The query requires tblCats to be joined to itself for this to work. Does this help?

 

[KKilfoil]

There's nothing wrong with what you are doing, but...

It looks like you are using the '+' (subdata) available in table view.

Yes, this happens to show you one direction of your many-to-many relationship between child and parent when viewing table A, but Access merely picks the 'most likely' relationship, and only allows you one such relationship.

But, in general terms, you should not rely on Table view for any serious work on data in Access. The preferred method is to create one or more forms (or reports for hardcopy), relying on queries to provide the data 'crunched' to your desired output.

You probably want two separate forms/subforms, one with the 'children' on the main form, and one with 'parents' on the main form, with the other end of the relationship in the subform. You CAN actually use the same query for both forms, but I personally prefer a spearate query for each form so make later tweaking easier.

 

[The_Doc_Man]

For the wide-open, free-wheeling general case where ANY relationship is possible, you might at least CONSIDER this...

Table Person
PersID, pk, autonumber
info about person incl. date of birth, date of death if applicable, etc.

Table PersRel
PersAID, fk, long
HowRelated, code (see discussion below)
PersBID, fk, long
RelStart, date (optional, see discussion later)
RelEnd, date (optional, see discussion later)

Table RelCode
RelID, pk, autonumber
RelName, text of appropriate length, name of relationship
RevRelName, text of appropriate length, name of relationship in other direction

OK, you enter data in this puppy through two forms. (Or one multi-function form with a LOT of programming.)

Define person data. Don't worry about relationships yet. Put in the Person table. Fill it in. Next, define the relationships you will track. Put the known relationships in the RELCODE table. Define a "forward" name and a "reverse" name for each relationship. See later for explanation of forward/reverse names of relationships.

NOW build a relationship in which you add the Person table to the relationship window TWICE. The second time you add the table you will see Person(2) for the table name. Don't worry, it's the same table, just a second REFERENCE to the table. Build a relationship, one to many, for PersRel to RelCode, one to many, so that you can use the RelCode table as drop-down lookup for relations. Link the first Person table entry to the PersAID. Link the second Person table entry to the PersBID.

Now, you have all you need to build a form in which you can have a relationship definition. You can build a drop-down for PersonA and another one for PersonB. Write the relationships so that they are directional. That is, make the form say

Person [xxxxxxxx[v] is the [yyyyyyyyy[v] of person [zzzzzzzzz[v]

(where the [xxxx[v] is my symbolic representation of a drop-down box on a form to give you a pick-list.)

What good does this do you? Why, bless you, it gives you everything you need to find any and all relationships you care to define.

You can build a UNION query on the PersRel table so that you can query the entire table for relationships in which a person is in the PersA slot UNION relationships in which a person is in the PersB slot. (Read up on union queries.) The "RevRelName" is to minimize data entry... It might look SOMETHING like

Select PersAID as PersID, RelName as RelDescr, PersBID as OtherID, etc. from PersRel JOIN RelCode On HowRelated = RelID Where ....;
UNION
Select PersBID as PersID, RevRelName as RelDescr, PersAID as OtherID, etc. from PersRel JOIN RelCode On HowRelated = RelID Where ....;

(Don't hold me to this exactly, you might have to play with it.)

NOW you can base a query on this union query to find everyone related to a particular person. In this query, the fields are PersID, RelDescr, OtherID (plus if you kept dates, whatever you named the dates.) AND the RelDescr has already been reversed. Now you can do lookups on the PersID and OtherID to get their names from the Person table.

The "reverse name" is there because if PersA is [the child] of PersB, reversing that says PersB is [the parent] of PersA. So in the RelCode table, RelName is "child" and RevRelName is "parent". Sometimes, they are symmetric, e.g. "sibling" on both sides, or "spouse" on both sides. Sometimes they don't have to be.

The worst part of this would be including the names and such - might take a couple of lookups to get it right. But the actual PersRel table is very small. If you don't include the dates, you only need three number fields, two of which must be Long. The HowRelated code could be Integer or Long, take your pick.

This is a case where you won't necessarily have a prime key for the linking table (PersRel) because you would expect that every name and every kind of relationship could occur more than once. A person can have more than one child. A child has two parents (and a technically unlimited number of step-parents). A child can have many siblings - which means that when you turn it around, that child is the sibling of many other children. So none of the fields in PersRel can be prime keys unless you make the COMBINATION of PersAID, PersBID, and HowRelated as a three-part prime key. Authorities differ on whether it is required - or wise - to force the prime key issue on a linking table. Access does not require it.

I think this design minimizes total required table space. Because the relationship table uses only ID numbers rather than text names, it can be small (= faster to search). Even adding dates for transitory relationships doesn't add THAT much size to the table. But those dates make it possible to track people through multiple marriages if necessary. Even REmarriages to the SAME people after a divorce and subsequent reconciliation.