Re: How to handle things that change over time?

On 9/13/19 2:57 AM, stan wrote:
>
> I am working on a system that will support internal bossiness work for a
> company. Periodicly things will change in their "world".

Oh this is my favorite topic. :-) It's a common problem, although
solutions don't seem to be well-known in the programming community.
Typically you'd use start/end times on your table, as you suggested.
Postgres is a great RDBMS for this since it has range types and
exclusion constraints. The SQL:2011 standard also has temporal primary
keys, foreign keys, SELECTs, and UPDATE/DELETEs, and we're working on
adding those too. But your use case sounds easy to build even without
those extra features. Here is a talk I gave this summer about temporal
features in Postgres:

https://github.com/pjungwir/postgres-temporal-talk

And here is an annotated bibliography to the main writings & tools out
there, including some projects that might help you implement what you
want in Postgres:

https://illuminatedcomputing.com/posts/2017/12/temporal-databases-bibliography/

> 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.

Normally people would indeed just add another row. I wouldn't call it
complex (especially compared to the alternatives), but maybe a little
wasteful. The Date/Darwen/Lorentzos book gives a lot of attention to
avoiding the problem though. Essentially you'd adopt a sixth-normal form
where each attribute (column) gets its own table.

Good luck. I'm always happy to talk about temporal databases if you
like. :-)

Yours,

--
Paul ~{:-)
pj(at)illuminatedcomputing(dot)com