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:
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:
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:
where
EquivalencyId
is a user- or application-generated key, but is not unique, andCourseId
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:Then the equivalent courses from the second school would be expressed in another table:
where
EquivalencyId
is a foreign key fromEquivalencyBase
. The data in this table would be: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
.Result:
This design could be extended to map equivalent courses for multiple schools with the addition of a school identifier in each table.