Best Table Structure

[Wally Steadman]

Greetings all,
I hope this finds everyone well. I am creating a contacts Database for
my Church and I was wanting to get some advice on a table structure.

I have a table called Contacts and it contains the following fields

ContactID: Autonumber
LastName: Text
FirstName: Text
BirthDate: Date/Time
Address: Text
Address1: Text
City: Text
State: Text
Phone: Text
AltPhone: Text
Email: Text
Marital Status: Text
Anniversary: Text
Hobbies: Memo

I want to associate the Spouse and Children and was wondering if I should
build a separate table with the following information:

FamContactID Autonumber
ContactID: Number (FK to Contact table)
LastName: Text (In case it is different)
FirstName: Text
BirthDate: Date/Time
Phone: Text
Email: Text
Hobbies: Memo

One of the things we are going to do is create a query that will pull out
each birthday monthly as well as Anniversaries. Not sure if it is better to
create all the entries in the Contact table or build the subtable and then
create a query looking at the common fields in both tables.

Any suggestions would be appreciated.

Thanks
Wally Steadman

 

[Lou]

Greetings all,
  I hope this finds everyone well.   I am creating a contacts Database for
my Church and I was wanting to get some advice on a table structure.

I have a table called Contacts and it contains the following fields

ContactID:   Autonumber
LastName:   Text
FirstName:   Text
BirthDate:    Date/Time
Address:      Text
Address1:    Text
City:             Text
State:           Text
Phone:         Text
AltPhone:     Text
Email:            Text
Marital Status: Text
Anniversary:    Text
Hobbies:        Memo

I want to associate the Spouse and Children and was wondering if I should
build a separate table with the following information:

FamContactID     Autonumber
ContactID:   Number (FK to Contact table)
LastName:   Text (In case it is different)
FirstName:   Text
BirthDate:    Date/Time
Phone:         Text
Email:           Text
Hobbies:        Memo

One of the things we are going to do is create a query that will pull out
each birthday monthly as well as Anniversaries.  Not sure if it is better to
create all the entries in the Contact table or build the subtable and then
create a query looking at the common fields in both tables.

Any suggestions would be appreciated.

Thanks
Wally Steadman

Have you considered the following:

Create table tblContacts
(
ContactID: Autonumber
LastName: Text
FirstName: Text
BirthDate: Date/Time
Address: Text
Address1: Text
City: Text
State: Text
Phone: Text
AltPhone: Text
Email: Text
Marital Status: Text
Anniversary: DateTime
RelatedTo number
RelatedHow text
)

For the head of the household, the RelatedTo value and ContactID value
would be the same.
The RelatedHow value would be 'SELF'.

Other members of the "household" could addressed individually.
Imagine some members of the household may be away performing military
service or attending college. Each non-head-of-household member would
have a RelatedTo value pointing to the "head's" record and the
RelatedHow could identify spouse, son, daughter, grandchild, niece,
nephew, etc.

Interestingly, this model allows "households" to form, dissolve and
change leadership.

After creating the tblContacts as indicated, consider creating a
tblHobbies. Rather than use a memo field, simply use text.

create table tblHobbies
(
ContactID long integer,
Hobby text
)

The advantage here is that you can identify everyone who might
participate in a particular social event. The value of Hobby could be
limited to a look up domain table, thus eliminating the problem of
free-form text.

 

[John W. Vinson]

Greetings all,
I hope this finds everyone well. I am creating a contacts Database for
my Church and I was wanting to get some advice on a table structure.

I have a working church membership database and I use a slightly different
structure: a table of Families (homesteads, basically) with a FamilyID, family
name (defaulting to Mr. & Mrs. John Doe, but editable in case they prefer "The
Johnsons" or "John Vinson & Karen Strickler"); it's related one to many to a
Members table with the individuals. There's VBA code in a "MoveOut" button to
handle cases where a young person goes away to college or otherwise leaves
this household and becomes a household in their own right.

It also has a table of Lists - any person can be a member of any number of
lists and vice versa, so you could have a Membership list, Choir list,
Buildings & Grounds list, etc.

If you'ld be interested in a copy I'll be glad to send you one free of charge;
contact me at jvinson <at> wysard of info <dot> com.