MySQL Table design

[eluser]slowgary[/eluser]
Consider though that you may want to allow multiple items per listing, so matching keys wouldn't work for a primary.

I must say though, I'm not a fan of this structure. Each type of auto part will have so many different attributes that you're going to end up with lots of blank fields. Also, this method is not good for searching. Assuming there will be a search, what happens if a user searches for 'red'? Will the search need to check each table and each attribute field? Will the search know to skip the wheels table? I suppose you could always have the user select which categories to search in, but you'll still be querying fields that won't make sense for the query. For example, using the 'red' search against only just the auto parts table:

Code:

SELECT * FROM `parts`
WHERE
   `width` = 'red' OR
   `length` = 'red' OR
   `height` = 'red' OR
   `weight` = 'red' OR
   `color` = 'red' OR
   `type` = 'red' OR
   `size` = 'red' OR
   `mileage` = 'red' OR
   `manufacturer` = 'red' OR
   `model` = 'red' OR
   `year` = 'red' OR
   `price` = 'red' OR
   `quantity` = 'red' OR
   `liters` = 'red' OR
   `volts` = 'red' OR
   `cylinders` = 'red'

EDIT: I realize you can do a fulltext search, but AFAIK that won't check numeric fields and I'm sure it's still more overhead than just checking one field.

I'll admit I don't know much about car parts so that list is probably ridiculous, but I bet it's not too far from reality. With the EAV method you could event have a string_value and a numeric_value in your table, it would still be better than the alternative, not to mention more expandable in the future. You wouldn't need to change ANY code to expand the types of items you might add. Boat parts, anyone?

[eluser]peterbz[/eluser]
Well i guess it all comes down to the specifications of your requirements. For my case, I don't need too many attributes for auto parts; only 2 fields (description and price). As for searching, I'm not going to write my own search engine as this is a bad idea. I'm planning to implement Sphinx.

[eluser]slowgary[/eluser]
Wait a minute... if all you're planning on doing is description and price, what was the point of this thread at all? This should be as simple as an items table and a categories table.

[eluser]peterbz[/eluser]
That is only the case for auto parts. Cars and wheels have their own unique attributes (over 4). I made this thread to account for future changes I might have to make for each of these categories.

[eluser]slowgary[/eluser]
Then I still feel like it makes more sense to keep your items in the same table and separate the attributes. It will definitely prove to be more expandable in the long run.