Requesting info on Normalizing our Database

[dgaletar]

One thing that I did notice... shouldn't the table "tblServicetypes" actually be named "tblServiceSpectypes"?

I also just copied and renamed the following three tables:

• tblServiceSpecs = tblDriveline
• tblServiceSpecParts = tblDrivelineParts
• tblServiceSpectypes = tblDrivelinetypes

This should take care of the other "driveline" information for each vehicle. Right???

 

[dgaletar]

OK, Bob, I am uploading a newer more detailed PDF of the table layouts.

I also uploaded a PDF of the relationships for this new database.

Let me know what you think...

 

[boblarson]

Sorry, for not getting back to you until now. I will take a look. I was extremely busy at work today so I couldn't pop in like I would have.

 

[boblarson]

Okay, your diagram and list both help. But things that should be changed:

• Department Name should NOT be stored in tblEmployees or tblVehicles. Department ID is what should be stored.
• Don’t know why you used SetupID as the ID for Company but I would use CompanyID and again, Company NAME should NOT be stored in the Departments table. COMPANYID should be stored there.
• Your parts should be in a single parts table. It doesn’t matter if they are windshield wipers or a transmission assembly. If it is a part, it goes in the parts table. The partID is what would be stored in the other tables where you need to have the parts listed.
• Service Specs – what good are they if you aren’t using them? They store the information for which a particular service would entail, do they not? So I’m not clear on the disconnect that you have between the Service Specs and the Jobs which would be using the information provided from them. You have JobTypes but doesn’t that mirror the service specs?
• VehicleID – USE AN AUTONUMBER surrogate key. Don’t use what you have there. Many times you may think that would be a perfect key but either business rules change or the number changes and doesn’t fit what you’ve allowed for. Use an Autonumber instead and then you can set an index (no duplicates) on the Caption/CUA# which you can store in another field.

 

[dgaletar]

Thanks for taking the time here, Bob. I do know how busy you are, and what a burden this must be.

As to your observations:

1. this is something that I have been struggling to understand. But I'm working on it. DONE

   Department Name should NOT be stored in tblEmployees or tblVehicles. Department ID is what should be stored.

2. Believe it or not, that was from the original template that I based it on. I'll change that (like I knew I should have). DONE

   Don’t know why you used SetupID as the ID for Company but I would use CompanyID

3. See response to point 1.DONE

   ...and again, Company NAME should NOT be stored in the Departments table. COMPANYID should be stored there.

4. NOT DONE

   Service Specs – what good are they if you aren’t using them? They store the information for which a particular service would entail, do they not?

   NO, THEY DON'T.
   

   So I’m not clear on the disconnect that you have between the Service Specs and the Jobs which would be using the information provided from them. You have JobTypes but doesn’t that mirror the service specs?

   NO, THEY DON'T.
   
   And this is where I was afraid that the disconnect between me and you was gonna kick in. I have been trying to tell you that the "Driveline" & the "ServiceSpecs" tables are for reference ONLY! It has nothing to do with the actual work that we are trying to track. Maybe this will help; imagine that you are an Access database designer for a living. And let's say that when you design a database for a contractor, you ALWAYS have to use a certain VBA coding for them. But, for an accountant, that VBA doesn't apply. So you have a different VBS code specifically for them. So, in an effort to save time, you create a list that tells you "when I design a database for this type of business, I need to use XYZ code." That way you don't have to try to remember which code goes where, or have to look it up on the internet. Well, when a 2004 Ford F150 with a 5.3ltr. V8 comes in for an oil change, I look up the vehicle number, and refer to the "ServiceSpecs" table to see how much of what kind of oil it takes, and which filter to use, all without having to remember it or look it up on the internet each time. HUGE time saver. Then, just so we can look back as a reference, we document the work in the "Jobs" table.
5. NOT DONE

   VehicleID – USE AN AUTONUMBER surrogate key. Don’t use what you have there. Many times you may think that would be a perfect key but either business rules change or the number changes and doesn’t fit what you’ve allowed for. Use an Autonumber instead and then you can set an index (no duplicates) on the Caption/CUA# which you can store in another field.

   The issue here is that our vehicles already have numbers. So using an autonumber might be difficult. For example, we still have trucks 1-4, but 5-9 have died or been traded in a long long time ago. We don't even have records for them.

