I am creating a MySQL database of automobiles. You will find a variety of possible vehicle characteristics that I wish to be populated with a database, color, transmission, fuel, etc.
Wouldn't it seem sensible to produce a table for every group of characteristics or must i place them all-in-one table, then title each column exactly what the attribute is. Then I'd just list the potential options as rows.
Kinda a new comer to database design and desired to acquire some opinions. Thanks in advance!
If you have a set group of characteristics for every entity, place it in a single table. If you have different characteristics insert them in another table with this particular structure:
TABLE ( Primary Key ID Foreign Key Ref AttributeName AttributeValue )
The response to your question is dependent on whether each vehicle might have several "color" or "fuel" mounted on it.
Whether it was me, I'd result in the base tables seem like this:
Table: Automobiles - Cols: vehicleID, title, etc.
Table: Colors - Cols: colorID, title, rgbValue
Table: Fuel - Cols: fuelID, title, etc.
If each vehicle are only able to have one color and something fuel, then alter the Vehicle table to become something similar to:
Table: Automobiles - Cols: vehicleID, title, colorID, fuelID, etc.
If each vehicle might have 4 colors and three fuels, etc. Then leave the automobile table alone, and make "relationship" tables much like these:
Table: vehicle_has_color - Cols: vehicleID, colorID
Table: vehicle_has_fuel - Cols: vehicleID, fuelID
Hopefully this provides you a concept of ideal database design, and provides a good starting point from. Bear in mind when you simply have 3 different colors and they are not likely to change, it may be easier simply to place the colour title directly within the Vehicle table and take care of the choosing from the color within the PHP or Web coding.
In case your database must display historic changes, i quickly would opt for suggestion #2 (place them all-in-one table), but when you want to represent automobiles through the color, transmission, fuel, etc, then place the characteristics inside a separate table. Because then you've associations with ID amounts and when something must change, you are making that alternation in an attribute's table also it will get up-to-date everywhere. It is also better for consistency e.g. "29 mpg Hwy" versus "29mpg Hwy".