I have an app which records Insurances in a company where they work. each insurance has a time frame.

Example : Employee John decides to buy a medical insurance from their employer. But the next month john decides to change to another medical plan. John can have "n" number of plans with his employer.

i need a good way to store that database, because I need to show the John had a specific plan in a particular month. There are also some complex calculations which are involved on the times when johb changes the insurance plans


I would create a many-to-many relationship between employees and medical plans, using a daterange to identify the period in which the chosen plan was valid.

create table employee 
  id integer primary key, 
  first_name text not null,
  last_name text not null

create table medical_plan
   id integer primary key, 
   ... additional columns for the plan ....

create table employee_plans
   employee_id integer not null references employee,
   plan_id integer not null references medical_plan, 
   active_during daterange not null, 

You can prevent overlapping ranges in the assignment with an exclusion constraint

alter table employee_plans 
  add constraint non_overlapping_ranges 
  exclude using gist (employee_id with =, active_during with &&);

(you need to install the btree_gist in order to be able to create that constraint)

Then you can check which plan is active by joining the tables and adding a WHERE condition on the date range:

select e.*, p.*
from employee e
  join employee_plans ep on ep.employee_id = e.id
  join medical_plan mp on mp.id = ep.plan_id
where mp.active_during @> date '2019-02-01';

mp.active_during @> date '2019-02-01' means: return all rows where the specified date falls in to the range defined by active_during.

| improve this answer | |

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