Requesting info on Normalizing our Database (1 Viewer)

[dgaletar]

For any of those who have seen me here before, you will certainly know that I really have very little idea of what I am doing. Therefore, I am offering to all of you the chance to help a newbie out. (lol)

Anyway, I have spent the last two months designing (and re-designing) (and re-designing) a database that I originally built from scratch. I have also spent that time learning (for the first time) how to use Access (not by choice though!)

Now I have a database that, for all intent & purposes, is pretty much finished and currently in use. I have either imported all of the data or entered it for each vehicle, and am looking pretty good.

The problem that I am having is that the last time that I re-started this project I used a template from something different all together, because it had a feature that I just couldn't get to work in my previous version.

Therefore, one of the fields is named "CustomerID" instead of "VehicleID". I am scared to change it... in fear of the whole database coming apart at the seams!

In addition to that task, I have been told that I need to "Normalize", "Decompile", "Compact" & "Compile" the database.

The best case scenario for me would be to have someone take a look at it and give me some suggestions on what needs to be done. (Or just knock it on out – no hate here!!!)

Please note that I am a mechanic by trade, and am ONLY doing this because my boss "suggested" to me that it would be a good idea if I did it. I am not a database designer, and have no intentions of ever being one.

Any and all help would be GREATLY appreciated!!!

dgaletar

 

[plog]

Post your file, or send it to me and I would be happy to help.

 

[dgaletar]

OK... here she is!!! In all her glory (lol)

Thanks!

 

[dgaletar]

And, just so you don't get frustrated looking for me, I am leaving for the day. I will be back in at 7:00am EST. So if you need anything, I'll take care of it then.

Thanks again!

 

[plog]

I'm just looking at your tables, until you have that right, nothing else really matters. Here's what I see:

1. The tables Air_Filters, Batteries, Fuel_Filters, Oil_Filters should be combined. Anytime you have tables with the same structure they should usually be merged. I would copy the structure of one of them call that 'Parts', add a new field called 'PartType' which would hold the name of the table the data is currently in. Then move your data there like so:

Parts
PartID, PartType, PartBrand, PartDetails
1, "Battery", "ACDelco", "75-5YR"
2, "Battery", "ACDelco", "K7711-61112"
3, "Air Filter", "WIX", "42013"
4, "Air Filter", "WIX", "42098"
5, "Fuel Filter", "KOHLER", "120-436"
etc.


2. In your Employees table you don't need the field [Employee ID] if the field [Employee Number] is unique.

3. The table Service_Specs has some fields with non-alpha-numeric field names. This just makes coding harder down the road. Use only letters or numbers in object names.

4. Service_Specs has Oil Filter, Fuel Filter, Battery fields but they don't link to your tables of those same names. Service_Spec should store the ID of those fields not text. First, I'd make your Parts table as I described above then I'd reconfigure those Service_Spec fields to store the number of that Part and not the text.

 

[dgaletar]

Thanks plog! This is REALLY good stuff! I'll get to it first thing in the morning.

On a side note, I'm really trying to learn how 2 make a simple app for our phones that would be able to display vehicle details for us while we are working on themthem. Please keep this in mind as you rumage around in there.

 

[dgaletar]

Hey plog. I got to work on this this morning, and I got about 20 seconds into it before I ran into a question for you.

When you say "Then move the data like so:", you mean "then store the data like so:", right? Because I created the table exactly like you suggested, but I am not sure how to get the information FROM the individual tables INTO the new "Parts" table.

Also, once I move all of the data into the new "Parts" table, don't I have to change all of those fields on the respective forms to use the new table instead?

And, most importantly, is there anything else that I need to do so that I don't "break" the database with this?

 

[dgaletar]

OK plog, I moved on to #2 in the meantime. I removed [FONT=&quot][Employee ID][/FONT] from the [FONT=&quot]Employees Table [/FONT]like suggested. I renamed [FONT=&quot][Employee Number][/FONT] as [FONT=&quot][Employee ID], [/FONT]and made [FONT=&quot][EmployeeID] [/FONT]the primary key.

Now this is what I'm talking about above. I had to go through all kinds of stuff to make sure things were working properly, and they weren't. I had to do a bunch of changing things to get them to work again.

