How do you model a family?

[Peter T. Keillor III]

I'm not talking "Days of our Lives", only church stuff. What's the
most efficient way to show relations among members, have a member
table, then a relations table (member 1, member 2, relation)? Or is
there a better way?

Another need might be church activities of members. I guess that
could be activity table and a member -- activity table.

Thanks.

Pete Keillor

[Darryl Kerkeslager]

Assuming a person can only be a member of one family:

person is an entity.
family is an entity.
fam_name is the descriptor ("The Bob Smith Family")
per_family_id is a foreign key in person table
per_family_role is a descriptor of their role

Now, if you want to go "Days of Our Lives", you need a bridge table between
person and family, with family_id and person_id both being foreign keys in
table per_fam_table.
Darryl Kerkeslager
"Peter T. Keillor III" <ke*********@ch artermi.net> wrote:

I'm not talking "Days of our Lives", only church stuff. What's the
most efficient way to show relations among members, have a member
table, then a relations table (member 1, member 2, relation)? Or is
there a better way?

[Alan Webb]

Peter,
Two tables, person & relation. Person has anything related to an individual
like name, etc. Relation has three columns, person, related person and
relationship. Same schema, in essence, as you would find in a
bill-of-materials or company organization chart.

"Peter T. Keillor III" <ke*********@ch artermi.net> wrote in message
news:f4******** *************** *********@4ax.c om...

I'm not talking "Days of our Lives", only church stuff. What's the
most efficient way to show relations among members, have a member
table, then a relations table (member 1, member 2, relation)? Or is
there a better way?

Another need might be church activities of members. I guess that
could be activity table and a member -- activity table.

Thanks.

Pete Keillor

[Allen Browne]

Hi Peter. This question is somewhat bigger than it sounds.

Presumably you already have a table of individuals, with a PersonID as
primary key.

The simplest approach is just to identify a grouping of some kind (e.g. a
household):
GroupID Autonumber primary key
GroupName Text e.g. "The Smith family"
and then have a related table to identify the members of this group:
GroupID Number foreign key to tblGroup.GroupI D
PersonID Number foreign key to tblPerson.Perso nID

With that approach, a person can be a member of 2 households (e.g. half time
with Dad, and half with Mum). The tables are often named Household and
HouseholdDetail , but the concept works exactly the same for the members of a
golf club, the members of a committee, and so on. You may want another field
in the GroupDetail table to specify the person's main role in the group,
e.g. mother, secretary, ...

The weakness of that approach is that it gives you no information about
extented families. A person is a member of a group or not. It also doesn't
tell you details of the relationships, e.g. your son, step-son, and
foster-son are all just members of the same family, so you cannot determine
the biological relationships between them. If you need to go that way, then
the approach you suggested starts to come into the picture. It does start to
get messy, though trying to define the web of relationships should be
implied through the existing relationships.

The table you suggested - fields Member1, Member2, Role - works well for
directional relations, e.g. Alex is the father of Bill. It does not work
well for reflexive relationships where Mary is the sister of Martha, and so
you want to find out if Matha is the sister of Mary, you don't know whether
to look for Matha in the Member1 field or the Member2 field.

Ultimately it comes down to finding the minimal complexity that meets your
actual needs. In some scenarios, you actually want the corporate entities
(households, committees, schools, organizations, businesses) in the Client
table, along with the individuals because it gives you ease of searching and
makes it simple to accept donations and write out receipts for both
individuals and corporate entities. In this case, the groups will be in the
Client table, and the main Group table will have a foreign key to the Client
table, and so will the GroupDetail table. This allows you to specify the
household in the Group table, and the members of the household in the
GroupDetail table. But if you make the foreign key in the Group table
optional (not required, and yes, that's possible even with Referential
Integrity enforced), you can also create ad hoc reflexive groupings of
people with a Null foreign key in the Group table and the members of the
grouping in the GroupDetail table. I think that's about as flexible as it
gets without getting complex.

If other people have different suggestions, we'd love to hear them.

