Database Normalization

programmer79

I am designing a database for a car dealer and I'm trying to get it normalized to the 3rd normal form. I would appreciate any comments or suggestions.

Thank you.

Database Structure

barand

A manufacturer has many models but a model has one manufacturer, therefore the relationship between manufacturer and model is one to many, and not many to many. So link model directly to manufacturer via mfg_id in model table.

Assuming vin_id is for an individual vehicle (?) a vehicle can have many features so this is a many to many. Have a link table between vehicle and feature.

hth

programmer79

I got the first part ok, but I was unsure of the second part. I have a vehicle table and a feature table. You are saying vin_id in the vehicle table will be linked to vehicle_feature(new table) via vin_id and vehicle_feature linked to feature via feature_id?

Thank you for your help!

barand

Assuming vin_id is the unique id for a vehicle in detail table

detail                  veh_features                    features
-------                   ------------------                 -----------

vin_id----------------vin_id
etc                         feature_id------------------feature_id
                                                                    description

A vehicle can have many features. A feature can be on many vehicles (many to many)

hth

Edit : Another assumption of mine is that feature is something like Sunroof, aircon, alloys, fluffy dice etc.

programmer79

Well I ran across a problem with the Mfg/Model issue. I found a model that has two different Manufacturers. Would I need to go back to the many to many relationship because of this data?

Thanks,

Tony

barand

Is that 2 maunufacturers who both happen to have models with the same name, or a joint build like body by Rolls-Royce, engine by BMW?

DeltaRho2K

Normalization is hard to visualize... We can all appreciate that.

This article (found here at the phpBuilder website) is a good start to beginning to understand how the normalization will apply in your case.

DB Normalization

The best thing to do, as this article illustrates, is to figure out what data can possibly be duplicated, and normalize from there...

(In my opinion, two manufacturers who manufacture a vehicle with the same name doesn't necessarily mean this becomes a many to many relationship, because as you research, you'll find that it is a rare enough instance, and they can be identified separately by one way or another. In this case, it is rare enough that it will be more of a headache to try to relate them in a many to many relationship) That's just my opinion though.

The first step is to understand what type of normalization you need, and you are heading in the right direction... Good luck with this.

programmer79

Well, here it is. I agree that the same model for two different mfg's is a rarity, but I wanted to make sure this was done right. Just in case I had to add that same model for two different makes, I didn't want my db to crash. Please see the attached revised ERD. I think Im getting close to the end, but wanted some approval from the masterminds. Any help is greatly appreciated.

Thanks,

Tony

programmer79

I didn't receive any comments on the last post. I didn't know if it may have been because people don't like downloading zip files, so here is the link to the word doc with the most recent normalization. I'm just interested in finding out if I have broken these tables down into a manageable solution that will not break in the near future. Thank you for all of the help!

http://www.expertcellular.com/car_table_revised.doc

Tony

designguy79

Looks good, in my opinion...

One suggestion would be to "standardize" your naming somewhat. For example, it may be easy to confuse a table with a row when coming up with the query. I always name the tables starting with 'tbl_', and keep the main key for each table as 'id'. Then, all the foreign keys are named table_name_id. Believe me, naming your tables, rows, indexes, and keys is very critical to saving headaches down the road.

I would be more than happy to look over your structure again later, if you feel like taking my advice. 🆒