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 ~{:-)
p...@illuminatedcomputing.com
