2

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 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!

timroman
  • 1,384
  • 1
  • 10
  • 18

4 Answers4

2

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.

GarrettNow
  • 139
  • 1
  • 7
  • Yes, each vehicle will only have one attribute selected. – timroman Jan 13 '11 at 23:00
  • OK, I'm going to do a really small table for each attribute. Should I include a primary and foreign key for each option? For example: Table -> att_color: index, fkey, colorname Thanks! – timroman Jan 14 '11 at 02:05
  • I would put the foreign key in the vehicle table. Think of the attribute tables as a list to select from, and then on the main vehicle table you're using a foreign key to point to the list and say "I want that one". – GarrettNow Jan 16 '11 at 03:34
  • Most concise explanation about relationship ever heard. After years of reading confusing articles about design of tables and relationship, I needed an abstract explanation like yours. Thank you a lot @GarrettGriffin – quantme Oct 11 '13 at 14:49
1

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
)
Jan
  • 15,802
  • 5
  • 35
  • 59
0

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".

NightHawk
  • 3,633
  • 8
  • 37
  • 56
  • No, historical changes to the options aren't important. I agree with the consistency, and this is why I'm doing it from the database. – timroman Jan 13 '11 at 23:01
0

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

Community
  • 1
  • 1
Damir Sudarevic
  • 21,891
  • 3
  • 47
  • 71