Lookup tables - Is there another way? 1

[simon373]

Hi,

Sorry if this sounds like a silly question, but:

Let's say I have the following information:

A product that can have many attributes like colour, weight, height. I want to be able to select each attribute from a dropdown list. Do I have to create a table for each attribute like:

ID Color
1 Green
2 Red
3 Blue

Then, using the dropdown list, lookup the value of the color ID and store it in the product table under color ID?

If so, I have lots of attributes and need to create a table for each. Or is there a better way of doing this because I am getting lots of tables!

Many thanks

Simon.

 

[Remou]

You could use a single table for attributes that do not change often and that do not need other fields. For example

[tt]tblAttributes:

ID AttributeType Attribute
1 Colour Red
2 Doors 1
13 Colour Blue
44 Days Monday[/tt]

There can be disadvantages to this method.

 

[johnherman]

Remou's suggestion of a generic Code Table has advantages if codes are rarely added or deleted, and if the code and description are all that matter. If codes are more volatile, then they belong in a separate table. If the code has additional attributes, like selected roll-ups, origins, or destinations.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[Remou]

johnherman
I think I said that already: "You could use a single table for attributes that do not change often and that do not need other fields."?

 

[johnherman]

I'm sorry for clarifying what I thought was unclear. Feel free to use either explanation in the future.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright