Correct table structure for people/children

As I've worked with filemaker over the past 15 years, slowly getting a
little more sophisticated, I've always run up against one issue I
can't untangle. If I'm building a database that will have families,
with children, parents, possibly grandparents, what is the proper
structure table-wise. I've always defaulted to using a Parents record
with fields for both spouses (PersonA and PersonB), and then a
separate Children table.

But now I'm working with a situation where children grow up and become
parents themselves (horrors!) and so I've constructed a single
"People" table with a self-join where each record has a Parents'
foreign key, and one record might be a child's record (just PersonA)
or a couple linked to a child. It still is awkward though,
particularly if I need to assign attributes to both of those parents
individually, and I know I should probably have a table where every
individual has an individual record. And then use join tables. Would
the join table be 'Family'? Linked to an 'Address' table? And then
how do I handle a divorce? Or linking and unlinking people to the
address table? Is there a common practice?

For what it's worth, this particular database is about 1700 records,
of which maybe 300 are actively used (it's for a school, with alumni
etc.). The system is all Macintosh, and uses FM 8.5 and FM 8.5
server. I work with FM 8.5 advanced.

TIA for any help with what's obviously a novice's question...

Lanse

Re: Correct table structure for people/children

On 2007-09-08 10:40:46 -0700, lansingoogle said:

> But now I'm working with a situation where children grow up and become
> parents themselves (horrors!) and so I've constructed a single
> "People" table with a self-join where each record has a Parents'
> foreign key, and one record might be a child's record (just PersonA)
> or a couple linked to a child. It still is awkward though,
> particularly if I need to assign attributes to both of those parents
> individually, and I know I should probably have a table where every
> individual has an individual record. And then use join tables. Would
> the join table be 'Family'? Linked to an 'Address' table? And then
> how do I handle a divorce? Or linking and unlinking people to the
> address table? Is there a common practice?

Not really standard, as when you're dealing with modern families things
can get sticky.

I'd start with a Family table, with a FamilyID. Then there's a People
table. Everyone goes in there and gets a PeopleID and a FamilyID.
(don't confuse me about what happens if two of your families parent's
marry and you get a Yours, Mine & Ours situation) You'll also need an
Address table, as each biological parent of a child may need to get
separate mailings or notices. Or, heaven help you, if the school
patronage is wealthy enough, some or all of these people may have
multiple or seasonal residences.

Then you need a parent-child-step join table, a spouse (current &
former) join table, and a person-address join table to show all the
different places you have to mail for any one person. Clear as mud,
isn't it?

This gives you the ability to link parents with grandparents,
step-grandparents AND kids, AND stepkids AND divorced spouses, while
looking ONE place to have addresses. It should let you filter out
multiple mailings to one address as well.

In the confusing case of needing to join two families already in the
db, I'd have some Merge mechanism where everyone involved gets a new
FamilyID, while preserving the former FamilyID for backwards
compatibility.

If you get a real soap opera where two families switch spouses, tell
them they have to leave the school, as your db will have a nervous
breakdown. ;)
--
Lynn Allen
--
www.semiotics.com
562.938.7890
Member Filemaker Business Alliance
Long Beach, CA

Re: Correct table structure for people/children

lansingoogle wrote:

> If I'm building a database that will have families,
> with children, parents, possibly grandparents, what is the proper
> structure table-wise. I've always defaulted to using a Parents record
> with fields for both spouses (PersonA and PersonB), and then a
> separate Children table.
>
> But now I'm working with a situation where children grow up and become
> parents themselves (horrors!) and so I've constructed a single
> "People" table with a self-join where each record has a Parents'
> foreign key, and one record might be a child's record (just PersonA)
> or a couple linked to a child. It still is awkward though,
> particularly if I need to assign attributes to both of those parents
> individually, and I know I should probably have a table where every
> individual has an individual record. And then use join tables. Would
> the join table be 'Family'? Linked to an 'Address' table? And then
> how do I handle a divorce? Or linking and unlinking people to the
> address table?

