0

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.

1

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

| improve this answer | |
  • I apologise if I didn't explain my question correctly, however my issue is not about how to prevent the dependent model from deletion. The fields on the Schedule table are crucial, since they identify the information "who taught what subjects in what room at specific date and time". If I set any of the fields to null, I lose that information. The question is about how to preserve this information even if the dependencies are deleted. Should I make foreign keys to different fields instead of id? if not what could be the solution design-wise? – grustamli Apr 26 '19 at 23:08

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.