I"m a big fan of a table / table_history combo. Meaning, if you have a
person and that person can have different states that change frequently,
you can do something like this:
create table people(
person_id
person_name
)
create table people_state_history(
person_id references people,
effective tsrange not null
default tsrange(clock_timestamp()::timestamp without time zone,
'infinity'),
primary key (person_id, effective),
person_state
)
Use a constraint so you can't have overlapping effective ranges:
alter table person_state_history add
constraint no_overlapping_person_state_history
exclude using gist (
cast(person_id as text ) with =,
effective with &&);
Then, to see a person's state at the current time, you can do something
like this:
select person_id, person_state from people_state_history where effective <@
now()::timestamp without time zone
(and add your joins in as well).
We've also created a trigger, so any new insert on the state_history table
will automatically close the last row.
create or replace function set_event_history_in_use ()
returns trigger
as
$$
begin
update person_state_history
set effective = tsrange(lower(effective),
clock_timestamp()::timestamp without time zone)
where clock_timestamp()::timestamp without time zone <@ effective
and person_id = NEW.person_id;
NEW.effective := tsrange(clock_timestamp()::timestamp without time
zone, 'infinity');
return NEW;
end;
$$
language plpgsql;
Then, depending on business requirements, you can create this dual table to
track history across time. If salaries are something people would like to
see over time, then you could do a person_salary_history table, etc.
Hope this helps!
Best,
Rob Heinen
On Fri, Sep 13, 2019 at 11:57 AM stan <st...@panix.com> wrote:
>
> I am working on a system that will support internal bossiness work for a
> company. Periodicly things will change in their "world". For example they
> periodically recalculate the "mark up" of various components, of their
> bushiness, such as labor and or purchased materials. Presently I am keeping
> these constants in a table, and I have the constant, and an effective
> start,
> and end date for these factors. In this scenario, the number of rows will
> just grow over time, and by using the proper conditions in our select
> statement, i can apply the correct factors as they change over time.
>
> In another scenario, a column of the employee table is the pay rate.
> Obviously this will change over time, also. It does not appear to me that
> this lends itself to this same treatment, as most of the data associated
> with a given employee, is fairly static, and if I add an entire row, just
> because the pay rate changed, this looks overly complex.
>
> This cannot be the first time this issue has been addressed. What have
> others done in this scenario?
>
>
>