I'd use one address table and one cross table, put up a second instance
of the address table as AddressesRelated.

The cross table will have three fields:
Address:ID
AddressRelated:ID
Relation

Relation is a text field that describes how people are related.

Now you can create child records for every relation. You'll have to
create two records for every relation.

--
http://clk.ch

Re: Correct table structure for people/children

On Sep 9, 2:37 am, c...@freesurf.ch (Christoph Kaufmann) wrote:
> lansingoogle wrote:
> > If I'm building a database that will have families,
> > with children, parents, possibly grandparents, what is the proper
> > structure table-wise. I've always defaulted to using a Parents record
===clipped text===
> > individually, and I know I should probably have a table where every
> > individual has an individual record. And then use join tables. Would
> > the join table be 'Family'? Linked to an 'Address' table? And then
> > how do I handle a divorce? Or linking and unlinking people to the
> > address table?
>
> I'd use one address table and one cross table, put up a second instance
> of the address table as AddressesRelated.
>
> The cross table will have three fields:
> Address:ID
> AddressRelated:ID
> Relation
>
> Relation is a text field that describes how people are related.
>
> Now you can create child records for every relation. You'll have to
> create two records for every relation.
>
> --http://clk.ch

I'm not quite clear on the AddressesRelated part. Can you explain a
bit more? Does that mean you are describing the inter-person
relationship (e.g. child-to-parent) in a field in the Addresses
table? I liked Lynn's suggestions, but I'm not sure I follow how the
relationships (the human variety) are being recorded, if everyone in a
family is sharing a FamilyID, there's no telling who's the parent and
who's the child, is there?

thanks,
Lanse

Re: Correct table structure for people/children

lansingoogle wrote:

> I'm not quite clear on the AddressesRelated part. Can you explain a
> bit more? Does that mean you are describing the inter-person
> relationship (e.g. child-to-parent) in a field in the Addresses
> table?

Nope. All the related persons are child records in the cross table. You
can use a portal to show, edit and delete relations.

Before you read on, make sure you understand what how cross tables are
used. Now: one record in the cross table is one relation of a person.
The cross table is in the middle of two instances of the address table
and has two fields (like every other cross table):

The first field links the cross table to the address table. The second
field links the cross table to the 2nd instance of the address table,
which I call AddressesRelated. It's a sort of a self join.

You'll need to create 2 records for every relation, one per direction.
--
http://clk.ch

Re: Correct table structure for people/children

On 2007-09-09 13:46:57 -0700, lansingoogle said:

> I liked Lynn's suggestions, but I'm not sure I follow how the
> relationships (the human variety) are being recorded, if everyone in a
> family is sharing a FamilyID, there's no telling who's the parent and
> who's the child, is there?

Sorry I wasn't clear in my description.

In addition to a FamilyID, each record in the People table needs the
PersonID, of course. (this gets difficult because the nomenclature we
use to discuss db metastructure comes from families in the first place.
Is she talking about db terms or family terms? Use your own terms but
be VERY careful that they are clear as to directionality and
non-confusing to others)

In the Relationship join file, there are 2 RelationshipID fields, call
them LeftID and RightID. Even though FM7+ relationships do go both
ways, for the purposes of generational and marriage connections, we
need to have clarity of direction of the relationship in order to
determine who is the parent and who the child.

So a parent-child relationship join record would put the parent's
PersonID in the LeftID field, and the child's PersonID field in the
Right field. Then to fully characterize the record connected to the
RightID, you would have a TypeRight field, to include things like
Child, Spouse, Ex-Spouse, Step-child. To characterize the the record
connected to the LeftID, you'd have another TypeLeft field with Parent,
Spouse, Ex-Spouse, Step-Parent, Guardian, Nanny, Whatever. Both these
lists are non-exhaustive.

You might also have further classifications such as "Gets All
Notifications" "Do not Notify" "Can/Cannot pick up from School" "Watch
Out They're Nuts" "No fund-raising", or whatever you can think of.
Those should probably go in the People record.

