1

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?

  • Wouldn't simply deleting and re-creating a material (not necessarily in this order) do what you want? – mustaccio Jun 13 '18 at 19:41
  • Doing that would first require me to delete all references of that material in the linked tables anyway due to referential integrity. Also, what if a material were associated with more than one course? – Eaglegust Jun 13 '18 at 19:49
  • But that's what you want -- delete all references of that material in the linked tables MaterialCourse and MaterialClass, no? I presume you have referential integrity constraints in place, which will cascade-delete children rows. – mustaccio Jun 13 '18 at 19:53
0

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)
);
  • Thanks for this, but this is exactly the solution I have now. The problem (which I acknowledge may not be a problem but a necessity), is that if a user changes an association (e.g., user wants a material assigned to a single class available for the entire course), this structure would require me to either leave the now invalid ClassMaterials entry after making a new CourseMaterials entry, or I would need to delete the ClassMaterials entry. Either way would work, but there are disadvantages to both. I was just wondering if there were another design that would better support data integrity. – Eaglegust Jun 14 '18 at 15:13
0

All of this is valid in PostgreSQL, which supports self-referencing tables and recursive CTEs to query them (rCTEs)

Commentary

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).

That's not true. You'd just have to hide the duplicates (like with DISTINCT) or make a mention that the course and class requires it. Which actually makes sense and sounds useful.

Schema

I would go with a single-table hierarchy for the structure, with categories that looks like this,

CREATE SCHEMA myedu;

CREATE TABLE myedu.categories (
   id_category  int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
   id_parent    int REFERENCES myedu.categories,
   name         text
);
INSERT INTO myedu.categories VALUES
  ( 1, null, 'Car Courses' ),
  ( 2, 1, 'Fixing Cars'),
  ( 3, 1, 'Driving Cars');

You can use an enum type for the semester

CREATE TYPE myedu.semester AS ENUM ('spring', 'fall', 'summer');

We can create the classes like this,

CREATE TABLE myedu.classes (
  id_class    int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  id_category int REFERENCES myedu.categories,
  semester myedu.semester NOT NULL,
  year     smallint       NOT NULL,
  UNIQUE (id_category, year, semester)
);

INSERT INTO myedu.classes (id_class, id_category, semester, year)
VALUES ( 1, 3, 'fall', 2018 );

Now we create a material table and two linking tables. We do this because there is nothing about the materials that are hierarchical, books and pencils don't relate to each other directly though the classes that use them do. You could alternatively write this as a JSONB column on the respective tables which is likely what I would do unless you actually need to search on materials.

CREATE TABLE myedu.materials (
  id_material    int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  material_name  text
);

CREATE TABLE myedu.material_classes    (
  id_material int REFERENCES myedu.materials,
  id_class    int REFERENCES myedu.classes
);
CREATE TABLE myedu.material_categories (
  id_material int REFERENCES myedu.materials,
  id_catagory int REFERENCES myedu.categories
);
  • That's interesting, thanks. It's a different way of thinking about it. Unfortunately, changing the course/class table structures is beyond the scope of my current task. I can do what I want with Materials, but regardless, your solution is close to my current one. Your comment about keeping and hiding duplicates would be easier. I was thinking doing that was leaving invalid data, but perhaps it isn't. It would also provide a fallback association should a user disassociate a material from a higher association in the hierarchy (if that makes sense). – Eaglegust Jun 14 '18 at 15:22
  • Sure it makes sense. I wouldn't worry about it either. If the redundant data serves a purpose other than simplicity, it's not redundant. And, I think it could here. – Evan Carroll Jun 14 '18 at 16:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.