[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 492
  • Last Modified:

MySQL schema question

I'm creating a simple schema with vehicle information.

Each vehicle has a make/model/trim.  Currently I have a vehicles table with a vehicle_id as my primary key.
I also have a makes table with make_id and vehicle_id as primary keys with make_name as an attribute.
The models table with make_id, vehicle_id and model_id as primary keys with model_name as an attribute.

My question is how do I create a schema that correlates all three in the vehicle table for when I pull information? Users will have the option to add makes/models without attaching a vehicle to them, so that makes me think that adding a vehicle_id as an attribute to the makes/model tables won't work. I will need to populate them from the db, then probably pick the selected ones and put them into the vehicle database.

Any suggestions?
0
stevefNYC
Asked:
stevefNYC
  • 4
  • 3
2 Solutions
 
joesthebighmoeCommented:
Something is not adding up.
I am not sure why the Makes table has a Vehicle ID.
Also, can there be two Vehicles with the same make, model and Trim, or do the three of those together identify a vehicle?
Also, what is the differnce between defining a Trim and Defining a Vehicle?
If I say a Honda Accord can have a DX trim...did I just define a trim level, or vehicle?

Here's what I suggest (based on my assumptions, you can correct my assumptions and I will help you with a different design).
A Vehicle is Unique by the fact that it is for one Make (Honda) one Model (Accord) and One Trim Level (DX). Thus the primary key on the Vehicle Table is MakeID + ModelID + TrimID
A Model is unique for a given Make (thus a Honda could have a model called Z and so could Nissan). Thus the Model Tables primary Key is Make ID + Model ID
A Trim is unique based on Make (Honda) and Trim (DX). This is because Accords might use DX and Civics. However we don't want to confuse Honda's DX trim with Ford's. So the Trim table's primary Key is MakeID + TrimID.
Make Table
-MakeID  (pk)
-Make Name
Model Table
-Make ID (pk)
-Model ID (pk)
-Model Name
Trim Table
-Make ID (pk)
-Model ID (pk)
-Trim ID (pk)
-Trim Name
Vehicle Table
-Make ID (pk)
-Model ID (pk)
-Trim ID (pk)
-Vehicle Name

So a user can add makes all they want.
When they want to add a model they have to specify the Make first since its ID goes with the Model.
0
 
stevefNYCAuthor Commented:
Hi joe. :-)

Here's a current quick hack I put together: http://www.phpgeek.org/f1auto/f1auto_db.htm (it's certainly incorrect.)

A vehicle is identified unique by vehicle_id and VIN (vehicle identification number).

Any amount of vehicles can have the same make/model/trim.

The issue here is creating a place to store makes/model/trims. A user is going to ultimately have to select a make select box, which will populate all the models available for that make. Then depending on make+model selected, a specific Trim can be picked.

So the database can have infinite amount of Honda(make) Civic(model) DX(trim), however, you can't have a Honda(make) Sentra(model). I need a separate DB where users can input makes/models/trims. Model will be not selectable until make has been selected, and trim won't be selectable until make/model has been selected.

I hope that makes sense?
0
 
dqmqCommented:
>Currently I have a vehicles table with a vehicle_id as my primary key.

Good

>I also have a makes table with make_id and vehicle_id as primary keys with make_name as an attribute.

Wrong.  Vehicle_ID should not be in the primary key. In fact, it should not even be in the table.

>The models table with make_id, vehicle_id and model_id as primary keys with model_name as an attribute.

Wrong.  Models table should not have vehicle_id, either.  You can do the PK as Make_ID+Model_ID.

>Users will have the option to add makes/models without attaching a vehicle to them, so that makes me think that adding a vehicle_id as an attribute to the makes/model tables won't work.

That's true, but the more important reason is that it means make/model can only apply to one vehicle..  

>My question is how do I create a schema that correlates all three in the vehicle table for when I pull information?

Your vehicles table should contain the Primary Key of the model table as a foreign key (that would be MakeID+ModelID).  

Now, each vehicle is assoicated with a make and model.  

Trim can be modelled different ways, depending on whether it's independent from model.  Also, depending on how many trims a vehicle or model can have.

