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))
Best Answer
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
toItem_TypeColor_Assn
is(TypeID, ColorID)
, while in the second diagram, the FK is the(SeqID)
which of course references the (surrogate) Primary KeySeqID
.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)
fromItem_TypeColor_Assn
to tableType
and one FK(ColorID)
to tableColor
. 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:
Explananion: use singular names because relationships are read per single occurence. So you'd say Each (item) has Color.