I am currently in the beginning stages of designing our church database. I have read a lot about normalization and I am trying to determine the best way to handle things. Here is my question:

Each member and non-member has a bunch of information attached to them, such as address, email, birthdays, death date, phone numbers, etc. Each individual will also have a membership status, method received, date received, date inactive, date reinstated. The dates for inactive, received, and reinstated may have multiple instances. Should all this information be in the same table or should I separate it into multiple tables?

Each member will have their own number, but non-members won't. So I have an AutoID for each person. I currently have all the information in one table, but it's starting to be a lot of information.

The issue I see is that all this information is specific for each individual and without duplicating information, I don't see a way to divide it correctly.

What are your thoughts? Thanks!

Hello. I wrote a similar application for my church, so I understand a bit of what you are asking for.

My first question is "have you evaluated the need for all the information you want to store?"
Here's why I ask:Is that worth entering and maintaining? Will that data be useful, or even used at some point?

In any case, normalization is one of those things that is useful, but only if you don't take it too far (I can already feel somebody's glare at my words!).

We opted to have a record for each person, and with that person was information that was specific to them (anniversaries, birthday, cell phone, emails, etc.). We only worried about their current status and the date they reached that status. One way of maintaining history without getting into cascading records is to maintain the current status and the date in fields, and keep a running log in a memo field. Each time a status changes, add the date and change to the log, and update the fields with the current status. This way the history is there when you need it, but not particularly easy to report on. If turnover is a concern, include a field that increments each time the status changes so it keeps count.

We opted to have a second set of records for "households". Each household has a street address, a land line, location (a generalized descriptor of the town but a distinct grouping). The primary purpose of a "household" was to collect people together into family groups with a common address. We found it more efficient to work with families since most of the time, updates to one member of a household applied to all members.

Then, we used a third table to manage who is in each household - kinda like creating a single level bill of materials. One of the people inserted into a household was designated as the "head" (the name that appears on address labels to the family and on many of the reports). Also, a string was included that contained the first name of each person in a single field (just to make some of the reporting easier - it was updated each time a person was added or removed from a house).

Now, when it comes to keeping attendance, there are several approaches. We track attendance by person, but the front end has us entering it by household, then person. This allows us to maintain attendance as people move (a child grows and leaves the house and starts their own house, or two people get married and move in together).

We also used a simple ranking system to identify each family as "regular attender", "occasional attender" and "non attender". When an event (such as a Sunday service) is created and then selected for attendance entry, the families (households) are divided into 3 lists. When you select a family, it moves to the attended list and is removed from the original list (for that event) and the members of the family appear on a list off to the side along with another of other fields (prayer requests, notes, etc.) we maintain regarding that family at that event. If a member happened to be absent, we unselect them in that list. Generally, by the end of attendance entry, most of the regular attenders list is empty (they are in the attended list) and maybe three quarters of the middle list is empty.

So, to summarize an answer to your question:
1 table for each person and information specific to a person (try to avoid "one-to-many" type pieces of data, although a few, such as multiple email addresses, are fine).
1 table for each household.
1 table to associate people into a household.
1 table to add events to.
1 table to add people to those events along with other relevant information.
Also, I suggest not separately tracking the dates of membership as they change, but if it makes sense for your organization, then 1 table to manage the status change history (still keep the current status in the person table just to simplify reporting).

Hope this helps

What I do is get some a4 paper on each page write what I want to store feildname
Then I when I start seeing same data on differance pages that data come a new page

Ie

A4 page his customer/clients info
So that a4 get named customer and that the table name I then write customerid which comes the PK

When I want to link other a4 paper to the customer a4 I just write the customerid on it

Once finish I then I think / look at the a4 pages for a day or so thinking have I got all the info on the right pages then I start the building the tables in a database

Thanks for the information. As far as the membership status of each individual, it is something we need to document. Presbyterian's have a government type hierarchy and within each local church, we have a session. The session must keep a record book for each member, documenting the members status, when they were removed, reinstated, etc.

Caryn

ok. That's a good example of when to use a "one-to-many" setup using a child table with a foreign key.