Association Tables and Data Integrity

data integritydatabase-design

I'm developing a solution related to classroom materials. The hierarchy for classes is as follows:

  • Course Type (e.g., Car Courses)
    • Course (e.g., Fixing Cars, Driving Cars)
      • Class (e.g., Fall semester class)

My problem deals with class materials. Currently I have a "Material" table that has MaterialId, Title, Href, etc. I need to associate Materials with 4 possible scenarios:

  1. Global materials (material applies to all classes regardless of course or
    course type)
  2. Course Type materials
  3. Course materials
  4. Class specific materials

I'm currently handling global materials with a field in the Material table (IsGlobal), and I'm handling the other 3 scenarios using linked tables (MaterialCourseType, MaterialCourse, MaterialClass). The problem with this solution is it requires me to enforce data integrity through application code. For example, if I associate a material with a course type, I would need to delete all references to materials in the MaterialCourse and MaterialClass tables (since it's inclusion in MaterialCourseType would cover those). It is possible in my select statement to use a UNION which would automatically select distinct materials across all 4 tables, but that would leave invalid data. When the user through the front end decides to make a material available to the entire course instead of for a specific class, not cleaning up the MaterialClass table seems messy to me.

Is there a better database design I could use to enforce data integrity, or am I forced to deal with this problem in my application code to first delete invalid entries whenever a user associates a material differently?

Best Answer

I fully support your inclination not to delete material records. The association records joining the Type/Course/Class to Materials however, you will need to delete if you no longer need the association.

I would recommend the following schema, which I've written in T-SQL as it's my native SQL flavor. It provides you the basis you create you Type -> Course -> Class structure, and enables to you share a global materials list across all three, whilst creating unique associations at each level.

Here is a sqlfiddle to let you play around and get familiar with the design.

create table CourseType (
  Id int identity primary key
  ,Name nvarchar(100) not null
);

create table Course (
  Id int identity primary key
  ,CategoryId int not null foreign key references CourseType(Id)
  ,Name nvarchar(100) not null
);

create table Class (
  Id int identity primary key
  ,CourseId int not null foreign key references Course (Id)
  ,Name nvarchar(100) not null
);

create table Material (
  Id int identity primary key
  ,Name nvarchar(100) not null
);

create table CourseTypeMaterials (
  CourseTypeId int not null foreign key references CourseType(Id)
  ,MaterialId int not null foreign key references Material(Id)
  ,primary key (CourseTypeId, MaterialId)
);

create table CourseMaterials (
  CourseId int not null foreign key references Course(Id)
  ,MaterialId int not null foreign key references Material(Id)
  ,primary key (CourseId, MaterialId)
);

create table ClassMaterials (
  ClassId int not null foreign key references Class(Id)
  ,MaterialId int not null foreign key references Material(Id)
  ,primary key (ClassId, MaterialId)
);
Related Question