How to design a persistent table with dependencies which are allowed to be deleted in the future

database-design

I am designing a database that archives all class schedules and student attendances based on these schedules.
So here are the tables.

Teacher, Subject, Classroom, Class

Schedule table has many-to-many relationship to the above tables.

Schedule:
  date (date),
  start_time (time),
  end_time (time), 
  teachers (m2m to Teacher), 
  subject (foreign key to Subject), 
  classrooms (m2m to Classroom), 
  student_classes (m2m to Class)

My issues is that the schedules must be persistent. However, the teachers may be deleted from the system. How do I design my dependencies so that removing them will not affect the schedules. Currently all relationships use integer primary key.

Best Answer

This looks like a case where you might create Foreign Keys with set null on delete. From that post:

What is a foreign key with "Set NULL on delete" in SQL Server?

A foreign key with "set null on delete" means that if a record in the parent table is deleted, then the corresponding records in the child table will have the foreign key fields set to NULL. The records in the child table will not be deleted in SQL Server.

CREATE TABLE child_table
(
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...

  CONSTRAINT fk_name
    FOREIGN KEY (child_col1, child_col2, ... child_col_n)
    REFERENCES parent_table (parent_col1, parent_col2, ... parent_col_n)
    ON DELETE SET NULL
    [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
);

Just make sure that Teachers in the Schedules table is defined to allow nulls

Related Question