(BTW, don't use Group as a table name. It's a reserved word in SQL. Perhaps
tblGroup.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Peter T. Keillor III" <ke*********@ch artermi.net> wrote in message
news:f4******** *************** *********@4ax.c om...

I'm not talking "Days of our Lives", only church stuff. What's the
most efficient way to show relations among members, have a member
table, then a relations table (member 1, member 2, relation)? Or is
there a better way?

Another need might be church activities of members. I guess that
could be activity table and a member -- activity table.

Thanks.

Pete Keillor

[Steve Jorgensen]

I agree with what you're saying for most applications, but I'm not sure its
best for this one. For purposes of this application, I think we can say that
everyone in a family belongs to one family and no other, and that we don't
need a traceable model of the nature of people's individual
inter-relationships. The extra complexity involved in making a more "correct"
schema is probably not justified.

With that simplifying assumption, we can simply have a family table and a
person table, with each person record having a family id and a family member
type which can be free form text like parent, child, grandson which will be
reasonable to the application user in context.

Even in cases where we would need to express the relations more directly,
there are problems with your suggested schema, but that's not to berate it
because there are equally bad or worse problems with the alternatives as I'll
get to below.

The main problem with your suggested scheme is that it assumes that a
relationship only goes in one direction, from the first person, to the second.
Nevertheless, it's less problematic than the more technically correct model in
which each relationship is actually a collection containing 2 members. That
schema would require 3 records (in addition to the person records) to describe
a single relationship. That's a bit much, though it does have the possibly
dubious benefit of being able to model a polygamous marriage (one relation
involving >2 people, not represented via a common relation to another single
person).

Since I'm agreeing with your schema (regardless of its problems) for most
cases that need any more rigor that what this app seems to need, here's how I
like to work around those problems. When you do a query of relatives, you
actually need a UNION of 2 selects of the relation table, one from the first
relation id, and one from the second. You also need 2 relation type names in
the relation types table, one for the forward direction, and one for the
reverse (e.g. RelationNameFor ward="Child", RelationNameRev erse="Parent"), and
query from the appropriate type name column depending on the direction the
SELECT statement is going.

This solution leads to a new UI problem, though. You can't directly edit data
via a UNION query, so you need to use unbound forms, or some sort of
self-linked subform trickery to edit the data via MS Access.

On Sat, 13 Nov 2004 21:57:47 -0500, "Alan Webb" <kn*****@hotmai l.com> wrote:

Peter,
Two tables, person & relation. Person has anything related to an individual
like name, etc. Relation has three columns, person, related person and
relationship . Same schema, in essence, as you would find in a
bill-of-materials or company organization chart.

"Peter T. Keillor III" <ke*********@ch artermi.net> wrote in message
news:f4******* *************** **********@4ax. com...

I'm not talking "Days of our Lives", only church stuff. What's the
most efficient way to show relations among members, have a member
table, then a relations table (member 1, member 2, relation)? Or is
there a better way?

Another need might be church activities of members. I guess that
could be activity table and a member -- activity table.

Thanks.

Pete Keillor

[(Pete Cresswell)]

RE/

What's the
most efficient way to show relations among members, have a member
table, then a relations table (member 1, member 2, relation)? Or is
there a better way?

I don't have any experience, but this sounds huge to me.

How about going to the Mormon church to see what they're doing? They're really
big on geneologies and, by implication, families.
--
PeteCresswell

[Allen Browne]

It's harder to explain that to demo, so have just uploaded a basic demo of
this kind of thing at:
http://allenbrowne.com/human.zip

40KB zip file.
Access 2000 or later.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Allen Browne" <Al*********@Se eSig.Invalid> wrote in message
news:41******** *************** @per-qv1-newsreader-01.iinet.net.au ...

Hi Peter. This question is somewhat bigger than it sounds.

Presumably you already have a table of individuals, with a PersonID as
primary key.

The simplest approach is just to identify a grouping of some kind (e.g. a
household):
GroupID Autonumber primary key
GroupName Text e.g. "The Smith family"
and then have a related table to identify the members of this group:
GroupID Number foreign key to tblGroup.GroupI D
PersonID Number foreign key to tblPerson.Perso nID

With that approach, a person can be a member of 2 households (e.g. half
time with Dad, and half with Mum). The tables are often named Household
and HouseholdDetail , but the concept works exactly the same for the
members of a golf club, the members of a committee, and so on. You may
want another field in the GroupDetail table to specify the person's main
role in the group, e.g. mother, secretary, ...

The weakness of that approach is that it gives you no information about
extented families. A person is a member of a group or not. It also doesn't
tell you details of the relationships, e.g. your son, step-son, and
foster-son are all just members of the same family, so you cannot
determine the biological relationships between them. If you need to go
that way, then the approach you suggested starts to come into the picture.
It does start to get messy, though trying to define the web of
relationships should be implied through the existing relationships.

The table you suggested - fields Member1, Member2, Role - works well for
directional relations, e.g. Alex is the father of Bill. It does not work
well for reflexive relationships where Mary is the sister of Martha, and
so you want to find out if Matha is the sister of Mary, you don't know
whether to look for Matha in the Member1 field or the Member2 field.

Ultimately it comes down to finding the minimal complexity that meets your
actual needs. In some scenarios, you actually want the corporate entities
(households, committees, schools, organizations, businesses) in the Client
table, along with the individuals because it gives you ease of searching
and makes it simple to accept donations and write out receipts for both
individuals and corporate entities. In this case, the groups will be in
the Client table, and the main Group table will have a foreign key to the
Client table, and so will the GroupDetail table. This allows you to
specify the household in the Group table, and the members of the household
in the GroupDetail table. But if you make the foreign key in the Group
table optional (not required, and yes, that's possible even with
Referential Integrity enforced), you can also create ad hoc reflexive
groupings of people with a Null foreign key in the Group table and the
members of the grouping in the GroupDetail table. I think that's about as
flexible as it gets without getting complex.

If other people have different suggestions, we'd love to hear them.

(BTW, don't use Group as a table name. It's a reserved word in SQL.
Perhaps tblGroup.)
"Peter T. Keillor III" <ke*********@ch artermi.net> wrote in message
news:f4******** *************** *********@4ax.c om...

I'm not talking "Days of our Lives", only church stuff. What's the
most efficient way to show relations among members, have a member
table, then a relations table (member 1, member 2, relation)? Or is
there a better way?

Another need might be church activities of members. I guess that
could be activity table and a member -- activity table.

Thanks.

Pete Keillor

[Bob Quintal]

>

"Peter T. Keillor III" <ke*********@ch artermi.net> wrote:

I'm not talking "Days of our Lives", only church stuff.
What's the most efficient way to show relations among
members, have a member table, then a relations table (member
1, member 2, relation)? Or is there a better way?

The cleanest way to do this is to model marriages as one table
and children of that union as another.

Unions has each partner's personID as a column, plus the date of
union and the date of dissolution.

Children has the unionID and the child's personID.

Each person's address is separate, which handles childrea away at
school, etc.A little code allows to copy from a parent's address
to a child's or spouse's

Relationships to grandparents, second cousins and such can easily
be deduced from the parent-child relationships.

--
Bob Quintal

PA is y I've altered my email address.

[Penguin]

Assuming frmPeople shows data from tblPeople, and has
frmsubRelations hips, with another PeopleID and RelationshipTyp eID.

Person1
has the following:
sister Person2
friend Person3
parent Person4
child Person5

When you want to add a relationship to Person1, and the Person2 is
new, the not-in-list event opens a pop-up to enter the new person,
Person2.

I find that if I try to enter the reciprocal relationship (Person2 is
related to Person1 as well) the entire database closes, but only on
the second person I enter. I've tried adding saves all other the
place. Could the problem be as simple as opening tblPeople too many
times? Would a recordset clone do anything better?

[Allen Browne]

You are experiencing a crash? i.e. "This application has been closed by
Windows, sorry for the inconvenience." ?

We would need to know a bit more to pin-point the cause.
Name AutoCorrect is a possible culprit:
http://members.iinet.net.au/~allenbrowne/bug-03.html

If this is Access 2002 or 2003, and a subform is involved, try adding a text
box to the subform for the foreign key field.

Any subqueries in the SQL statement you use to try to create the reciprocal
relation?

If none of those help, see:
Preventing Corruption
at:
http://members.iinet.net.au/~allenbrowne/ser-25.html

If you are still stuck, please tell us the version of your msaccess.exe file
(typically in c:\program files\microsoft office\office), and also
msjet40.dll (typically in windows\system3 2).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Penguin" <pe***********@ aol.com> wrote in message
news:2a******** *************** **@posting.goog le.com...

Assuming frmPeople shows data from tblPeople, and has
frmsubRelations hips, with another PeopleID and RelationshipTyp eID.

Person1
has the following:
sister Person2
friend Person3
parent Person4
child Person5

When you want to add a relationship to Person1, and the Person2 is
new, the not-in-list event opens a pop-up to enter the new person,
Person2.

I find that if I try to enter the reciprocal relationship (Person2 is
related to Person1 as well) the entire database closes, but only on
the second person I enter. I've tried adding saves all other the
place. Could the problem be as simple as opening tblPeople too many
times? Would a recordset clone do anything better?