Implementing hierarchy in Access tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am developing an Access DBMS to manage local history information,
artifacts, photos, etc. A crucial part of this is the people referred to AND
the realtionships among them. Within the tblPerson one person could be a
spouse of one or more persons, each person could be the child of a couple, a
couple could have one or more children. I am trying to show how the tblPerson
relates to itself in ways to accommodate these relationships, but none of my
attempts are working as I know they should.
This is similar to an employee table in a company where each employee might
be the supervisor of other employees and each employee might be supervised by
another employee.

Any tips would be appreciated.
 
Brock

One approach might be to create a "relationship" table, with fields like:

trelRelationship
Person1ID
Person2ID
RelationshipID

and a lookup table of relationships, with rows like:

RelationshipID Relationship
1 Spouse
2 Great-aunt, four-times removed
3 ...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
If you are using an autonumber field as primary key add another field named
Relative - integer. In the Relationship view put your table twice (Access
adds a sufix of "_1" to the second instance of the table). Set a releation
from primary key field in the first to the Relative field in the second.
Select referential integerity and cascade updates. Do not select cascade
deletes unless you know all about it.
 
you link them using a table something like thus:
id_link autonumber
id_person1 number
id_person2 number
relation Text or a lookup

You need two link records for each relation, e.g. one for Father relation
and the other, with id's swapped, for son relation
 
Karl:
I am still having difficulties with the concept here. Each child is related
to a mother and a father (potentially). Does this require another instance of
the Person table?

With respect to spousal relationships, would you advise yet another Spouse
field and another instance of the basic table?

My first approach was to consider a child as being related to a MARRIAGE,
but that proved to be awkward.
 
persons
1 Jack
2 Jill
3 Jack Junior


Relationships
66 (Key )
3
1
Father
-----
67
1
3
Son
-----
68
2
3
Mother
----
69
3
2
Son
 
Brock said:
This is similar to an employee table in a company where each employee might
be the supervisor of other employees and each employee might be supervised by
another employee.
Click to expand...

You are alluding to the 'adjacency list' approach to modelling a
hierarchy. Other models are available and may be better suited e.g.
nested sets. The subject matter is enough to fill a whole book.
Luckily, someone has written such a book: 'Trees and Hierarchies in SQL
for Smarties' by Joe Celko. For a taste, see
http://www.dbazine.com/ofinterest/oi-articles/celko24.

Jamie.

--
 
David said:
Relationships
66 (Key )
3
1
Father
-----
67
1
3
Son
-----
Click to expand...

This doesn't look right. Are you advocating having two rows to model
the same father/son relationship? How would you write constraints to
ensure that both rows are present? How would you prevent duplicate
father/son pairs if you are using a sequential number as a key?

Jamie.

--
 
It is "right" in that it will work, and it might make it easier for a
beginner to understand and write the queries they require.

By all means tell us which way you consider "Best".

I would not write constraints to ensure both sets are present. I would
write queries to make it so.

I believe it is possible to index on a composite key id_person1 & id_person2
and set no duplicates allowed.

But you have got me again, I would not do it that way. However I am not sure
that the way I would do it would be the best way either, and I am sure that
it would confuse a beginner.
I come on here to share and to learn. Sometimes it will mean I exit
"smarting but smarter".
 
David said:
By all means tell us which way you consider "Best".
Click to expand...

If you re-read the OP, it should be apparent that a family tree is
required. Modelling trees in SQL is not intuitive, IMO, hence why I
recommended a book.

I'd suggest the nested sets approach because the constraints are easier
to define and in the OP's case inserts should be infrequent e.g. once
defined, a family doesn't 'reorganize' itself significantly.

The OP was alluding to an adjacency list, essentially a denormalized
model against which it is difficult to define constraints (at least
Access/Jet, unlike most SQL products, can handle DRI actions within the
same table)...
I would not write constraints to ensure both sets are present. I would
write queries to make it so.
Click to expand...

That sounds pretty complicated: a DELETE to remove duplicates, an
UPDATE to make corrections and an INSERT to plug holes, possibly
multiples of each command type. Wouldn't it be easier to write
constraints to stop bad data happening in the first place?
I come on here to share and to learn. Sometimes it will mean I exit
"smarting but smarter".
Click to expand...

Sincerely, that's a great attitude. You shouldn't feel bad, of course:
two other people posted the same (IMO flawed) approach; I replied to
yours because you provided the most info. We all make mistakes e.g. see
my original post where I mention a 'hierarchy' (wrong kind of 'family',
I must be watching too much Sopranos <g>).

Jamie.

--
 
Brock Vodden said:
Karl:
I am still having difficulties with the concept here. Each child is
related
to a mother and a father (potentially). Does this require another instance
of
the Person table?
Click to expand...

You can insert multiple copies of the same table in a Query, joining them
either directly or via junction/intersection tables (as has been suggested).
You do not need multiple physical copies of the table, if that is what you
are asking.

A commonly-used example is an Employee Table where each employee has a
"foreign key" to another record in the Table representing that employee's
manager. You could have a Query that links employees to levels of
management, and the Query would have as many instances of the Employee Table
as you have levels of management.

Larry Linson
Microsoft Access MVP
 
I don't see the original posts in this thread so don't know if the
original issue was genealogy or parental relationships. Genealogy can
safely infer Ma and Pa if you ignore the possibility of sperm banks or
various fertilization methods wherein you may not be able to determine
genealogy strictly.

However, for parental relationships, a forward looking application
will have to handle the various possible roles accurately. There will
be the need for a new tblParent on the many side of a relationship and
tblPerson on the one side. There could be family groups with only
females as parents an others with only males as parents. Who is to
say that there mightn't someday be legal polyandrous family
relationships? Each record in tblParent would only need to have the
Primary Key of that parent but it could also have parental role
information. Gender info is available in tblPerson as before.

This isn't social commentary only a recognition that the application
must meet the needs of the user.

HTH
 
Larry said:
A commonly-used example is an Employee Table where each employee has a
"foreign key" to another record in the Table representing that employee's
manager. You could have a Query that links employees to levels of
management, and the Query would have as many instances of the Employee Table
as you have levels of management.
Click to expand...

Thanks for this explanation. In doing so, you've revealed one of the
limitations of the adjacency list model i.e. the number of recursions
(number of times you must self-join to the table) required to traverse
the tree must be defined in advance and therefore must either be known
or guessed, making queries difficult to write, even if you resport to
(yuk!) dynamic SQL.

While the number of levels predictable in a single company, it is
probably not known for a given family tree and guessing (eight living
generations?) could be costly at runtime. With the nested sets model,
traversal is more 'data-driven'.

Jamie.

--
 
How does the nested set model handle the case of disjointed relationships?
e.g. a bit of research shows that John Nolan is a nephew of James Peabody,
but there is no other information linking them? i.e no known siblings of
James, no parental information on John.
 
David said:
How does the nested set model handle the case of disjointed relationships?
e.g. a bit of research shows that John Nolan is a nephew of James Peabody,
but there is no other information linking them? i.e no known siblings of
James, no parental information on John.
Click to expand...

That's an issue more fundamental than the choice of model! A node with
no parent is the top of the tree, isn't it?

You may have mistaken me for an expert in modelling trees in SQL <g>.
Rather, I'm the guy who thinks it's a big topic and suggested the OP
read a book rather than expect a brief outline of an employee org chart
to provide a solution.

Jamie.

--
 
Back
Top