Requesting info on Normalizing our Database (1 Viewer)

[dgaletar]

Sorry. But what I was saying is that there really isn't a difference (or at least there doesn't need to be) between what we are calling "Vehicle Details" and "Vehicle Specifications". I think that all of this time I have been separating them in my head because it was originally too much information to show on one page. So I fictitiously created two fields, Details and Specifications.

Does that make it any clearer?

 

[dgaletar]

Maybe what we do is to have the one MAIN table; for the basic information that we will be associating with each Vehicle, like Vehicle ID#, CUA#, Alternate ID, Which Department it belongs to, and any Links or PDF associated to that Vehicle. That table would be set up like this:

tblVehicle

VehicleID  -  Autonumber
  CUAnumber  -  Number
  AlternateID  -  Text
  Department  -  Lookup
  Links/PDFs  -  Hyperlink

Then we have two tables for the Vehicle Specifications/Details; for the actual Specifications and Details that we will be associating with each Vehicle, like Tag#, VIN#, Make, Model, Trim Level, Engine, Transmission, Brakes (including Front Brakes Type, Rear Brakes Type, Anti-Lock Brake System, & Brake Fluid), Driveline, Seating, Wheelbase & Curb Weight, Wheel Torque, Type of Oil, Quantity of Oil, Tire Pressures (Front & Rear), Snowplow & Spreader. Those tables would be set up like this:

tblVehicleSpecifications

VehicleSpecificationsID
VehicleID
VehicleSpecificationsTypeID
VehicleSpecificationsTypeValue

tblVehicleSpecificationsTypes

VehicleSpecificationsTypeID -Autonumber (PK)
VehicleSpecificationsTypeName – Text

Then we have two tables for the Vehicle Parts; for the actual Parts that we will be associating with each Vehicle, like the Oil Filters, Air Filters, Fuel Filters, Tires, Batteries, and Windshield Wipers.

tblVehicleParts

VehiclePartsID
VehicleID
VehiclePartsTypeID
VehiclePartsTypeValue

tblVehiclePartsTypes

VehiclePartsTypeID -Autonumber (PK)
VehiclePartsTypeName – Text

Does that help it to make any more sense to you? Am I even going in the right direction here with this???

 

[dgaletar]

And, if the above is correct, here is the Relationship diagram for it:

 

[boblarson]

I THINK it is correct the way you have shown it in the latest.

 

[dgaletar]

OK, I'm leaving for the day, but I will still be on the forums (via my cell). In the meantime, here is the database as it sits now. I entered all of the information for Vehicle #1 as a test.

Would you mind taking a look at it when you have a minute and letting me know what you think???

 

[Beetle]

Ummmm, sorry, but I feel compelled to throw a monkey wrench into this little project.

Now, admittedly, I haven't completely read every single post in this thread, but I have scanned through them all and I'm a bit unclear as to how we got to the point where your vehicle specifications table is structured as it is (or why it exists in the first place, based on looking at the values you're currently storing in there). Here is the table from your example file;

Looking at the values you have in your VehicleSpecificationsType column, I've been in the automotive industry for more than 25 years and I have NEVER seen a vehicle where pretty much EVERY ONE of those values didn't apply. What I'm saying is, unless some of your vehicles are bicycles, these are not optional characteristics. These are hard attributes of a vehicle, and therefore should be fields in the Vehicles table. The way it's structured now, you're forcing yourself (or your users) to first select the attribute (which you will have to do every time for every vehicle that you add) and then enter the value for that attribute. It's not as if some vehicles might not have a VIN, or a Make, or an Engine, etc., so it should not be structured in a way that forces you to actually have to select those attributes for every vehicle.

As far as the actual values for some of these attributes, it wouldn't be a bad idea to have several small "lookup" tables for values that are often repeated. This is common because it speeds up data entry and, more importantly, it ensures better data integrity. This wouldn't apply to something like a VIN, because that is unique to every vehicle, but let's take the example of oil viscosity. Every vehicle is going to have a recommended viscosity, so it's going to need to be entered every time, but if you let users enter it manually you're going to end up with inconsistent values in that column of your table;

5/30
5W30
5W / 30
5-30
5W-30

Then, when you want to query for all vehicles that take 5W/30 you're going to have to query for multiple possible values. On the other hand, if you have a small table to store the viscosities;

tblOilTypes
TypeID (Autonumber PK)
Description (0W/20, 5W/30, 10W/30, etc.)

and use a combo box on your form for the user to select the appropriate viscosity (the combo box would store the PK value [TypeID] in the related table, not the Description), you ensure better data integrity and consistency. This approach would apply some of the other attributes as well (Make, Driveline, etc.), anywhere that you would anticipate having to repeatedly enter the same limited set of values in a field.

While we're on the subject of combo boxes, you should not be using these at the table level. These are commonly referred to as Lookup Fields at the table level and can cause problems. See here for more on that. Combo boxes in forms are appropriate, combo boxes (lookup fields) in tables are not.

 

[boblarson]

What I'm saying is, unless some of your vehicles are bicycles, these are not optional characteristics. These are hard attributes of a vehicle, and therefore should be fields in the Vehicles table.

That was my doing. And there is a method to the madness.

The way it's structured now, you're forcing yourself (or your users) to first select the attribute (which you will have to do every time for every vehicle that you add) and then enter the value for that attribute. It's not as if some vehicles might not have a VIN, or a Make, or an Engine, etc., so it should not be structured in a way that forces you to actually have to select those attributes for every vehicle.

It is easy to add all of the standard attributes to the vehicle with a small bit of code.

The number of attributes leads to a large number of columns, even though they may be mostly standard on all vehicles. I prefer to work with rows than 60 columns. Hence, why I pointed them in this direction.

 

[Beetle]

Fair enough Bob. I wouldn't necessarily consider 15 - 20 an overly large number of columns, but I'll defer to your expertise here. I wouldn't presume to question the brain inside your large, yellow, googly-eyed head.

 

[boblarson]

Feel free to question me as I don't have any illusions that I don't make mistakes. I may be wrong here.

 

[dgaletar]

Either way guys, I'm ready for the next step!!!

 

[boblarson]

Either way guys, I'm ready for the next step!!!

I would build the form for the details (it will become a subform later). Then build the form for the next level up.

 

[dgaletar]

ooops... I kinda did that while I was waiting. lol

Would you mind taking a look & letting me know if I'm still on track here???

 

[dgaletar]

They don't look like I want the end product to look, but I'll do that stuff later. I'm more concerned about having it work the way you intended it to.

 

[Beetle]

While you're still at the table design level, here is another question, or maybe just food for thought. Do you ever have vehicles move from one department to another? If so, do you have any need to track the history of the time periods in which a vehicle belonged to a given department?

 

[boblarson]

Yep, looking good. As for formatting and stuff, there is a lot you can do including use of Tab controls to organize a lot in a smaller space.

 

[dgaletar]

Good question, Bob. Yes, we do have vehicles change departments once in a while. But no, we don't need to track them.

 

[boblarson]

Good question, Bob. Yes, we do have vehicles change departments once in a while. But no, we don't need to track them.

That was Sean who asked that.

 

[dgaletar]

oooops! Sorry you too!

 

[dgaletar]

Did you look at the file? Are the tables working like you intended them to?

 

[boblarson]

Did you look at the file? Are the tables working like you intended them to?

commented just above:

Yep, looking good. As for formatting and stuff, there is a lot you can do including use of Tab controls to organize a lot in a smaller space.