Database schema for date-time ranges

Question

Let's say, I have a course which consists of N lessons. I want to offer it via email subscription. Each N days an email with content should be sent to all the users who've subscribed.

I want it to be sent to the users every 1, 2 or 3 days. For instance.

I'm looking into a way to create a database scheme where I'll able to store these time ranges, and of other types (weeks, months...), in the future.

Date-time ranges can be in minutes, hours, days, weeks and months. And there should be a possibility to specify at what time during a day it'll be sent, or which day of week, etc...

Note that each newly subscribed user should also go through the same process: he'll receive the same N lessons with the same frequency or date-time ranges, for instance: 1st lesson on day 1 at 15:00 o'clock UTC, 2nd on day 3 at 9:30 o'clock, 3rd on day 8 at 21:15 o'clock and so on.

This type of scheme isn't the same as the one for scheduling re-occuring tasks and cron in Linux.

And I want it to be flexible enough too.

I've began with these fields:

* frequency_unit: enum (minute, hour, day, week, month)
* frequency_value: int

How to create it?

Answer 1

You need to record T₀ for each student and ΔT for each lesson.

DATE math assumes a difference of 1 === 1 day. That means a value of 0.5 === half day. etc. etc. Some RDBMS support an INTERVAL data type that could be used instead. It is up to you which one you want to use.

So, to calculate "when to start a lesson", you just need to take the date_started value (T₀) and add the delta_T value (ΔT) of the lesson to get the DATE-TIME to send the email.

create table lesson_plan (
  lesson_id int -- identifier
  ,class_id      int not null
  ,delta_T       number -- ( or whatever is best for your RDBMS)
  ,constraint lesson_plan_pk primary key (lesson_id)
  ,constraint lesson_plan_fk1 foreign key (class_id)
      references classes(class_id) on delete cascade
);

comment on table lesson_plan is 'Stores chapters and when to send the lesson';

create table student_classes (
   student_id     int
  ,class_id       int
  ,date_started   date -- needs to record DATE and TIME
  ,constraint student_classes_pk primary key (student_id,class_id)
  ,constraint sudent_classes_fk1 foreign key (student_id)
      references students(student_id) on delete cascade
  ,constraint student_classes_fk2 foreign key (class_id)
      references classes(class_id) on delete cascade
);

comment on table student_classes is 'Records when each student started a class.';

create or replace
view student_lessons
as
select a.student_id
      ,b.class_id
      ,c.lesson_id
      ,a.date_started + c.delta_T as lesson_start_datetime
from student_lessons a
  join classes b on a.class_id=b.class_id
  join lesson_plan c on c.class_id=b.class_id;

comment on view is 'When a lesson starts for a specific student.';