-1

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

closed as too broad by mustaccio, Tony Hinkle, dezso, John Eisbrener, Randi Vertongen Apr 4 at 14:33

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

1

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.

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