4 ms·

He is talking about Effective Dating except in his version he can never use it. If you don't need to reference past for future dates then his idea is nice since

by brixon 11y ago

He is talking about Effective Dating except in his version he can never use it. If you don't need to reference past for future dates then his idea is nice since it keeps the primary tables smaller and the SQL easier to handle. Effective Dating: https://talentedmonkeys.wordpress.com/2010/05/15/temporal-data-in-a-relational-database/ https://talentedmonkeys.wordpress.com/2010/05/15/temporal-da... http://www.cs.arizona.edu/~rts/tdbbook.pdf http://www.cs.arizona.edu/~rts/tdbbook.pdf I did a college project with this where you pick the data and the system will show you how the database looked at that date/time. The past was read only, but the future was editable. The SQL Selects are quite annoying and the data grows very fast.

moron4hire 11y ago
I personally believe that the majority of CRUD projects actually need Temporal databases. I've come to realize that--in my 12 year career--every project I've worked on was in some way covered by at least one form of government regulation (whether Sarbanes-Oxley or HIPAA) that required the ability to audit change. The problem is, most organizations (at least the ones I've worked for) don't know that they are covered by these sorts of regulations, because most places are sub-100 employee consultoware shops that are just reimplementing ERP for small B2B clients who also don't have much visibility on their regulatory coverage. That's not even getting in to user requirements. Users almost always eventually want to know what the past data looked like. They always say at the outset of the project that they won't need it, and they always change their mind a year in. And they never understand why you can't "just get it back. You said we had backups." It's kind of the Wild West out there.
- EvanAnderson 11y ago
  Commenting to voice my agreement. Modeling and querying temporal schema in a traditional RDBMS is difficult so most developers don't even try to do it (and, when they do, they do it badly).
  - moron4hire 11y ago
    Yep, that's exactly right. So many shops live and die off of new college grads who--at best--only know relational algebra and nothing about optimization. I've seen a rare few people coming out of college who could design a schema that didn't completely destroy their data, but it's been rarer still to see anyone of any experience level in that environment who knows anything about making it work in a performant manner.
- blowski 11y ago
  > They always say at the outset of the project that they won't need it, and they always change their mind a year in. I feel your pain. They don't want to pay for it until they need it, but when they do need it, they need it bad. They want you to implement it retroactively so all the changes made even before you implemented it are there. When I was in the position, the solution I came up with was to do a very quick and dirty version that logged all changes for every table to a text file. Then I charged separately to parse the text file into the auditable database. I was able to get away with this because the database was tiny.
EvanAnderson 11y ago
I came here to plug "Developing Time-Oriented Database Applications in SQL", but you already did. Once you start developing systems w/ temporally-oriented schemas I find that it's difficult to go back. Queries can be painful (and arguably the functionality should be "baked-in" deeper down in the RDBMs stack), but the functional result is very nice.
jasonjei 11y ago
Brixon, did you ever have to handle drafts or documents/records that needed approval workflow on update or creation? I'm currently debating whether or not to implement drafts/revisions in the same table for simplicity at the expense of table bloat. Did you go to the University of Arizona? I know Prof. Snodgrass.

by brixon 11y ago

moron4hire 11y ago

I personally believe that the majority of CRUD projects actually need Temporal databases. I've come to realize that--in my 12 year career--every project I've worked on was in some way covered by at least one form of government regulation (whether Sarbanes-Oxley or HIPAA) that required the ability to audit change. The problem is, most organizations (at least the ones I've worked for) don't know that they are covered by these sorts of regulations, because most places are sub-100 employee consultoware shops that are just reimplementing ERP for small B2B clients who also don't have much visibility on their regulatory coverage. That's not even getting in to user requirements. Users almost always eventually want to know what the past data looked like. They always say at the outset of the project that they won't need it, and they always change their mind a year in. And they never understand why you can't "just get it back. You said we had backups." It's kind of the Wild West out there.

EvanAnderson 11y ago
Commenting to voice my agreement. Modeling and querying temporal schema in a traditional RDBMS is difficult so most developers don't even try to do it (and, when they do, they do it badly).
- moron4hire 11y ago
  Yep, that's exactly right. So many shops live and die off of new college grads who--at best--only know relational algebra and nothing about optimization. I've seen a rare few people coming out of college who could design a schema that didn't completely destroy their data, but it's been rarer still to see anyone of any experience level in that environment who knows anything about making it work in a performant manner.
blowski 11y ago
> They always say at the outset of the project that they won't need it, and they always change their mind a year in. I feel your pain. They don't want to pay for it until they need it, but when they do need it, they need it bad. They want you to implement it retroactively so all the changes made even before you implemented it are there. When I was in the position, the solution I came up with was to do a very quick and dirty version that logged all changes for every table to a text file. Then I charged separately to parse the text file into the auditable database. I was able to get away with this because the database was tiny.

EvanAnderson 11y ago

I came here to plug "Developing Time-Oriented Database Applications in SQL", but you already did. Once you start developing systems w/ temporally-oriented schemas I find that it's difficult to go back. Queries can be painful (and arguably the functionality should be "baked-in" deeper down in the RDBMs stack), but the functional result is very nice.

jasonjei 11y ago

Brixon, did you ever have to handle drafts or documents/records that needed approval workflow on update or creation? I'm currently debating whether or not to implement drafts/revisions in the same table for simplicity at the expense of table bloat. Did you go to the University of Arizona? I know Prof. Snodgrass.