If any model can have any trim, then they are independent. In that case, I recommend a VehicleTrim table with VehicleID(Fk to Vehicle table)+TrimID(FK to Trim table) as the PK.  This permits any model of any vehicle to have 0 to many trims without restriction.

If some trims apply to some models only, then it gets more complicated.  Then, you need a ModelTrim table to show which models and trims go together. That table would have MakeID, ModelID TrimID as the FK.  That table represents all of the trims that are possible for a model.  You still need the VehicleTrim table, but in this case it has a FK to the ModelTrim table instead of the Trim table.

 






Besides the Trim table, my recommendation is you have another table for VechicleTrim.  The PrimaryKey is VehicleID, TrimID with VehicleID a FK to the VehicleTable and TrimID a FK to the Trim table.  This permits a vehicle to have

Now, each vehicle





 



You need a table to store the Customer's choice:



I will need to populate them from the db, then probably pick the selected ones and put them into the vehicle database.




0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
dqmqCommented:
Drat, ignore the garbage at the end.  My last paragraph should have read:

If some trims apply to some models only, then it gets more complicated.  Then, you need a ModelTrim table to show which models and trims go together. That table would have MakeID(FK to Model), ModelID(FK to Model), TrimID(FK to Trim) as the PK.  That table represents all of the trims that are possible for a model.  You still need the VehicleTrim table, but in this case it has a FK to the ModelTrim table instead of the Trim table.  This permits a vehicle to have 0 to many trims from the list of trims that are allowed for the model.  

If a vehicle always has exactly one trim, then you can omit the VehicleTrim table and put the foreign key I described for it directly in the Vehicle table.


 
0
 
stevefNYCAuthor Commented:
dqmq:


Thanks also for the reply.

Trim is required and depends on the selections made by make/model. I resdesigned my schema if you refresh the page I put in above. (http://www.phpgeek.org/f1auto/f1auto_db.htm)

The only issue is MySQL lacks foreign keys ... so I can just put them in as required keys. I think this design might work better?

Then when the user stores information, it'll put the make/model/trim into the vehicles table according to their name.
0
 
dqmqCommented:
>The only issue is MySQL lacks foreign keys ...
I've never used MySQL, but I'll bet my first born it has foreign keys.

>Then when the user stores information, it'll put the make/model/trim into the vehicles table according to their name.

No, do NOT store make/model by name. Store MakeID and ModelID and TrimID.

Also, the way you're designed is fine if a trim cannot apply to more than one model.  In other words, you have a one-to-many between model and trim. However, if a trim applies to more than one model, that's a many-to-many relationship and you need another table to represent it.  In that case, I would suggest:

Trim
------
TrimID(PK)
TrimName

ModelTrim
------------
ModelID(PK,FK to Model)
TrimID(PK,FK to Trim)

Model
---------
ModelID(PK)
MakeID(PK,FK to Make)




   

   


0
 
stevefNYCAuthor Commented:
My most up to date schema can be found here, how's this look? http://shell.vaerchi.com/~steve/db.png
0
 
dqmqCommented:
You're getting there.

The most important change you can make is to change make_name, model_name, and trim_name to make_id, model_id and trim_id in the vehicles table.

The second most important change is to enforce valid combinations of those ID's using foreign key constraints.  The vehicle table should have a foreign key to the trims table, using all three of the ID columns.

Third, and I don't want to belabor the point, but your design represents this:

a make has 1 to many models (good)
a model belongs to one make (good)
a model has 1 to many trims (good)
a trim belongs to one model (not so sure)

If you are sure about the last item, then that part of the design is fine. However, if a trim can belong to more than one model, then you need another table.  Either way, personally, I would rename your Trims table to ModelTrims because that is what it represents: the trims for a model.  Then, if eventually, you are so inclined, you can add the Trims table to complete a many-to-many between Trims and Models.

Finally, this set of tables:
  Vehicles
  Photos
  Options
  Makes
  Models
  Trims
  ModelTrims (assuming you have it)

describe a vehicle completely independent from anything to do with customers. When you're ready to associate customers with vehicles you will need at least a CustomerVehicle table (with foreign keys) to represent (and enforce) that relationship.  Don't be alarmed, but very likely, even that's an oversimplification and you will need other tables, as well, to really represent your business.

 





0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now