database structure

[mbbout]

I have a question about the best structure in a database.

I'm setting up a car database. Users can select the desired car by the use of dropdown select boxes.

eg the desired car:
select car: renault
(in option select all renault cars)
select type: clio
(now in option select all colors of renault clio)
color: white

etc.....

what is the best way to set up my database and tables?

eg:
renault,clio,white
renault,clio,red
renault,espace,blue

[AstroTeg]

Here's a way I'd look at it: Keep repeating values in a seperate table. So...

You'd have a manufacture table:

Manufacture

manuID
manuName
[additional manufacture specific data can go here]

Model

modeID
modeName
manuID
[additional model specific data can go here]

Color

coloID
coloName

The manuID I put with the Model. Its assumed 1 model can only have 1 manufacture. But here, 1 model could have multiple colors available to select from. So we need a cross reference table to associate the models to the colors:

ModelColor

coloID
modeID

Now you can inner join Color with Model via the ModelColor table.

It might look like:

SELECT manuName, modeName, coloName FROM Model INNER JOIN Manufacture ON Manufacture.manuID = Model.manuID INNER JOIN ModelColor ON ModelColor.modeID = ModelColor.modeID INNER JOIN Color ON ModelColor.coloID = Color.coloID

• query untested

Its a bit ugly, but will give you flexibility and reduce the amount of data you're storing.

[programmer79]

mbbout,

I would be interested in hearing what you are looking to create. I developed a solution for car dealers and can probably assist in helping you get something working. I didn't go as far as having the color, but the mfg (ford, chevy, etc) are populated in a dropdown box and then when you select one of them it lists the models that are currently in stock for that particular mfg. You can see a working version of this at www.connexes.com/demo/search.php

You should search on "database normalization", as AstroTeg said you will want to keep repeating values in separate tables.

A normalized database in the beginning will save you from lost of pain and suffering down the road. Plan out all of the possibilites for the future and you will be fine.
Good Luck