Normalization (1 Viewer)

Dick7Access

Dick S
Local time
Today, 06:54
Joined
Jun 9, 2009
Messages
4,197
a DB that I use almost every day I made 25 years ago before I knew anything about normalization. It has over 13000 records and its a mess. It has close to 100 fields in the main table. It has one other table for meeting which has 15 fields. Here is my main form and meetings form with child links to main records. What other tables does anybody recommend.
 

Attachments

  • churchDB.png
    churchDB.png
    126.3 KB · Views: 215

GinaWhipp

AWF VIP
Local time
Today, 06:54
Joined
Jun 21, 2011
Messages
5,901
Hmm, would be helpful to see a screen shot of your Relationship window so we can see what tables you already have and what fields are in them.
 

Dick7Access

Dick S
Local time
Today, 06:54
Joined
Jun 9, 2009
Messages
4,197
Hmm, would be helpful to see a screen shot of your Relationship window so we can see what tables you already have and what fields are in them.
 

Attachments

  • CroppedRlation.png
    CroppedRlation.png
    38.8 KB · Views: 205

CarlettoFed

Member
Local time
Today, 11:54
Joined
Jun 10, 2020
Messages
119
In order to give an opinion it would be necessary to really see how the two tables are structured by posting an example file with a minimum of data and replacing the sensitive ones. It can be said, already now, that the States, the Cities, the Postal Codes, the Contacts, the Pastors and certainly more should be normalized by creating as many tables to be related to the Churches table, etc ...
 

GinaWhipp

AWF VIP
Local time
Today, 06:54
Joined
Jun 21, 2011
Messages
5,901
Okay, so with what I see I suggest...

tblChurches (And yes, I realize it may only be one Church but this is about normalization)
-cChurchID
-etc. (All fields pertaining to the Church only, i.e. address, phone numbers, etc.)

tlkpServices (Look-up table for all the types\days of the Church services)
-sServiceID
-etc.

tblChurchServices
-csChurchServiceID
-csChurchID - relate to tblChurches
-csServiceID - relate to tlkpServices
-csSermonID - relate to tlkpSermons
-etc.

tlkpSermons
sSermonID
-sSermon (Memo field)
-sPreached
-sCanceled
-sCanceledReason (Test, 255)

-etc.

For the Canceled fields, in bold, is it the Sermon that gets canceled or the Service? If the Sermon you can leave there but in my thinking you would only want to know the ones that got preached so you don't use the same one again.

Not sure I understand what Meeting is for. Are there Meetings AND Services? Do Sermons happen at both? I also don't fully understand some of those other fields and some are hidden from view but this should get you started, hopefully.
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:54
Joined
Sep 21, 2011
Messages
14,038
a DB that I use almost every day I made 25 years ago before I knew anything about normalization. It has over 13000 records and its a mess. It has close to 100 fields in the main table. It has one other table for meeting which has 15 fields. Here is my main form and meetings form with child links to main records. What other tables does anybody recommend.
Dick
If it has served you well for over 25 years. I'd leave well alone?
 

Cotswold

Active member
Local time
Today, 10:54
Joined
Dec 31, 2020
Messages
521
Why not create a 'correct' or preferred database design with all tables and relationships
for the system you now require. Once you are happy with that design, create all of the
Update Queries to transfer the existing data into the new design.

Test it and if it isn't quite right, delete and amend the export/import until it is. The old
system can still be used for reference and checking detail during the update.

I'd prefer to do that than start modifying the existing, where you'll end up with loads of
backups and forget what was changed where.
 
Last edited:

Dick7Access

Dick S
Local time
Today, 06:54
Joined
Jun 9, 2009
Messages
4,197
Why not create a 'correct' or preferred database design with all tables and relationships
for the system you now require. Once you are happy with that design, create all of the
Update Queries to transfer the existing data into the new design.

Test it and if it isn't quite right, delete and amend the export/import until it is. The old
system can still be used for reference and checking detail during the update.

I'd prefer to do that than start modifying the existing, where you'll end up with loads of
backups and forget what was changed where.
Yes, that is what I want to do. I want to build a completely new DB for several reason. One, is that while on this forum I am a novice, in my circle I am the expert. I have gone into churches who have just bought a whole system with office suite and complain they can make it do anything. In a half hour I set them up with a simple db. Its to my advantage to make db for them the best way. Secondly, A is my hobby. I can fool with it even when I have just an hour to kill in a hotel. Believe me, I am not bout to mess with the old one. Thanks to all who commented, this advice, has, I believe steered me in the right direction.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 10:54
Joined
Jul 9, 2003
Messages
16,244
My Excel in Access blog covers normalisation from a particular viewpoint.

Not sure it's quite the viewpoint you are looking from, but it might prove useful.


 
Last edited:

Mike Krailo

