Family Oriented Database

I am currently working on a database for an organisation that deals both with
individuals and families. I am particularly struggling with how to organise
the relationships between tables when it comes to coordinating information
about families.

I am assuming that each individual on the database has their own record
irrespective of whether they are married or single, and adult or a child.

I am planning to split the information up into a number of small tables.

To enter and display data I am planning to have one main form with multiple
pages – each page loosely associated with each table.

Provisional Tables and Fields:

Personal Details
-Personal ID (Pri Key)
-Title
-First Name
-Surname
-Birth Date
-Gender
-Notes
-Name for Letter Address (e.g. Mr J. Doe)
-Salutation for Letter (e.g. Dear John / Dear Mr Doe)
-Mobile Phone (it seems to me that this relates to the individual not the
family)
-Work Phone (it seems to me that this relates to the individual not the
family)
-Email (it seems to me that this relates to the individual not the family)
-Email 2 (it seems to me that this relates to the individual not the family)

Contact Details
-Contact ID (Pri Key)
-Address 1
-Address 2
-Area
-City
-Postcode
-Country
-Home Phone (it seems to me that this relates to the family not the
individual)
-etc

Organisation Involvement
-Organisation ID (Pri Key)
-Activity x
-Activity y
-Activity z
-etc

Family Details
-Family ID (PriKey)
- Spouse
-Child 1
-Child 2
-Child 3
-Child 4
-etc

I guess there will need to be another table to facilitate the many to many
relationships?

Relationships
The relationships are quite complicated. If the basic building block is a
family - parents (2 parents) and children (possibly many children?) have a
many to many relationship. A family has one main contact address but many
members. Each family member has separate involvement in activities 1
individal may be involved in many activities and so on.

The complicating factors are that:
• Families of people do not have part numbers so the ID / primary key to the
different tables must, if possible, be hidden.
• Some families will only have one parent associated – usually the mother
but sometimes the father
• The last names (surnames / family names) of the family may differ: parent
- parent, parent/s - child and child/ren - child/ren
• There may be unrelated people who have the same surname
• Single people may have no other family (it is important that the db does
not make a person with a family appear more important)
• A cell phone / email address may be shared by a husband and wife or it may
reach only one of them

It is necessary in a search to be able:
• When finding a parent of either gender to be able to identity the children
and find their ages
• When searching for a child to be able to find the parent and the parent’s
contact details
• To be able to: write to / phone / email everyone who:
o Has a certain surname
o Is Female / male
o Is involved in activity x or activity y / activity x and activity y
o Has children / no children
o Is married / not married
o Is under 18 / over 18

Does anyone have any better ideas of how to organise the data
Does anyone have any tips on how to make the tables relate?

Thnaks for your help

I am currently working on a database for an organisation that deals both
with
individuals and families. I am particularly struggling with how to organise
the relationships between tables when it comes to coordinating information
about families.

I am assuming that each individual on the database has their own record
irrespective of whether they are married or single, and adult or a child.

That would be correct. You should have a table that lists all individuals,
with whatever information would be specific to that individual.

I am planning to split the information up into a number of small tables.

The number of tables should be dictated by a natural grouping of
data/information, not just for the sake of splitting the information.

To enter and display data I am planning to have one main form with multiple
pages – each page loosely associated with each table.

Not a bad idea, though there may be multiple ways that you want to view your
information. You may wish to view by family or by individual.

Provisional Tables and Fields:

Personal Details
-Personal ID (Pri Key)
-Title
-First Name
-Surname
-Birth Date
-Gender
-Notes
-Name for Letter Address (e.g. Mr J. Doe)
-Salutation for Letter (e.g. Dear John / Dear Mr Doe)
-Mobile Phone (it seems to me that this relates to the individual not the
family)
-Work Phone (it seems to me that this relates to the individual not the
family)
-Email (it seems to me that this relates to the individual not the family)
-Email 2 (it seems to me that this relates to the individual not the family)

This seems to be fine, each individual has their own personal ID.

Contact Details
-Contact ID (Pri Key)
-Address 1
-Address 2
-Area
-City
-Postcode
-Country
-Home Phone (it seems to me that this relates to the family not the
individual)
-etc

It seems like this table should be the primary table for designating a
family. If so, then this should be Family Details, with Family ID as the
primary key. I would modify it like this:

Family Details
FamilyID (PK)
Address 1
Address 2
Area
City
Postcode
Country
Home Phone

Organisation Involvement
-Organisation ID (Pri Key)
-Activity x
-Activity y
-Activity z
-etc

A good thought, but whenever you start listing Activity x, Activity y,
Activity z, you are making a spreadsheet instead of a database. You might do
better if you create a table just for listing activities, and then use
another table to tie in to the individuals.

