CODEWITHSUNDEEP
mysqlsqlforeign-keysinnodb
AndrewB
AndrewB

Reputation: 836

Many Foreign Key Restraints Depending on Many Tables

Note: This question may possibly be poorly asked as my knowledge of SQL is quite limited.

We have 3 tables, BUILDINGS, FLOORS, ROOMS.

BUILDINGS contains the columns: Building_ID(its primary key), and Name(its name)

FLOORS contains the columns: Floor_ID(its primary key), Name(its name), and Building_ID(foreign key to BUILDINGS' primary key)

ROOMS contains the columns: Room_ID(its primary key), Name(its name), Building_ID(foreign key to BUILDINGS' primary key), and Floor_ID(foreign key to FLOORS' primary key)

My problem is, if I have two buildings, 1 and 2, and two floors, 1 and 2, if floor 1 belongs to building 1, and floor 2 belongs to building 2 (that is building 2 has no floor 1, and building 1 has no floor 2) it is possible to add a room which is in building 1, floor 2, even though this should not exist.

How can I correct my structure to ensure only valid rooms may be added? And if I were to scale out again, another table DESKS which would have an associated BUILDING, FLOOR, and ROOM would this solution still work?

Upvotes: 2

Views: 41

Answers (2)

Greg Viers
Greg Viers

Reputation: 3523

The issue is one of normalization. In your ROOMS table, do not include the foreign key Building_ID. The connection between ROOM and BUILDINGS is via the FLOORS table. A second connection is unnecessary, since any queries you run can join through those tables to get the building name.

Upvotes: 1

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

You create 3 tables. Room only need to reference floor. And browsing floors you will know buildings

CREATE TABLE Building
    (`building_id` int, `name` varchar(6))
;

CREATE TABLE floor
    (`floor_id` int, `building_id` int, `name` varchar(7))
;

CREATE TABLE room
    (`room_id` int, `floor_id` int, `name` varchar(9))
;

Querys

SELECT *
FROM Building;

SELECT *
FROM Building B;
INNER JOIN floor F 
    ON B.building_id = F.building_id
;

SELECT *
FROM Building B;
INNER JOIN floor F     
    ON B.building_id = F.building_id
INNER JOIN Room R
    ON F.room_id = R.room_id
;

And for DESKS

CREATE TABLE desks
    (`desk_id` int, `room_id` int, `name` varchar(9))
;

SELECT *
FROM Building B;
INNER JOIN floor F     
    ON B.building_id = F.building_id
INNER JOIN Room R
    ON F.floor_id = R.floor_id
INNER JOIN Desk D
    ON R.room_id = D.room_id
;

Upvotes: 4

Related Questions