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@illuminatedcomputing.com