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:


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)


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)


items            (ID(PK), name, type_color_id(FK))
types            (ID(PK), type_name))
type_colors      (type_color_id, color_name (PK), type_id (PK))
  • are the colors and types fixed, or will you be adding types and colors? – swasheck Jul 3 '12 at 15:11

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.

enter image description here

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.

  • You have TypeID(FK), ColorID(FK) in table Itembut you don't explain if that is one compound FK or two FKs (to Item_TypeColor_Assn) – ypercubeᵀᴹ Jul 2 '12 at 18:50
  • When you create a FK constraint on a compound key, you get 2 separate keys appearing in the child. Yes they are separate keys representing 1 compound PK (not 2 separate FKs) because the parent table has the 2 keys as part of 1 Primary key. – NoChance Jul 2 '12 at 18:55
  • Thanks for the detailed answer. One question I have is what would happen in the following situation. Let's say two types have the same color, yellow. What If I later decided that I wanted to change the color of a type from yellow to dark yellow? It seems like it might be rather tricky – roger smith Jul 3 '12 at 7:53
  • What if I instead moved the color_name to the item_TypeColor_Assn table – roger smith Jul 3 '12 at 7:59
  • Still the phrase "... so that you don't have two FKs in Item" is confusing. – ypercubeᵀᴹ Jul 3 '12 at 10:41

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.


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.