Categorizing data

[-Felix-]

Lets say I have the details of 500 cars stored in mysql database.

What would be the best way to categorize these so that I can easily print a list of all the "blue cars" and all the cars with "airconditioning" for example?

Assuming that these are all in one table, the best way would probably to add a field that indicates what the color of the car is and if it has aircondition.

 

Ok, but what if one car has both qualities? What would be the best way to do this?

 

[neylitalo]

I would suggest adding a field for every possible "extra" in the car, and set it to 0 or 1 depending on if it has the feature.

[effigy]

cars
====
id
make
model

features
========
id
name

cars_features_xref
==================
car_id
feature_id

[-Felix-]

umm...thanks for the replies guys, but you lost me lol

...Im using a web based admin tool to store mysql data.

I guess Im really a beginner. could you explain a little more specifically what I should do? I can add a field, and then choose a type for it, default values etc., but I dont know what type would be best in this case when same car can have multiple qualities.

[ryanlwh]

the easiest way to go would be adding a field for each feature like neylitalo suggested:

 

table Car
============
id
manufacturer
make
year
aircondition (1 or 0, use tinyint or enum)
color (use enum or make it varchar)
doors (integer)

 

A more dynamic way is suggested by the above poster... but maybe too complex for a beginner...

[effigy]

cars

====

id

make

model

 

example:

 

1 volkswagen jetta
2 volkswagen passat

 

features

========

id

name

 

example:

 

1 a/c
2 leather seats
3 sun roof

 

cars_features_xref

==================

car_id

feature_id

 

example:

 

1 1 (this means the jetta has a/c)
1 3 (this means the jetta (also) has a sun roof)
2 1 (this means the passat has a/c)
2 2 (this means the passat (also) has leather seats)

 

SQL: (i think this is right; not tested)

 

[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']select[/span] c.id, c.make, c.model, f.name from cars c, features f, cars_features_xref x where c.id=x.car_id and x.feature_id=f.id [!--sql2--][/div][!--sql3--]