Table(s) Design and Relationships

[hansjhamm]

I have a good one for y'all. I am designing a database for our church
and here is my question. I have a table called membership and each
person would need to be entered into this table that have joined the
church. But, let's say I have a family, husband, wife and 2 children. I
know I could put all in this one table, but I would have this scenario,
names listed 4 times with address, phone etc...here if not potentially
even more. I know, BAD design!
What I thought may be this way...a table called family with a
autonumber (PK) and then just the last name with all the contact
information. Then separate table or tables for each member of the
family, i.e. table called men, women, children then use the (PK) from
family as a (FK) to relate all this back together in a form or a
report.

Any thoughts on the best way?



Thanks,

Hans

 

[Roger Carlson]

Separate tables for men, women, and children is a bad idea. There is really
no reason for it.

I'd have a Family table and a Members table. In the Family table, you can
store the common information like address and home phone. (This can also
tie back to financial records as each family is often designated as a Giving
Unit as well.) But each person is likely to have separate email addresses
and cell phone numbers, so you'd store that with the individual member.

It gets trickier, however, because there is a many-to-many relationship
between Family and Members. Each Family can have many Members, but each
Member can be part of more than one Family. This is true of children. Many
families are now blended and children may be part of two families. So you
would have to create a "linking" table between Family and Members.

Is it necessary to represent the spousal and parent/child relationships? It
would be easier if you didn't, but if you did, you'd could create a couple
of recursive relationships in the member's table to represent the spouses
and children relationships. (A recursive relationship is a relationship
that a table has to itself. It is often used to represent hierarchical
structures like organizations for families.)

For the Spousal relationship, you would have a foreign key in the same table
called SpouseID. In SpouseID, you would store the primary key of each
spouse.

Parent/Child is a little trickier again because each child can have more
than one parent (in more than one family) In this case, create another
"linking table" with ParentID and ChildID foreign keys, both of which link
back to the Members table primary key.


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[John Vinson]

I have a good one for y'all. I am designing a database for our church
and here is my question. I have a table called membership and each
person would need to be entered into this table that have joined the
church. But, let's say I have a family, husband, wife and 2 children. I
know I could put all in this one table, but I would have this scenario,
names listed 4 times with address, phone etc...here if not potentially
even more. I know, BAD design!
What I thought may be this way...a table called family with a
autonumber (PK) and then just the last name with all the contact
information. Then separate table or tables for each member of the
family, i.e. table called men, women, children then use the (PK) from
family as a (FK) to relate all this back together in a form or a
report.

As Roger says... don't overdo it! I have a working church membership
database (and would be quite willing to send you a free copy if you
wish) which has a Families table (with the preferred name for the
family, e.g. "The Johnsons" or "Ken and Tina Jones" or "John Vinson &
Karen Strickler"), related one to many to a table of people. You
certainly do NOT want separate male tables, female tables, etc.

The app even has a "move out" button on the Form which will let you
create a new Family when (say) a child goes off to college and becomes
a separate 'household' for the purposes of the church mailings.

John W. Vinson[MVP]

 

[hansjhamm]

John,

I would love to see how you did this...so yes if you would send it to
(e-mail address removed) I would appreciate it!
Maybe it will open my eyes on "proper design"

Thanks

Hans