4 ms·

Many enterprise databases have a requirement where during the regular progress of business, database rows are never deleted or updated - instead, for every chan

Many enterprise databases have a requirement where during the regular progress of business, database rows are never deleted or updated - instead, for every change, a new row with a timestamp is added with a copy of the old data, and then mutated. In other words, many, many business applications built on top of databases have half-assed version control of the data built into them. Datomic takes the knowledge that many databases are built like this, and basically designs with that in mind. The ability to say "give me a view of the data as it was 1 month ago" is built into the system from the very beginning, rather than added on later.

ajuc 13y ago
There's also design pattern in systems based on relational databases that skips the need for copying so much data to keep history - instead of keeping primary data in your database with additional column "timestamp", you keep changes to that data in another table, and either use triggers triggered by inserting these changes to modify the primary data, or forgo primary data alltogether, and just use view over changes table (can be slow). I've seen it implemented 2 times in enterprise systems and it works OK, but like all design patterns it's workaround for lacking feature. Also some dbs (for example Oracle) allows you to do SELECT * FROM SOME_TABLE WHERE USER_ID = 5 AS OF TIMESTAMP..., but that's not good enough.
- malkia 13y ago
  While looking on the subject, I do recall something about old PostgreSQL versions having something like it... But I've always wondered how they deal with changes to database schemas, types, etc. While googling I've found from this post: http://www.postgresql.org/message-id/Pine.GSO.4.64.0802031528120.7860@westnet.com http://www.postgresql.org/message-id/Pine.GSO.4.64.080203152... this free (?) book - Snodgrass/Jensen "Developing Time-Oriented Database Applications in SQL" - http://www.cs.arizona.edu/people/rts/tdbbook.pdf http://www.cs.arizona.edu/people/rts/tdbbook.pdf Haven't read it, so I'll spend some time through it. To me this is an interresting thing, since I would like to know whether I can do history tracking of game asssets for game development. E.g. often in our studio someone would like to compare data, how it was before this and what changed. We do have the mechanism, but they are not so ellegant.
  - ajuc 13y ago
    Oracle only keeps history for specified time, it takes up space, on our systems it's usualy a few hours, so it won't work for long-period history. Regarding game assets - why not keep them in git or some other cvs? For textual data it has huge additional advantage - you can blame, merge and easily see differences, but even for binary data it can be used.

ajuc 13y ago

There's also design pattern in systems based on relational databases that skips the need for copying so much data to keep history - instead of keeping primary data in your database with additional column "timestamp", you keep changes to that data in another table, and either use triggers triggered by inserting these changes to modify the primary data, or forgo primary data alltogether, and just use view over changes table (can be slow). I've seen it implemented 2 times in enterprise systems and it works OK, but like all design patterns it's workaround for lacking feature. Also some dbs (for example Oracle) allows you to do SELECT * FROM SOME_TABLE WHERE USER_ID = 5 AS OF TIMESTAMP..., but that's not good enough.

malkia 13y ago
While looking on the subject, I do recall something about old PostgreSQL versions having something like it... But I've always wondered how they deal with changes to database schemas, types, etc. While googling I've found from this post: http://www.postgresql.org/message-id/Pine.GSO.4.64.0802031528120.7860@westnet.com http://www.postgresql.org/message-id/Pine.GSO.4.64.080203152... this free (?) book - Snodgrass/Jensen "Developing Time-Oriented Database Applications in SQL" - http://www.cs.arizona.edu/people/rts/tdbbook.pdf http://www.cs.arizona.edu/people/rts/tdbbook.pdf Haven't read it, so I'll spend some time through it. To me this is an interresting thing, since I would like to know whether I can do history tracking of game asssets for game development. E.g. often in our studio someone would like to compare data, how it was before this and what changed. We do have the mechanism, but they are not so ellegant.
- ajuc 13y ago
  Oracle only keeps history for specified time, it takes up space, on our systems it's usualy a few hours, so it won't work for long-period history. Regarding game assets - why not keep them in git or some other cvs? For textual data it has huge additional advantage - you can blame, merge and easily see differences, but even for binary data it can be used.