creating a database scheme for storing information about an item, which option is better? I have a database which stores information about various items. In particular, for each item I need to store the name, type and color. Each type can have a various amount of different colors. For example, I need to be able to ask 'what color can an item of type A have?'

I'm struggling to see how I should best structure my database. Each item has a color and a type, but I also need to capture the fact that each type has a unique range of colors available. 

currently I have these ideas:

FIRST OPTION

items            (ID(PK), name, types_and_colors_ID (FK))
types and colors (ID(PK), type_name (FK), color(FK))
types            (ID(PK), type_name)
colors           (ID(PK), color_name)


SECOND OPTION

items            (ID(PK), name, type(FK), color(FK))
types and colors (type (PK FK), color (PK FK))
types            (ID(PK), type_name)
colors           (ID(PK), color_name)


THIRD OPTION

items            (ID(PK), name, type_color_id(FK))
types            (ID(PK), type_name))
type_colors      (type_color_id, color_name (PK), type_id (PK))
With the first version the schema design itself forbids having invalid type-color pairs, so I would choose that one.  With the second version you will need an extra mechanism (a trigger for example) to maintain valid state of the items.

From a querying point of view, it means one more JOIN on quite simple tables, I would not expect that causing performance or maintainability problems.

EDIT

Well, I finally realized what could Emmad mean on 'The first answer is not OK, because type information appears in 2 tables' - I silently considered type_name in your line


  types and colors (ID(PK), type_name (FK), color(FK))


a typo, and a foreign key on types.ID instead of name.  With this modification my suggestion is quite the same as Emmad's second diagram.