6 ms·

When people set out to design a SQL database, they usually end up updating and deleting records. This is bad because it destroys history, and nothing that you c

When people set out to design a SQL database, they usually end up updating and deleting records. This is bad because it destroys history, and nothing that you can add to your SQL architecture will fix it at a fundamental level. By basing your system on a journaled event stream, you start with a foundation of complete history retention, and you can build exactly the sort of reporting views you need at any time (say, by creating a SQL database for other applications to query).

alexro 15y ago
You can have a design where your previous version of a record gets automatically copied into another table along with the timestamp of the operation. Then you can slice this history however you want. All with no additional app code. But I wouldn't write off the noDB approach for various transitional data, or data that isn't mean to live long anyway, like tweets.
- enjo 15y ago
  Why would the lifetime of the data be at all relevant? Most noDB (a term I don't like much) stores are built to be highly durable. There are a lot of questions that make the choice of data store a difficult one, but I'm not sure that plays into it at all.
matwood 15y ago
When people set out to design a data driven application, they usually end up updating and deleting records. FTFY... It's not hard to build history into a SQL table design. You can even store events in a...wait for it... SQL database. I have built numerous systems backed by SQL databases that have complete history retention. Answering questions like 'who had id 'X' on this date 3 years ago' are easily solvable with basic standard sql. I certainly don't believe SQL databases are perfect or the tool for every job, but in many cases they work just fine until you get into very large datasets. Admittedly, I only deal with databases in the 100s of GB range so I have yet to personally run into the scaling problems that a Google or Facebook have and the SQL backed systems I have built work just fine.
- jcromartie 15y ago
  It's not hard, no, but it usually doesn't happen in the average application. That's the issue: it's not built in, it's not standardized, and every SQL database is fully mutabile by default. If your system operates in this journaled/event-sourcing way at the most basic level then you have the ultimate future-proof storage layer. You could decide to completely change the way the data is stored and represented (in-memory or otherwise) at any time, as long as you have that raw history.
  - deleted 15y ago
    [deleted]
  - kfool 15y ago
    That's one of the things ChronicDB does. It makes historical values available in the average application.
- artsrc 15y ago
  You loose some features of SQL when you have historical values. For example if you only want customers to create orders you can set a foreign key between customer and order. If you don't delete orders and customers, then the database will allow you to insert new orders against old customers, unless you apply a more complex constraint.
  - kfool 15y ago
    This can be handled by deleting orders from the logical representation, yet preserving them in the historical layer.
rxin 15y ago
The storage system of Postgres, one of the earliest rdbms, uses MVCC. The early version even allows you to roll back or query historical data. http://wiki.postgresql.org/wiki/MVCC http://wiki.postgresql.org/wiki/MVCC
r00fus 15y ago
Deletes on enterprise SQL systems are usually prevented (the preferred pattern is "mark for delete + purge" similar to a VM's garbage collection). The Application ignores "marked" data as deleted. That leaves tracking the remaining inserts and updates, which is a well understood problem. It's called Auditing. Here is a simple script that will auto-audit a SQL Server database... variations in other SQL dialects are likely just as straighforward. http://www.geekzilla.co.uk/ViewECBC0CC3-1C7E-4E7E-B243-F2F259A5C920.htm http://www.geekzilla.co.uk/ViewECBC0CC3-1C7E-4E7E-B243-F2F25...
wanorris 15y ago
I used to work on an application that did all the typical insert/update/delete operations on the core data tables and retained a journaled event stream in a separate audit database sufficient to regenerate the entire database from scratch (which was done at least a couple of times). I suppose it would be equally possible to think of the main data tables as a "reporting view" in the sense you use here, except that the application was 1000:1 or more in update frequency to read frequency, and all the reads were performed on the main data tables, so that's kind of a "tail wagging the dog" view of the app. For an application with different requirements, your view of things might be quite useful, of course.

alexro 15y ago

You can have a design where your previous version of a record gets automatically copied into another table along with the timestamp of the operation. Then you can slice this history however you want. All with no additional app code. But I wouldn't write off the noDB approach for various transitional data, or data that isn't mean to live long anyway, like tweets.

enjo 15y ago
Why would the lifetime of the data be at all relevant? Most noDB (a term I don't like much) stores are built to be highly durable. There are a lot of questions that make the choice of data store a difficult one, but I'm not sure that plays into it at all.

matwood 15y ago

When people set out to design a data driven application, they usually end up updating and deleting records. FTFY... It's not hard to build history into a SQL table design. You can even store events in a...wait for it... SQL database. I have built numerous systems backed by SQL databases that have complete history retention. Answering questions like 'who had id 'X' on this date 3 years ago' are easily solvable with basic standard sql. I certainly don't believe SQL databases are perfect or the tool for every job, but in many cases they work just fine until you get into very large datasets. Admittedly, I only deal with databases in the 100s of GB range so I have yet to personally run into the scaling problems that a Google or Facebook have and the SQL backed systems I have built work just fine.

jcromartie 15y ago
It's not hard, no, but it usually doesn't happen in the average application. That's the issue: it's not built in, it's not standardized, and every SQL database is fully mutabile by default. If your system operates in this journaled/event-sourcing way at the most basic level then you have the ultimate future-proof storage layer. You could decide to completely change the way the data is stored and represented (in-memory or otherwise) at any time, as long as you have that raw history.
- deleted 15y ago
  [deleted]
- kfool 15y ago
  That's one of the things ChronicDB does. It makes historical values available in the average application.
artsrc 15y ago
You loose some features of SQL when you have historical values. For example if you only want customers to create orders you can set a foreign key between customer and order. If you don't delete orders and customers, then the database will allow you to insert new orders against old customers, unless you apply a more complex constraint.
- kfool 15y ago
  This can be handled by deleting orders from the logical representation, yet preserving them in the historical layer.

rxin 15y ago

The storage system of Postgres, one of the earliest rdbms, uses MVCC. The early version even allows you to roll back or query historical data. http://wiki.postgresql.org/wiki/MVCC http://wiki.postgresql.org/wiki/MVCC

r00fus 15y ago

Deletes on enterprise SQL systems are usually prevented (the preferred pattern is "mark for delete + purge" similar to a VM's garbage collection). The Application ignores "marked" data as deleted. That leaves tracking the remaining inserts and updates, which is a well understood problem. It's called Auditing. Here is a simple script that will auto-audit a SQL Server database... variations in other SQL dialects are likely just as straighforward. http://www.geekzilla.co.uk/ViewECBC0CC3-1C7E-4E7E-B243-F2F259A5C920.htm http://www.geekzilla.co.uk/ViewECBC0CC3-1C7E-4E7E-B243-F2F25...

wanorris 15y ago

I used to work on an application that did all the typical insert/update/delete operations on the core data tables and retained a journaled event stream in a separate audit database sufficient to regenerate the entire database from scratch (which was done at least a couple of times). I suppose it would be equally possible to think of the main data tables as a "reporting view" in the sense you use here, except that the application was 1000:1 or more in update frequency to read frequency, and all the reads were performed on the main data tables, so that's kind of a "tail wagging the dog" view of the app. For an application with different requirements, your view of things might be quite useful, of course.