I hope that I don't sound condescending in my reply. I just want to make sure you understand how we are using this database. And, since you do not use it, you could be thinking about it differently. I certainly don't need you doing any more work than you already have to on this!

 

[boblarson]

NOT DONE NO, THEY DON'T.

NO, THEY DON'T.


And this is where I was afraid that the disconnect between me and you was gonna kick in.

Not a problem as I'm trying to learn your business from what you post and so those questions come up and so back and forth explanation is needed.

I have been trying to tell you that the "Driveline" & the "ServiceSpecs" tables are for reference ONLY! It has nothing to do with the actual work that we are trying to track.

No worries. That is helpful. I was thinking that it would be a definition of what needed to be done and so you would run an append query to add the steps in the job when it was needed but since it isn't, it can stand alone as it is.

NOT DONE The issue here is that our vehicles already have numbers. So using an autonumber might be difficult. For example, we still have trucks 1-4, but 5-9 have died or been traded in a long long time ago. We don't even have records for them.

USE THE AUTONUMBER!!! Even with what you have said, this comes from 16 years of experience. Reliance on natural keys will come back to bite you more than you might think. It is a hard lesson to learn and usually requires redoing the whole database when something changes as you have data, queries, reports, forms, etc. that rely on that and then, if something happens that you had no ability to foresee, it can wreak havoc on your system. So, I've learned from my past mistakes and I ALWAYS use a surrogate (autonumber) key. If I want the other number too, I can use it in a different field and use an index to keep it from being duplicated.

I hope that I don't sound condescending in my reply. I just want to make sure you understand how we are using this database. And, since you do not use it, you could be thinking about it differently. I certainly don't need you doing any more work than you already have to on this!

I understand that and it does take some back and forth to understand the business and flow. I hope you will take my advice with the autonumber. It comes from actual history of my own and also from people who have shown up on the forums with the same issue - they were told, or thought, nothing would ever change for their natural key. But then it happened and BAM! pain and suffering. So I'm trying to help you avoid that pitfall.

 

[dgaletar]

No worries, Bob. I'm putty in your hands! Autonumber it is. So, where does that leave us? Besides the Autonumber in the tblVehicles, is everything else right?

Does that help it to make more sense?

And, if so, what's the next step?

(btw, I changed "VehicleID" to an Autonumber, and added "CUAID" as the vehicles number that is assigned by us. Better???)

 

[boblarson]

No worries, Bob. I'm putty in your hands! Autonumber it is. So, where does that leave us? Besides the Autonumber in the tblVehicles, is everything else right?

Post the relationships diagram again so we can look.

And, if so, what's the next step?

If it is correct, It would be to start thinking about the form structure for entering/editing the data (you will use some subforms as well as possibly some subforms on subforms depending on the items). And the reports you want to come out of it.

(btw, I changed "VehicleID" to an Autonumber, and added "CUAID" as the vehicles number that is assigned by us. Better???)

That sounds like what I would do.

 

[dgaletar]

Also, if you take a minute and look at my previous database, you will see that, when you select a vehicle (any vehicle) the page is broken down into four real sections; one as the top portion and three in the tabbed pages. The top portion is the basic vehicle information. The first tab is the driveline as related to that vehicle. The second tab is the service specifications as they relate to that vehicle. And the third tab is where any work that we do to that vehicle is logged.

here it is...

 

[boblarson]

And I still don't see a PARTS table which is where you would get the parts from in the Driveline parts table and in the job details table.

 

[dgaletar]

OK, my relationships table looks just like yours does... now. VEHICLEID to VEHICLEID. Not CUAID... DONE. COMPANYID and DEPARTMENTID lines removed... DONE.

As for the Parts table... what??? You haven't mentioned a Parts table, yet.

 

[dgaletar]

