How to represent many similar attributes of an entity in a database?

Question

Let's say I'm building a website about cars. The car entity has a lot of enum-like attributes:

• transmission (manual/automatic)
• fuel (gasoline/diesel/bioethanol/electric)
• body style (coupe/sedan/convertible/...)
• air conditioning (none/simple/dual-zone)
• exterior color (black/white/gray/blue/green/...)
• interior color (black/white/gray/blue/green/...)
• etc.

The list of these attributes is likely to change in the future. What is the optimal way to model them in the database? I can think of the following options but can't really decide:

• use fields in the car table with enum values
  • hard to add more columns later, probably the fastest
• use fields in the car table that are foreign keys referencing a lookup table
  • hard to add more colums later, somewhat slower
• create separate tables for each of those attributes that store the possible values and another table to store the connection between the car and the attribute value
  • easy to add more possible values later, even slower, seems to be too complicated

Answer 1

Depending upon the number of queries and size of the databases you could either:

1. Make wide tables
2. Make an attibutes table and a car_attributes table where: cars -> car_attributes -> attributes

#1 will make faster, easier queries due to less joins, but #2 is more flexible