CODEWITHSUNDEEP
mysqldatabase-designforeign-keysmodeling
avrono
avrono

Reputation: 1680

Relational Database design foreign keys

I have tables types which has many grades and many colors. I have put a foreign key in grades , type_id and the same in colors. I have a third table called item which has a grade and a color.

My question is, how do I ensure the integrity of the data from item back to types.

For example:

Type:
     0 sometype1
     1 sometype2

grades:
     0 somegrade 0 (points to sometype1)

color:
     0 red 0 (points to sometype1)

item:
     0 item1 0 0 (points to somegrade, red - which points to   sometype1)

How do I ensure that a situation does not arise where the color and grade are correct but the type is not ?

Also what if the one of the foreign keys references the correct type and the other not , etc etc ?

It seems to me, there must be a better way to model this, can anyone help?

Upvotes: 0

Views: 178

Answers (2)

TommCatt
TommCatt

Reputation: 5636

If Item can be a certain Type and Type can have one or more Colors and one or more Grades, then the FK reference of the Items table may not necessarily go directly to the Types table. If the Item gets the color and grade only of its particular type, it does not directly refer to color or grade.

create table Colors(
    ID     int primary key,
    <ColorInfo>
);
create table Grades(
    ID     int primary key,
    <GradeInfo>
);

If each Type can have several combinations of color and grade, the references must be moved to an intersection table.

create table TypeColorGrade(
    TypeID    int,
    ColorID   int,
    GradeID   int,
    constraint PK_TypeColorGrade primary key( TypeID, ColorID, GradeID ),
    constraint FK_TypeColorGrade_Type foreign key( TypeID )
        references Types( ID ),
    constraint FK_TypeColorGrade_Color foreign key( ColorID )
        references Colors( ID ),
    constraint FK_TypeColorGrade_Grade foreign key( GradeID )
        references Grades( ID )
);

Every valid combination of color and grade a type can have is represented here. Thus, the Items table can only reference the Type with a defined grade and color.

create table Items(
    ID       int,
    TypeID   int,
    ColorID   int,
    GradeID   int,
    constraint PK_Items primary key( ID ),
    constraint FK_Item_Type foreign key( TypeID, ColorID, GradeID )
        references TypeColorGrade( TypeID, ColorID, GradeID )
);

The foreign key is not directed to the Types table but to the Type/Color/Grade combination. Any attempt to insert an item of a particular type with a color or grade that is not defined for that type will fail.

Upvotes: 1

Uueerdo
Uueerdo

Reputation: 15941

Since both color and grade reference types, you could have item have all three id values involved, and reference both tables using their own id AND the type_id. Since the foreign key constraints will share the same type_id, it will be impossible to reference a grade and color from different types.

The bare minimum columns on the tables would be:

types: type_id 
       [no foreign key constraints]
grades: grade_id, type_id 
       [type_id references types.type_id]
colors: color_id, type_id 
       [type_id references types.type_id]
items: item_id, type_id, grade_id, color_id
       [ (type_id, grade_id) should reference grades as one key
        ,(type_id, color_id) should reference colors as one key
        , type_id would reference types but that should not need enforced with a constraint.
       ]

Upvotes: 1

Related Questions