Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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