Multiple foriegn keys and search features

[rossini]

HI,

 

I am currently building a site for alloy wheels and this will have a wheel finder based on the size of wheel, make and model of the car. Each wheel is going to need a multitude of different cars and models assosiated with it, that will be the criteria on what is drawn in from the database.

 

I am thinking using a multiple table system would be best but as I am new to this I am wondering if you could link all the foreign keys needed into one table column ie:

 

cars = 15 - 20 foriegn keys from the cars table

model = 20 - 30 foriegn keys from the model table

 

Also if done this way would a search feature read this correctly?

 

If anyone one can shed any light on this or suggest a better way I would be very grateful.

 

Thanks in advance

[Mchl]

You don't need dozens of foreign keys to link two tables. Just one.

 

table models

modelID, model

----------

1, Ford T

2, Ford Mustang

3, Ford Crown Victoria

 

table cars

carID, modelID

---------------

1 , 3

2,  3

3,  2

 

So you have three cars, two of them (1,2) are Crown Victoria, and one (3) is Mustang. Lucklily for you, you don't have to look for spare parts for T.

 

 

[rossini]

Hi,

 

Thanks for such a quick reply, Im not sure if I explained what I am trying to achieve properly (apologies).

 

I would be trying to acheive somthing more along the lines of:

 

Cars Table:

Car_id / Car-name

1, Toyota

2, Ford

3, Nissan

 

Wheels table:

Wheel id / Car Id

1, 1,2,3

 

So that a search feature could find that wheel a a wheel that would fit all 3 models?

 

I hope this make it clearer, im not sure if it is possible to do it in this way or if I would have to try and do it another way.

 

[Mchl]

You will need three tables all together

Cars Table:

Car_id / Car-name

1, Toyota

2, Ford

3, Nissan

 

Wheels table:

Wheel id, wheel name?

1, I don't know how wheels are called

 

wheelsToCars

wheelID, carID

1 ,1

1, 2

1, 3

[rossini]

Thanks,

 

So to throw in the models I would also need:

 

Models table

 

Models to cars table

 

?

 

[Mchl]

Nope. Usually one car is one model (unless you have a hybrid of Toyota and Hummer in your garage), so it is enough to store modelID with each car.

 

 

[rossini]

That would be an ineteresting hybrid! Of course how daft of me, Thanks!