Normalization (1 Viewer)

[Dick7Access]

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.

 

[GinaWhipp]

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]

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.

 

[CarlettoFed]

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]

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]

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]

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.

 

[Dick7Access]

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]

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.

Excel in Access – Nifty Access

www.niftyaccess.com

 

[Mike Krailo]

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]

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]

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]

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]

Then add a field to the Church Services table with a table holding the different days and time periods the Services are held.

 

[Dick7Access]

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]

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]

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]

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.

 

[Dick7Access]

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]

You can compress it and try to attach or post on a site with a sharing service.