tbl_Activities
ActivityID (PK)
ActivityText

tbl_PersonalActivities
PersonalActivitiesID (PK)
Personal ID
ActivityID

Family Details
-Family ID (PriKey)
- Spouse
-Child 1
-Child 2
-Child 3
-Child 4
-etc

Again, this is a spreadsheet. In order to tie together individuals to a
family, you will probably want a family table (like the one I suggested
above) to list the families, and then use a table to tie individuals to those
families.

tbl_FamilyMembers
FamilyMembersID (PK)
Personal ID
FamilyID

Look at this, and see if it makes sense.

On Mon, 13 Mar 2006 14:18:27 -0800, Xanda
wrote:

I am currently working on a database for an organisation that deals both with
individuals and families. I am particularly struggling with how to organise
the relationships between tables when it comes to coordinating information
about families.

I have a similar database (used for church membership), with tables
for Families, People (related one to many), and what you call
activities (I call them mailing lists since that's how the church
secretary uses them). It will support pretty much everything you
describe. I'd be happy to send you a working sample free of charge, if
you wish to send me your email address offline (send it to jvinson
at wysard of info dot com, after omitting the blanks and editing
the punctuation).

John W. Vinson[MVP]

Thank you so much for your reply. It seems to me that you understand what I
want to do. I have set up the tables with their relationships as you suggest.

When it comes to entering data I would like, for ease of use, to have one
central form with different pages.

I would like the first page to display the primary contacts (either a single
person or a couple in the case of family) along with their main email / phone
contact details.

The next page of the form I would like to display postal address details as
well as letter salutaion and to whom the letter should be addressed (either
Mr John Doe or Mrs John Doe or Mr and Mrs John Doe)

The next page I would like to display a list of children (if they have them)
and the children's ages

The next page I would like to have a list of all activites in which they are
involved.

Is this possible?
Would the sections need to be subforms?
How would I go about setting it up?

I realise that these are big questions and time consuming to answer. I would
really appreciate any help that you can give. Thanks.

"mnature" wrote:

I am currently working on a database for an organisation that deals both
with
individuals and families. I am particularly struggling with how to organise
the relationships between tables when it comes to coordinating information
about families.

I am assuming that each individual on the database has their own record
irrespective of whether they are married or single, and adult or a child.

That would be correct. You should have a table that lists all individuals,
with whatever information would be specific to that individual.

I am planning to split the information up into a number of small tables.

The number of tables should be dictated by a natural grouping of
data/information, not just for the sake of splitting the information.

To enter and display data I am planning to have one main form with multiple
pages – each page loosely associated with each table.

Not a bad idea, though there may be multiple ways that you want to view your
information. You may wish to view by family or by individual.

Provisional Tables and Fields:

Personal Details
-Personal ID (Pri Key)
-Title
-First Name
-Surname
-Birth Date
-Gender
-Notes
-Name for Letter Address (e.g. Mr J. Doe)
-Salutation for Letter (e.g. Dear John / Dear Mr Doe)
-Mobile Phone (it seems to me that this relates to the individual not the
family)
-Work Phone (it seems to me that this relates to the individual not the
family)
-Email (it seems to me that this relates to the individual not the family)
-Email 2 (it seems to me that this relates to the individual not the family)

This seems to be fine, each individual has their own personal ID.

Contact Details
-Contact ID (Pri Key)
-Address 1
-Address 2
-Area
-City
-Postcode
-Country
-Home Phone (it seems to me that this relates to the family not the
individual)
-etc

It seems like this table should be the primary table for designating a
family. If so, then this should be Family Details, with Family ID as the
primary key. I would modify it like this:

Family Details
FamilyID (PK)
Address 1
Address 2
Area
City
Postcode
Country
Home Phone

Organisation Involvement
-Organisation ID (Pri Key)
-Activity x
-Activity y
-Activity z
-etc

A good thought, but whenever you start listing Activity x, Activity y,
Activity z, you are making a spreadsheet instead of a database. You might do
better if you create a table just for listing activities, and then use
another table to tie in to the individuals.

tbl_Activities
ActivityID (PK)
ActivityText

tbl_PersonalActivities
PersonalActivitiesID (PK)
Personal ID
ActivityID

Family Details
-Family ID (PriKey)
- Spouse
-Child 1
-Child 2
-Child 3
-Child 4
-etc

Again, this is a spreadsheet. In order to tie together individuals to a
family, you will probably want a family table (like the one I suggested
above) to list the families, and then use a table to tie individuals to those
families.

tbl_FamilyMembers
FamilyMembersID (PK)
Personal ID
FamilyID

Look at this, and see if it makes sense.