I don't like that because I am not very confident. With my luck, I'll think that I got everything and my boss will walk over... click one thing... and that will be the one that I missed!

 

[dgaletar]

Now on to #3. While I see what you are saying, I'm not sure how to change it. Maybe if I remove all of that extra stuff from the table, and add it into the form in the "Caption" field.

Yeah, that may just work. OK, I'm off to do that task now. Thanks!

 

[dgaletar]

OK, step #3 - COMPLETED! I went through each and every one of my tables and took every space and/or character out of the name. I then went to the associated forms and revised them accordingly.

Awesome stuff!!!

 

[plog]

1. When I say move the data I mean you need to move it from those individual tables into the Parts table I described. To get it from those individual tables you should be able to build an APPEND query for each individual table. Once you create the Parts table, yes, you will have to change everything that depended on those individual tables. The thing is though, you don't really use those individual tables. Like I said in #4 you aren't storing their ID numbers you were storing the text.


2. Backup, backup and then backup. Always have an old version around that you can either restore to entirely or copy a table/query/form out of to fix any subsequent mistakes you have.

 

[dgaletar]

Like I said in #4 you aren't storing their ID numbers you were storing the text.

I guess that I am not understanding the difference.

 

[dgaletar]

OK, plog, an UPDATE:

DONE! 1. The tables Air_Filters, Batteries, Fuel_Filters, Oil_Filters should be combined. Anytime you have tables with the same structure they should usually be merged. I would copy the structure of one of them call that 'Parts', add a new field called 'PartType' which would hold the name of the table the data is currently in. Then move your data there like so:

Parts
PartID, PartType, PartBrand, PartDetails
1, "Battery", "ACDelco", "75-5YR"
2, "Battery", "ACDelco", "K7711-61112"
3, "Air Filter", "WIX", "42013"
4, "Air Filter", "WIX", "42098"
5, "Fuel Filter", "KOHLER", "120-436"
etc.


DONE! 2. In your Employees table you don't need the field [Employee ID] if the field [Employee Number] is unique.


DONE! 3. The table Service_Specs has some fields with non-alpha-numeric field names. This just makes coding harder down the road. Use only letters or numbers in object names.


:banghead: 4. Service_Specs has Oil Filter, Fuel Filter, Battery fields but they don't link to your tables of those same names. Service_Spec should store the ID of those fields not text. First, I'd make your Parts table as I described above then I'd reconfigure those Service_Spec fields to store the number of that Part and not the text

...and here we are at the dreaded #4!!! So I made the "Parts" table like you said, and loaded it with the information from the individual tables. Now I assume that I have to do something to the "Service_Specs" table, and to the "Service_Specs" form... right? I also imagine that I should delete those other individual tables now, right again???

 

[plog]

In the table Service_Specs you have fields for Oil Filter, Air Filter, etc. Those fields are text values, not numeric values that link to the individual tables. Those fields should be numeric values which link to an ID number.

 

[dgaletar]

OK, so I'm thinking that I just need to:

1. Delete the existing relationships between those fields and the individual tables
2. Change the field types in the "Service_Specs" table to numeric values
3. Create new relationships between those fields in the "Service_Specs" table and the new correlating fields in the "Parts" table

Am I on the right track here???

 

[dgaletar]

Ummmm, plog... have you tried looking at the database relationships window since you got this? Shouldn't there be like 100 more relationships in there than there are?

For example, there are no existing relationships between "Service_Specs" and the "Air_Filters" table. Is that weird?

 

[dgaletar]

Another thing is that when I changed the fields from "Text" Combo Boxes to "Number" Text Boxes, I think that I lost some data. (I don't mind because I backed it up... moments before I changed them! ) The reason that I am saying that is because some of the part numbers use letters in them.

Oh, wait a second... am I just supposed to be linking them to the "PartID" field? Because that makes a lot of sense!!!

So I tried that, and entered info into a couple of the fields, and nothing went into the "Parts" table.

 

[plog]

Can you post an updated version of your database?

 

[dgaletar]

Here the backup I made before the last round of changes...

 

[dgaletar]

Ooops... i forgot the stupid database!