Sql-server – Database Designer’s Block

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.

My wife is a nurse educator who had no even management platform. I developed one in a VERY similar fashion that you outlined here. The logic and thought process was the same.

You should be safe with this overall schema.