# One or two tables (for two things: cancellations and moves)

database-design

A lesson may be canceled, moved to an other time, and possibly switched to an other teacher.

In other words, we have

lessonid INT UNSIGNED
canceled BIT(1)
time TIME
teacherid INT UNSIGNED


Should all these "lesson modifications" be stored in one table? Or one table for canceled and another for moved lessons?

I am inclined to store it into one table (with possibly NULL time and probably NULL teacherid, because I can add a common "modification ID" (the primary ID of the table) for both cancellations a moves to an other time. But I am open to hear arguments pro and contra.

MySQL 5

You could store the original lesson record in one table, and consider it immutable. Then you could store all changes in a lesson-modifications table. When you want to view the current state of any lesson, you will need code to look at the original lesson and then look at all the modifications and apply them to figure out when the current lesson is, and if it's cancelled or not.

The schema could look like this:

base_lesson
-----------
id
teacherid
time

lesson_mods
-----------
mod_id
lesson_id
changed_field
new_value
mod_datetime


the lesson_mods table stores only the modifications made to a base lesson. Problems with this are that changed_field and new_value: it will be difficult to enforce that these only refer to valid fields that can be changed, and values that are valid for those fields (there are ways to do this, but I don't want to get into that here).

Or you could do something like this:

base_lesson
-----------
id
teacherid
time

lesson_cancellations
--------------------
cancel_id
lesson_id
is_cancelled (in case you want to un-cancel a lesson)
cancel_created_time (to know when this record was created - in case there are multiple cancellations/un-cancellations you will need to know the most recent)

lesson_reschedules
------------------
resched_id
lesson_id
new_lesson_time
rescehedule_created_time (to know when this record was created - in case there are multiple reschedules you will need to know the most recent)


This is still pretty complicated and I'm not sure what benefit you would get by storing the cancellations and schedule changes this way.

A simpler way to do it (though it would require more storage) is simply record each image of a lesson after a change is made, and all images in the same table. You'll need additional metadata to keep track of which version of a lesson record is the most current.


lessons
-------
id
original_lesson_id (FK to lessons.id - id of lesson that this modification was based on - NULL for new lessons that are not modifications of existing lessons)
teacherid
lesson_time
cancelled
created_time (used for versioning)


I personally would go with the second option as it is much simpler and if you want you can still do image-by-image comparisons. I'd only consider the first if the number of modifications is large enough to create concerns for storage.