MySQL Table design

[eluser]slowgary[/eluser]
Jedd is a normalization junkie, I would take his opinion very seriously. It makes sense to me to do this:

Code:

items
-----------------------------
| item_id | item_name       |
-----------------------------
| 1       | 96 honda        |
| 2       | goodyear tires  |
| 3       | mustang fender  |
-----------------------------

attributes
-----------------------------
| attr_id | attr_name       |
-----------------------------
| 1       | mileage         |
| 2       | size            |
| 3       | color           |
-----------------------------

values
-------------------------------------
| item_id | attr_id | value         |
-------------------------------------
| 1       | 1       | 74962         |
| 1       | 3       | red           |
| 2       | 2       | 16 inches     |
-------------------------------------

It's the only way you'll really be able to expand the items that you can store without having to add database tables and write new code. Also some of the attributes will surely be cross-item, like weight for instance. So you could search by a similar attribute like "SELECT * WHERE `weight` < '50lbs'" and you wouldn't have to write code to figure out if the item has a weight field or not. It'd be a nightmare any other way.

The frontend would be more consistent this way too, as the user would always use the same open ended interface...maybe just a dropdown of attributes next to a blank input and an 'add' button. You could use JavaScript to build a list (each with an 'x' button for deletion), then when the user is satisfied with the info they'd save the item.