[QUOTEwhich is where you would get the parts from in the Driveline parts table and in the job details table.[/QUOTE]

Do you mean the Driveline table and the ServiceSpecs table? Or JUST the Job Details table? Because the Driveline and the ServiceSpecs tables just hold specification information, right? The Job Details table holds the work being done.

And if that's the case, I do have a tblJobType & a tblJobDetails, which I would think feed the tblJobs.

 

[boblarson]

Okay, we're not coming together on this.

Do you have parts? I assume yes.

Do the specifications call out certain parts as belonging to a specific vehicle's makeup (I know as reference)?

Do Jobs use parts? Are they actually installed? If not, and no parts are used in the jobs, then we don't need the parts table for that.

But if they do use parts, then the parts should be in a parts table, the same parts table which contain the parts which are identified, for reference, in the service specs.

You show PartID in the Driveline Parts table (which I assume is the table which you would pull together the parts which should be in a specific driveline, as reference) but you have no parts table linked.

You also show JobPartID in the Job Details table. Where does the part come from? Shouldn't it be coming as a selection from the parts table?

I hope that clears up how I am seeing it. Maybe I am not seeing it correctly so let me know.

 

[dgaletar]

Do you have parts? I assume yes.

YES, WE DO HAVE PARTS. BUT WE DON'T COUNT OR INVENTORY THEM. AT LEAST WE ARE NOT SET UP TO DO THAT YET. THE PARTS, AT THIS POINT, ARE STRICTLY A REFERENCE FOR US TO KNOW HOW MANY WE USE, SO WE CAN 'GUESS' HOW MANY WE SHOULD KEEP ON HAND. (ie. we have 18 trucks that use oil filter #55316, so we will keep about 8 on hand.)

Do the specifications call out certain parts as belonging to a specific vehicle's makeup (I know as reference)?

YES, THEY DO. BUT JUST SO WE KNOW WHICH PART TO GRAB OFF OF THE SHELF TO PUT ON.

Do Jobs use parts? Are they actually installed? If not, and no parts are used in the jobs, then we don't need the parts table for that. But if they do use parts, then the parts should be in a parts table, the same parts table which contain the parts which are identified, for reference, in the service specs

NO. AT THIS POINT, UNLESS WE CHANGE THIS DATABASE TO TRACK OUR INVENTORY, JOBS DO NOT USE PARTS. THE JOBS SECTION IS STRICTLY FOR US TO BE ABLE TO LOOK BACK ON AS MECHANICS AND SEE WHAT WE FIXED BEFORE, LOOKING FOR REPEATING ISSUES, ECT.

You show PartID in the Driveline Parts table (which I assume is the table which you would pull together the parts which should be in a specific driveline, as reference) but you have no parts table linked.

I WAS JUST FOLLOWING YOUR LEAD ON THE TABLE LAYOUTS. WHATEVER YOU HAD IN THE ServiceSpecs TABLE, I PUT IN THE Driveline TABLE. REMEMBER, THE DRIVELINE SIMPLY CONSISTS OF THE TYPE OF ENGINE, THE TYPE OF TRANSMISSION, THE TYPE OF DRIVELINE (ie. 2WD, 4WD, ect.), TYPE OF BRAKES INSTALLED (ie. disk, drum, ect.), ANTI-BRAKE SYSTEM (yes or no), HOW MANY THE VEHICLE SEATS, THE WHEELBASE MEASUREMENT AND THE CURB WEIGHT.

You also show JobPartID in the Job Details table. Where does the part come from? Shouldn't it be coming as a selection from the parts table?

ACTUALLY, IN THAT ASPECT, JobPartID WAS BEING USED AS THE PART OF THE VEHICLE BEING WORKED ON (ie. engine, drivetrain, interior, etc.).

DOES ANY OF THIS HELP???

 

[dgaletar]

OK Bob, I've been thinking about this all day, & I have a question for you; taking into account what you know now about how we are using this database, and thinking along the lines of how you were originally setting up the "ServiceSpecs" table, could I set up the vehicles table like this:

VehicleID
CUAID
AlternateID
DepartmentID
DetailsID
SpecificationID
Notes

...in combination with a Details table like this:

DetailsID (Auto number)
DetailsName (Tag #, VIN #, Make, Model, Trim Level, Engine, Transmission, Brakes, Seating, ect.)
DetailsValue

...and a second table named Specification that looks something like this:

SpecificationID
SpecificationName (Tire Size, Oil Filter, Fuel Filter, Air Filter, Tire PSI, Wheel Torque, Type of Oil, Qty. of Oil, sect.)
SpecificationDetails

Your thoughts...

(I figured that if we took this thing in smaller steps, it might help us both. We can setup the Vehicle tables first, and worry about the "Jobs" aspect of it later.)

 

[boblarson]

You're close. DetailsID should not be in the vehicles table. The VehicleID goes into the Details table. Each detail record is a single attribute of the said vehicle.

A single detail record would be one item.

Now, because a specification could belong to more than one vehicle, you have it correct in that the specificationID goes in the vehicles table.

 

[dgaletar]

Bob, please proceed with caution. I may be on the verge of learning something here! lol

Now, a point of clarification:

While I believe that I really understand what you are saying about the "Specification" table, I am not as clear with the "Details" table.

A single detail record would be one item.

Obviously some of the fields (like the Tag #, VIN #, ect.) won't be duplicated. However, what about the fields that might be replicated in more than one vehicle, like an engine (several of our Ford vans use the same 5.4L V8 SOHC 16V), or brakes (which has the choice of either disk or drum)?

This is where the rubber hits the road. Your thoughts please...

Oh, and here is a new relationship report for your viewing pleasure. I have also included an Excel file that shows how many of the same vehicles use the same equipment, so that you may have a better reference of what I am talking about above.

 

[boblarson]

Now, a point of clarification:

While I believe that I really understand what you are saying about the "Specification" table, I am not as clear with the "Details" table.

Obviously some of the fields (like the Tag #, VIN #, ect.) won't be duplicated. However, what about the fields that might be replicated in more than one vehicle, like an engine (several of our Ford vans use the same 5.4L V8 SOHC 16V), or brakes (which has the choice of either disk or drum)?

Okay, first thing - I think you are still needing the Specification Details table. But we'll go there in a moment.

It matters not that the same things might be in more than one vehicle. That is why you have a Parts table. This is the source of all of the items which you will then assign to each vehicle in the vehicle details table. If it is a part it would come from the parts table. Tag# or VIN# will not come from anywhere except keying in but it is still a vehicle detail. The details table still looks off to me. I am thinking

tblVehicleDetailsTypes
VehicleDetailTypeID -Autonumber (PK)
VehicleTypeName - Text

and the entries would be just a list of the possible detail types (much like we went through with the specifications list way back when we started this).

example:

VehicleDetailTypeID       VehicleTypeName
    1                             TAG #
    2                             VIN #
    3                             Tires
    4                             Wheel Torque
    5                             Oil Type
    6                             Oil Qty
    7                             Brake Fluid
..etc.

Then in the VehicleDetails table:

tblVehicleDetails
VehicleDetailsID
VehicleID
VehicleDetailTypeID
VehicleDetailTypeValue

VehicleDetailsID    VehicleID     VehicleDetailTypeID     VehicleDetailTypeValue
     1                      1                    1                  VBD 284
     2                      1                    2                  XDSD9832300XSD
     3                      1                    3                  215/70R/14
     4                      1                    4                  45
     5                      1                    5                  5W30

And so on. Does that help?

 

[dgaletar]

OK. I got it! I'm off to do that now. See ya in a minute or two!!!

Oh, and by the way, I am not 100% sure that we even need the "Specifications" table. Couldn't that information be included into the Details table that I'm doing now?

Or, actually, if the statement above is correct, and if it's not too much to ask, can we switch the names Detail & Specifications? That way we would only have the "tblVehicleSpecificationsTypes" and the "tblVehicle" tables hosting all of this information.

 

[boblarson]

Or, actually, if the statement above is correct, and if it's not too much to ask, can we switch the names Detail & Specifications? That way we would only have the "tblVehicleSpecificationsTypes" and the "tblVehicle" tables hosting all of this information.

I think you just confused me again. LOL

I have to admit I don't know the answer to that question. It doesn't seem right to me though somehow.