Well-known member
Local time
Today, 06:54
Joined
Mar 28, 2020
Messages
1,030
Dick, everyone that would like to help could really benefit by looking at your current DB. Just fill in descriptions (table design) for fields you think might need more explanation for those not familiar with the entities. Remove sensitive data as required.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:54
Joined
Feb 19, 2002
Messages
42,970
Start by removing the obvious duplicates as mentioned. I'm not sure how many "services" fields you have but there should be a separate table that defines service types and another that links services to churches. Try not to mush info together. For example instead of a bunch of names like wed afternoon, every thurs eve, etc. Use multiple fields = frequency = weekly, third, second; weekday = sun, ,mon, Time = morn, aft, eve, midnight, dawn. This will ultimately help a lot if you want to make a published schedule since the items will be easier to "slot".
 

Dick7Access

Dick S
Local time
Today, 06:54
Joined
Jun 9, 2009
Messages
4,197
Start by removing the obvious duplicates as mentioned. I'm not sure how many "services" fields you have but there should be a separate table that defines service types and another that links services to churches. Try not to mush info together. For example instead of a bunch of names like wed afternoon, every thurs eve, etc. Use multiple fields = frequency = weekly, third, second; weekday = sun, ,mon, Time = morn, aft, eve, midnight, dawn. This will ultimately help a lot if you want to make a published schedule since the items will be easier to "slot".
sounds like good advice
 

Dick7Access

Dick S
Local time
Today, 06:54
Joined
Jun 9, 2009
Messages
4,197
sounds like good advice
Pat, I just went back over your recommendation, and probable didn't understand it correctly the first time. The reason for first morning, second morning, evening etc. is that many have two and three services in one day. I have to show up at the right time for the one I am schedule to speak at. What do you mean by "easier to slot"
 

GinaWhipp

AWF VIP
Local time
Today, 06:54
Joined
Jun 21, 2011
Messages
5,901
Then add a field to the Church Services table with a table holding the different days and time periods the Services are held.
 

Dick7Access

Dick S
Local time
Today, 06:54
Joined
Jun 9, 2009
Messages
4,197
Then add a field to the Church Services table with a table holding the different days and time periods the Services are held.
Starting to make sense. If its not too much trouble can you send a example of that table.
 

GinaWhipp

AWF VIP
Local time
Today, 06:54
Joined
Jun 21, 2011
Messages
5,901
Oh, I did not create the tables just typed up some suggestions. If you can wait till tomorrow evening I can get it to you. Might be earlier but don't want to make a promise I can't keep.
 

Dick7Access

Dick S
Local time
Today, 06:54
Joined
Jun 9, 2009
Messages
4,197
Oh, I did not create the tables just typed up some suggestions. If you can wait till tomorrow evening I can get it to you. Might be earlier but don't want to make a promise I can't keep.
thanks
 

GinaWhipp

AWF VIP
Local time
Today, 06:54
Joined
Jun 21, 2011
Messages
5,901
So, I got finished a little early tonight and...

Okay so a few things:

When you enter a Church and select the Services they have you enter the Times there not in tlkpServices which just holds Sunday School, Wednesday, Evening Services. Because while the Services may be the same, i.e. Sunday School, the Times may not be.

Sermons are linked to tblChurchServices which is linked to tblChurches. You select your Sermon from there which are stored in tlkpSermons.

I broke out Contacts because there will most likely be more than one Contact per Church. You have the ability to add their Role and their Title as they don’t always match but the Role will come in handy if you want to send an eMail to those assigned a specific Role.

tblLinks is not linked because it can be used for Churches or Sermons for access external documents or websites.

There are some extra fields in there so don’t think I meant for you to use all of them but I was pulling from some stock tables. That said, I also did not add all the fields I think you might need. Not sure I have the full picture of what you are trying to do so consider this a *Starter Kit*. Hope it helps. 🙃
 

Attachments

  • Dick7Access.accdb
    728 KB · Views: 167

Dick7Access

Dick S
Local time
Today, 06:54
Joined
Jun 9, 2009
Messages
4,197
So, I got finished a little early tonight and...

Okay so a few things:

When you enter a Church and select the Services they have you enter the Times there not in tlkpServices which just holds Sunday School, Wednesday, Evening Services. Because while the Services may be the same, i.e. Sunday School, the Times may not be.

Sermons are linked to tblChurchServices which is linked to tblChurches. You select your Sermon from there which are stored in tlkpSermons.

I broke out Contacts because there will most likely be more than one Contact per Church. You have the ability to add their Role and their Title as they don’t always match but the Role will come in handy if you want to send an eMail to those assigned a specific Role.

tblLinks is not linked because it can be used for Churches or Sermons for access external documents or websites.

There are some extra fields in there so don’t think I meant for you to use all of them but I was pulling from some stock tables. That said, I also did not add all the fields I think you might need. Not sure I have the full picture of what you are trying to do so consider this a *Starter Kit*. Hope it helps. 🙃
Thank you very much. I will work on it, My db is too big to try and send via AWF. Can I sent you a link via drop box?
 

CarlettoFed

Member
Local time
Today, 11:54
Joined
Jun 10, 2020
Messages
119
You can compress it and try to attach or post on a site with a sharing service.
 

Users who are viewing this thread

Top Bottom