Designing a Course Equivalency Database

Question

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.

Answer 1

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.

Answer 2

I think this is a M:N (many to many) relationship between School1 and School2.

Maybe something like this:

School1
========
course_num1 (PK)
course_title1
course_description1
credit_hours1

School2
=======
course_num2 (PK)
course_title2
course_description2
credit_hours2

Equivalency
=======
course_num1 (PK) - fk back to School1 table
course_num2 (PK) - fk back to School2 table

Sample Data for School1 table (only first two columns shown):

course_num1: course_title1
=========================
10: Computer Science 1
11: Computer Science Accelerated
12: English Literature
13: Dystopian Literature
14: Intro to Database

Sample Data for School2 table (only first two columns shown):

course_num2: course_title2
=========================
0: General Elective
96: Software Design
97: Database programming
98: Programming I
99: British Literature 19th c.
100: British Literature 20th c.
101: Basket weaving

Sample Data for Equivalency table:

course_num1: course_num2
========================
10 : 98
14 : 97
12 : 99  -- your A+C=Y requirement
12 : 100 -- your A+C=Y requirement
13 : 0   -- general elective
11 : 97  -- your D = X+Y+Z requirement
11 : 98 -- your D = X+Y+Z requirement
11 : 96 -- your D = X+Y+Z requirement

notice that course_num2 "101" is not represented in the equivalency table (as your requirements specified).