MySQL Table Design for CMS

[peterbz]

I have a car site that allows users to post different information about used cars, wheels, auto parts, etc.

 

Now each of these categories also posses sub categories.  For example, used cars have different makes such as Honda, GM, etc. Wheels are divided into its sizes, etc.

 

However, each of the categories (used cars, wheels, auto parts, etc) have different attributes.  For example, used cars will have kilometers driven, price, transmission, etc.

 

Now the question is, should i divide used cars, wheels, and auto parts into separate databases or should they all be on the same database with a category id?  The only problem is that each of them have their own unique attributes (used cars have transmission, wheels have size, etc)

 

 

Version 1

 

TABLE: Used Cars

 

TABLE: Wheels

 

TABLE: Auto Parts

 

TABLE: UsedCarCategories

 

TABLE: WheelsCategories

 

TABLE: AutoPartsCategories

 

 

Version 2

 

TABLE: Listings

 

TABLE: Categories

 

 

Which version is better?  The reason for Version 2 is because more different categories may be moved around or added in the future.

[corbin]

You could also have an attributes table ;p.

[peterbz]

You could also have an attributes table ;p.

But wouldn't attributes table slow down my queries significantly?  Also, the segments of joins is a nightmare.

[corbin]

Eh, not humanely significantly.

 

 

It really all comes down to how much you want to normalize your DB.

 

 

http://www.phpfreaks.com/forums/index.php/topic,126097.0.html

http://www.phpfreaks.com/forums/index.php/topic,210842.0.html

 

 

The second link might not apply much, and it's been a while since I read the stuff linked in that first thread, but if I remember right, it should explain a little.

[peterbz]

I just read some of the links above.

 

Apparently, on the slides, it frowns upon EAV (Entity Attribute Value) design, meaning having an attribute table.  However, it suggests to have multiple tables instead of EAV.

 

My question is: is EAV really that bad?  Is it a lot more expensive to have an attributes table as each call to a particular item will require making a few more table calls.  However, having separate tables seem somewhat impractical and harder to maintain.

[corbin]

Haha kind of ironic that the thing I linked to suggested to not do what I suggested doing.

 

 

I must say, after reading it, it does make more sense to use columns instead of rows and use ALTER TABLE statements to add attribute columns.

 

 

But, that would of course require different tables for different types of things.

 

 

 

So, it really comes down to this:  Will you have a set of things, or will things be dynamic?  For example, do you know for sure you will have fruits and vegetables, or will new categories be added frequently or by users?

 

 

I must say, I would still stick with EAV.  Makes more sense to me in this situation, but I'm not quite sure what your data will look like.