creating a database scheme for storing information about an item, which option is better?

Question

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))

Answer 1

Use your option 2.

• I have added an ERD showing 2 possible solutions (underlined columns are PKs). They are both Normalized to 3rd+ Normal Form. The first answer is not OK, because type information - the TypeName - appears in 2 tables. This is not following normalization rules.

• Note that in the first diagram, the Foreign Key from Item to Item_TypeColor_Assn is (TypeID, ColorID), while in the second diagram, the FK is the (SeqID) which of course references the (surrogate) Primary Key SeqID.
  Also note that in both diagrams, there is one Foreign key (and not two as the text in image falsely implies). It just happens that in first diagram, the Foreign key is compound (composite).

• In both diagrams/solutions, there is an FK (TypeID) from Item_TypeColor_Assn to table Type and one FK (ColorID) to table Color. The two solutions are identical in that aspect.

Edit 2

To answer your comments:

If you need to change a color combination for an item, you must delete a row from ItemColor_Assn table and re-insert a new row with the desired combination.

I have also updated the diagram so that the FK on item is nullable and won't cause a cascade delete on item when that happens. (this is a decision you have to make, whether that FK column needs to be nullable or not and whether the deletes are to be cascaded. Just offering an option)

As per your other suggestion, you can do that too, however, where would the color names come from, I mean the literal "Red" for example? If you want to have a color table containing the text and have the text repeated in the ASSN table, then this would be against normalization, however, is valid from the logical standpoint.

---

Unrelated to the design issue, here are my (@Emmad's) personal preferences for naming style:

• Use singular name for table names.

Explananion: use singular names because relationships are read per single occurence. So you'd say Each (item) has Color.

Answer 2

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.