So from any record in the People table, you should be able to see ALL
the connected other people, and their relationship to the record
currently being viewed. You should be able to filter the list of
connected people by Type up or down.

A single person can be child to another AND parent to a third at the
same time, obviously. They can have multiple spouses, ex-spouses,
ex-step-grandmothers, etc, depending on the basic kinkiness of the
local community.

The Address table is similar. Each record has an AddressID. The join
table has a PersonID and an AddressID to connect the two. A further
qualifier might be "Use between dateA and dateB" or "Emergency calls
but no notification". Each person can be connected to multiple
addresses through these join records.

So a process of isolating a group of records for a mailing would be to
find the original group of people you want to notify, filter out all
the "do not notify" people, then GoToRelatedGroup of Address join
records, and from there to the related group of Address records. That
eliminates all duplicates. Filter again to eliminate the "Do not use
for notification" addresses. Whew.

Doncha just wish things were simple like they used to be?
--
Lynn Allen
--
www.semiotics.com
Member Filemaker Business Alliance
Long Beach, CA

Re: Correct table structure for people/children

On Sep 9, 9:31 pm, Lynn Allen wrote:
> On 2007-09-09 13:46:57 -0700, lansingoogle said:
>
> > I liked Lynn's suggestions, but I'm not sure I follow how the
> > relationships (the human variety) are being recorded, if everyone in a
> > family is sharing a FamilyID, there's no telling who's the parent and
> > who's the child, is there?
>
> Sorry I wasn't clear in my description.
>
> In addition to a FamilyID, each record in the People table needs the
> PersonID, of course. (this gets difficult because the nomenclature we
> use to discuss db metastructure comes from families in the first place.
> Is she talking about db terms or family terms? Use your own terms but
> be VERY careful that they are clear as to directionality and
> non-confusing to others)
>
> In the Relationship join file, there are 2 RelationshipID fields, call
> them LeftID and RightID. Even though FM7+ relationships do go both
> ways, for the purposes of generational and marriage connections, we
> need to have clarity of direction of the relationship in order to
> determine who is the parent and who the child.
>
> So a parent-child relationship join record would put the parent's
> PersonID in the LeftID field, and the child's PersonID field in the
> Right field. Then to fully characterize the record connected to the
> RightID, you would have a TypeRight field, to include things like
> Child, Spouse, Ex-Spouse, Step-child. To characterize the the record
> connected to the LeftID, you'd have another TypeLeft field with Parent,
> Spouse, Ex-Spouse, Step-Parent, Guardian, Nanny, Whatever. Both these
> lists are non-exhaustive.
>
> You might also have further classifications such as "Gets All
> Notifications" "Do not Notify" "Can/Cannot pick up from School" "Watch
> Out They're Nuts" "No fund-raising", or whatever you can think of.
> Those should probably go in the People record.
>
> So from any record in the People table, you should be able to see ALL
> the connected other people, and their relationship to the record
> currently being viewed. You should be able to filter the list of
> connected people by Type up or down.
>
> A single person can be child to another AND parent to a third at the
> same time, obviously. They can have multiple spouses, ex-spouses,
> ex-step-grandmothers, etc, depending on the basic kinkiness of the
> local community.
>
> The Address table is similar. Each record has an AddressID. The join
> table has a PersonID and an AddressID to connect the two. A further
> qualifier might be "Use between dateA and dateB" or "Emergency calls
> but no notification". Each person can be connected to multiple
> addresses through these join records.
>
> So a process of isolating a group of records for a mailing would be to
> find the original group of people you want to notify, filter out all
> the "do not notify" people, then GoToRelatedGroup of Address join
> records, and from there to the related group of Address records. That
> eliminates all duplicates. Filter again to eliminate the "Do not use
> for notification" addresses. Whew.
>
> Doncha just wish things were simple like they used to be?
> --
> Lynn Allen
> --www.semiotics.com
> Member Filemaker Business Alliance
> Long Beach, CA

Thanks to both of you for your patient help. I'll do some studying
and will emerge as a far wiser FMP user!

Lanse