Lots of comments were made regarding immutable of data in a database. Everything prompts me to think further on the topic, as well as provide some clarification.
AZ Jim’s comment compares immutable data to that of slowing changing dimensions or facts in a data warehouse. While this is very similar in concept to immutable data, the intention is not exactly the same. With slowly changing facts or dimensions, you can perform a merge operation with changes. Existing records may be modified, at least in theory. With Immutable data, no existing data will ever be modified. Instead, all immutable data is versioned. The current record is the last version. Thus, with immutable data there are only inserts. This doesn’t address deletes directly, so that would be something you have to figure out.
Mark talks about using Trie data structures as used by functional programming. In a SQL database this is more difficult to implement. You essentially need Insert actions on a table with insert row level locks. There are no updates unless you do soft deletes with a status flag. Read actions are implemented without any locking. Because you can’t update a record, your reads don’t require locking because in theory the underlying data cannot be updated.
Robert looks at the different ways to lock down permissions on the database so that some users may only read data, and others have permission to modify it. In an Immutable table design, most likely the same user is going to need both permissions. That being the case, you probably can’t implement your Immutable business rules through security, at least not efficiently. Instead, you could probably do your magic through stored procedures. Don’t provide direct access to the table. Do all of your CRUD (Create, Retrieve, Update, Delete) operations through stored procedures, and grant permission to the stored procedures instead of the underlying table(s).
I don’t know if there is a need for immutable data in an SQL database, at least not very often. There are a lot of gymnastics needed in order to implement it. However, the more I think about it, I find potential uses.
Cheers,
Ben