Designing a Course Equivalency Database

database-design

I am trying to create a database to store information regarding equivalent courses transferring from one school to another. I am struggling to conceptualize the relationship in a way that can translate into a database. I currently have two tables, each holding the courses offered by each school. These tables contain the course code, the course name, the course description and the number of credit hours.

School 1 offers courses A, B, C, D.
School 2 offers courses W, X, Y, Z.

The equivalent courses could be:

A = W –Taking class A at School 1 is equivalent to taking class W at School 2.

A+C = Y –Taking classes A and C at School 1 is equivalent to taking class Y at School 2.

D = X+Y+Z –Taking class D at School 1 is equivalent to taking classes X, Y and Z at School 2.

And so on. One or more classes at School 1 will be equivalent to one or more classes at School 2. Every course from School 1 needs to have an equivalent course at School 2, as there is a "General Elective" catch all. However, not every course at School 2 will be represented.

I'm not extremely technical and am more interested in just design at this stage. Any help is greatly appreciated.

Best Answer

The courses have a many-to-many relationship, but the functional dependencies can't be expressed using a single intermediate table. Consider the following:

  • Course A School 1 = Course X School 2
  • Course A + B School 1 = Course Y School 2
  • Course C School 1 = Course Y + Z School 2

When a course from School 1 is contained in more than one equivalency, there is no way to determine from a tuple (row or record in the table) which courses are a single equivalency:

course_school1  course_school2
A               X
A               Y
B               Y
C               Y
C               Z

This is ambiguous, as it could mean either the statement above or that A = X + Y, C = Y, C = Z etc. You will need another table to express the notion of an individual equivalency. The schema would be something like:

CREATE TABLE EquivalencyBase (
    EquivalencyId int NOT NULL,
    CourseId varchar NOT NULL )

where EquivalencyId is a user- or application-generated key, but is not unique, and CourseId is a course from School 1. So the data in this table (Courses would probably have a generated primary key of type int/bigint, but I use the letters for clarity) for the relationships above would be:

EquivalencyId  CourseId
1              A
2              A
2              B
3              C

Then the equivalent courses from the second school would be expressed in another table:

CREATE TABLE CourseEquivalency (
    EquivalencyId int NOT NULL,
    CourseId varchar NOT NULL )

where EquivalencyId is a foreign key from EquivalencyBase. The data in this table would be:

EquivalencyId  CourseId
1              X
2              Y
3              Y
3              Z

To select the groups of equivalent courses, join the tables. Obviously another table would contain the course descriptions, etc., which would simply require an additional JOIN on CourseId.

SELECT
  eb.EquivalencyId,
  eb.CourseId AS School1Course,
  c1.Title AS Course1Title,
  ce.CourseId AS School2Course,
  c2.Title AS Course2Title
FROM EquivalencyBase eb
INNER JOIN CourseEquivalency ce
ON eb.EquivalencyId = ce.EquivalencyId
INNER JOIN Courses c1
ON eb.CourseId = c1.CourseId
INNER JOIN Courses c2
ON ce.CourseId = c2.CourseId
WHERE eb.CourseId = 'C'

Result:

EquivalencyId  School1Course  Course1Title  School2Course  Course2Title
3              C              Cowboy 101    Y              Yak herding
3              C              Cowboy 101    Z              Zoology

This design could be extended to map equivalent courses for multiple schools with the addition of a school identifier in each table.

Related Question