Database design - multiple foreign keys

Question

My database holds schools, divisions, and courses. A school may or may not have divisions, and a courses is related to a school, and may or may not be related to a division (the school may not have divisions, or the course may be cross-divisioned)

My tables are currently set up as follows:

school:

ID       | name
--------------------
harvard  | Harvard University
mit      | MIT
ucla     | UCLA

division (id+school=unique)

ID  | school (FK) | name
------------------------------------------
eng | harvard     | School of Engineering
arc | harvard     | School of Architecture
eng | UCLA        | UCLA Engineering

course:

ID | school (FK) | division | name
-------------------------------------------------
1  | harvard     | eng      | Intro to Engineering 
2  | harvard     | arc      | Intro to Architecture
3  | harvard     |          | Statistics
4  | mit         |          | Math

My concerns with this:

There is no validation to make sure the divisions in course exists and is related to the school.
division is not actually a fk
It takes two queries to get the school and division

Is there a better way of doing this? I want to be able to:

Query all "harvard" courses
Query all "harvard engineering" courses
Query all "harvard engineering and harvard general" courses

user_10676716 · Answer

If is always possible to create a composite foreign key. However, I would suggest the following changes to your design:

create a default division for each school, called "General"
allow the possibility that the same course would exist in different schools (in real life, this might happen).

Also I would recommend using autoincremented integer primary keys for all the tables, instead of relying on hand-built names.

Consider the following design, that follows the above principles:

school
    id            primary key
    name

division
    id            primary key
    school_id     foreign key to school(id)
    name

course
    id            primary key
    name

course_division
    id            primary key
    course_id     foreign key to course(id)
    division_id   foreign key to division(id)

Now here are queries to use this schema.

Query all "harvard" courses

SELECT c.* 
FROM course c
INNER JOIN division d ON d.id = cd.division_id
INNER JOIN school s ON s.id = d.school_id AND s.name = 'Harvard University'

Query all "harvard engineering" courses

SELECT c.* 
FROM course c
INNER JOIN course_division cd ON cd.id = c.course_id 
INNER JOIN division d ON d.id = cd.division_id AND d.name = 'School of Engineering'
INNER JOIN school s ON s.id = d.school_id AND s.name = 'Harvard University'

Query all "harvard engineering and harvard general" courses

SELECT c.* 
FROM course c
INNER JOIN course_division cd ON cd.id = c.course_id 
INNER JOIN division d ON d.id = cd.division_id AND d.name IN ('School of Engineering', 'General')
INNER JOIN school s ON s.id = d.school_id AND s.name = 'Harvard University'

user_1491895 · Answer

You can create a multi-column foreign key:

CREATE TABLE course (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    school VARCHAR(50),
    division VARCHAR(50),
    name VARCHAR(50),
    FOREIGN KEY (school, division) REFERENCES division(school, id)
);

However, it would probably be better to use a separate AUTO_INCREMENT column in the division table, and use that as the foreign key. That way you don't have to duplicate two columns in the course table.

CREATE TABLE division (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    division_code VARCHAR(50),
    school VARCHAR(50),
    name VARCHAR(50),
    UNIQUE KEY (division_code, school),
    FOREIGN KEY (school) REFERENCES school (id)
);
CREATE TABLE course (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    division_id INT(11),
    name VARCHAR(50),
    FOREIGN KEY (division_id) REFERENCES division(id)
);

Database design - multiple foreign keys

2 Answers