Database schema for date-time ranges 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?
You need to record T0 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 &quot;when to start a lesson&quot;, you just need to take the date_started value (T0) 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.';