Database design for lots of possible attributes

I'm building a MySQL database of vehicles. There are many different possible vehicle attributes that I want to be populated by a database, color, transmission, fuel, etc.

Would it make sense to create a table for each set of attributes or should I put them all in one table, then 开发者_C百科name each column what the attribute is. Then I'd just list the possible choices as rows.

Kinda new to database design and wanted to get some opinions. Thank you ahead of time!

The answer to your question depends on whether each vehicle can have more than one "color" or "fuel" attached to it.

If it was me, I would make the base tables look like this:

Table: Vehicles - Cols: vehicleID, name, etc.

Table: Colors - Cols: colorID, name, rgbValue

Table: Fuel - Cols: fuelID, name, etc.

If each vehicle can only have one color and one fuel, then change the Vehicle table to be something like:

Table: Vehicles - Cols: vehicleID, name, colorID, fuelID, etc.

If each vehicle could have 4 colors and 3 fuels, etc. Then leave the Vehicle table alone, and create "relationship" tables similar to these:

Table: vehicle_has_color - Cols: vehicleID, colorID

Table: vehicle_has_fuel - Cols: vehicleID, fuelID

Hopefully this gives you an idea of ideal database design, and gives you a good place to start from. Keep in mind that if you only have 3 different colors and they're not going to change, it might be simpler for you just to insert the color name directly in the Vehicle table and handle the selecting of the color in the PHP or HTML code.

When you have a fixed set of attributes for each entity, put it in one table. When you have varying attributes put them in a separate table with this structure:

TABLE (
  Primary Key ID
  Foreign Key Ref
  AttributeName
  AttributeValue
)

If your database needs to display historic changes, then I would go with suggestion #2 (put them all in one table), but if you just want to represent vehicles by the color, transmission, fuel, etc, then put the attributes in a separate table. Because then you have relationships with ID numbers and if a value needs to change, you make that change in an attribute's table and it gets updated everywhere. It's also better for consistency e.g. "29 mpg Hwy" vs "29mpg Hwy".

Take a look at these SO examples: one, two, three.