table structure question

[bazz]

Hi,

I will have three tables as listed below. I ask for your help on how to relate each of them. I think it may be ok to do it with a many to many table with three fields but I need advice on it from you, more experienced, people.

tbl_make
| MakeID | Make of car|

tbl_model
| ModelID | Model Name|

tbl_colour
| ColourID | Colour Name |

I am trying to populate a triple combo though its not absolutely necessary that I do it that way. The key point is that I need to relate tbl_2 with tbl_1 as well as with tbl_3. The makes and models seem straightforward (by using a many-to-many table), because no two makes have the same model name. But, the colours are not so consistent across models and makes because 'mars red' could be used on more than one model and more than one make.

it could be like this :

Ford -> Mondeo -> Mars Red
Ford -> Taurus -> Mars Red

Opel -> Vectra -> Mars red
Opel -> Vectra -> Pale blue

What I am trying to show is that the values in tbl_3 (color's names), can be used by more than one of the values of tbl_2.

I have thought about doing it this way:

tbl_makes_models_colours
| MakeID | ModelID | ColourID |



Bazz

[oracleguy]

That is how I would do it. That way you can easily select any sort of information involving those three variables. In fact I have had to do this vary thing before and I think that is how I did it.

[bazz]

Thanks Oracleguy. :thumbsup:

It is good to feel re-assured.

bazz