Help with Normalization

[chellebell1689]

So I've posted a few forums on here and recently I've been told to normalize my data (which is a new concept to me). When I first started reading about it, I thought I had done a fairly good job of it (I have a table for my church members, visitors, children, those who have passed away, and those who have moved). But the more I read the less confident I am in my data sorting. Each table has a lot of fields (any and all data that goes along with that person). I was hoping someone would have the time to look at my fields (pictured) and let me know the best way to normalize my data (or at bare minimum start me on the right track so I can figure the rest out on my own).

I really appreciate the help! I'm still learning and having help and explanation on what/why I'm doing (this) helps me retain better. Apparently in my BCIS classes they don't really teach normalization or don't explain that's what we're doing.

 

[AccessBlaster]

Does each attachment represent a separate table? Attach# has a question mark after staff. Id get rid of that and any spaces in your field names. Also I don't see any foreign keys.

 

[jdraw]

Here is a tutorial that will help you with database design. It takes about 30 minutes to an hour to work through it generally, but you will learn the concepts. You do have to work through it.
Good luck.

 

[JHB]

After you've worked through the link jdraw sent you, show your relationship in printscreen!

 

[jdraw]

JHB is correct -- once you create the tables and relationships as per the tutorial - the relationships window will have represent the structure of your database.
And a print screen capture would be useful in any follow up posts.

 

[chellebell1689]

Does each attachment represent a separate table? Attach# has a question mark after staff. Id get rid of that and any spaces in your field names. Also I don't see any foreign keys.

No, sadly that's all one table. I'll take out the punctuations & spaces.

Thank you all for your responses. I'll do the tutorial and then on my next day at work, I'll apply to this db. Thanks.

(I'm going to go ahead and close this and if I have any other questions, I'll either reopen it or start a new.)

 

[AccessBlaster]

Once you get through the tutorials you will have a better idea on how to breakout other tables if needed. Good luck with your project.

 

[chellebell1689]

I really appreciate all the help and the link for the tutorial. I'm still reading through it (just started today when I got back to work). If ya'll don't mind, I'm going to post screen shots as I go along to make sure I'm on the right path. So here's the first, let me know whatcha think.

Thanks


*Update*
So I finished and I understand everything, but I need help with the foreign keys. I feel like the Env_Num should go in all tables (as foreign), but I also feel all the primaries from the other tables should go into the Member table. Please help.

 

[gemma-the-husky]

assuming that the member table is about right

you don't need Age, - you evaluate that from birthdate - otherwise you have to recalculate the age all the while. Similarly with death. if you have a date of death, then the person has passed.

The family table seems over complicated
If a member can only be in one family, then you need a table for family, and a field in the members table to identify that family.

if a member can be in more than one family, then you need a table for family, and a table for family members.

I am not sure what all the other fields in the family table are for. Some look unnecessary.

I would have thought deacon is just another staff. Add a yesno field to the staff table for Deacon, and you can lose the deacon field. You may also consider members and staff as just different categories of person, and just have one "person" table to manage both. You would just need yesno/flags to distinguish the roles.

Finally, possibly some of the membership fields also belong in the persons/members table also. you only really need a second table if there can be multiple memberships for a member/person.

You may be able to lose the committee table too. Just put a committee member flag on the persons table. It depends whether you need to track lapsed/ex committee members.

 

[chellebell1689]

The age is in there just for the member detail form (which will show ALL information for that member), the pastor likes having it and it helps when we do VBS and (every once in a blue moon) we redo the Sunday School Classes. I can see that for the death date and will take of the y/n field.

For the family table I was trying to group all "family" type data in there. But that seems easier to just have a table for all the families and then the relationship field in the member's table. The orders show mom/dad/bro/sis and 1st child, 2nd, etc. The salutation is for titles (Dr., Prof., Col., etc) and the Label Name is how to address any mail to that person. The family picture is for the Directory, while the profile picture (in the member table) is for their individual details that we pull up in Access.

The deacons aren't included in staff because they are not paid. Also, I created a table for them because I thought it would be easier to assign members to them and then print out their member list each year.

The staff table is just the category and I'll assign the StaffID to those who are on the staff. Looking at those fields again, I don't need the "OnStaff" field, it's like the death date & did they pass away that you talked about earlier.

For the committess, yes, we do want to track members by when they joined a certain committee (and they can be on more than one) and who used to be on what committee(s).

All the fields shown are one we currently track in Alpha 6. All have data for one person
or another. I've gotten rid of the ones that were completely blank and the rest were the ones the pastor wants to track.

So should I combine the "Member" and "Membership" tables? I had them separated as identifying information for the member and information on the member's membership history with this church and any other church (if applicable).

Thank you so much for your help!

 

[gemma-the-husky]

it's a matter of what you want to track. If a person can have multiple memberships, then you need a membership table. if only one, then you do not need it.

you do not need the age. Having it displayed on a form, still does not imply a need to store it. NO User should ever need to open a table directly.

age = datediff("y",date(),"DOB")

ie the difference in elapsed years between the current date, and date of birth.

-----
the thing about families was really whether a person can be in multiple families.

Sarah Smith is the daughter Jim Smith, and now the wife of John Brown. Is she a member of both families or just one? What if she just left home and lives independently? That's what I meant.

Surely the salutation is the member's not the families.


Anyway, irrespective of this, there is always a process of continual refinement. When you start to develop queries and forms, you will probably realise that you do need either some more tables, or need to move fields to different tables. It's hard to get it spot on first time.

 

[chellebell1689]

Ok thank you!

The only way a member can have multiple memberships is if they're on a committee(s). All the fields shown, in all the pictures, are what the pastor wants to track.

The age makes sense. Now that I think about it, I know how to set it up in the form to display the age without using the field. Upon more thought, I'm not really using that field. I have a query that updates the birthday for me when I open the member detail form. :-D

The way the pastor has the families set up is once they get married. In other words Sarah Smith is still in Jim Smith's family until she marries John Brown, then she's in his family. We still have her as Jim's daughter, but now she's in BRO01 instead of SMI01 (family ID).

Usually it's for couples (Mr. & Mrs. John Brown), but is also used for those families that like "The Brown Family" on their letters.

Thank you for all your help. I'm just hoping I understand enough about normalizing to get it combed down to the way it should be set up.

 

[chellebell1689]

Oh do you by chance have any ideas/hints on what my foreign keys should be? (How I should link my tables?)

Thanks

**Edit**
Ok how would I link my Sunday School Class table with my members table? I need to be able to assign them to a class, and check if they are on the roll (if they're not they don't need to show up on the roll sheet).