Immutable Data (2015)
kevinmahoney.co.uk
Immutable Data (2015)
1–10 of 43 posts
Re: Immutable Data (2015)
#2It’s a mistake to use timestamp for sorting versions instead of revision_id imo
Re: Immutable Data (2015)
#3From a user's perspective, I can see a privacy drawback as well.
Suppose that instead of a typical User table, you have a User_Revision table like suggested. Every time a user updates their account settings, you INSERT a new row there. If a user changes their email address, you get a row each time they update it.
Not only the company gets an history of email addresses, but also they are tied to each other. If this information gets leaked, the user is exposed to more vectors of attack.
Re: Immutable Data (2015)
#4From a user's perspective, I can see a privacy drawback as well. Suppose that instead of a typical User table, you have a User_Revision table like suggested. Every time a user updates their account settings, you INSERT a new row there. If a user changes their email address, you get a row each time they update it. Not only the company gets an history of email addresses, but also they are tied to each other. If this in…
GDPR and the right to having your personal data deleted certainly puts a bit of a stopper on using an immutable database for anything personally identifying.
Re: Immutable Data (2015)
#5[flagged]
Re: Immutable Data (2015)
#6It’s a mistake to use timestamp for sorting versions instead of revision_id imo
What case would you make for using revision_id?
Re: Immutable Data (2015)
#7[flagged]
[flagged]
Re: Immutable Data (2015)
#8(1) You will benefit immensely in the applications built from these sorts of structures by keeping the version log for an entity in a separate table from the current value. First because you can just use old-school triggers, “any update over here triggers inserting a new row into the corresponding versions table,” you don't need to even care about implementing the versioning in application logic. Second because the sorts of queries that would involve looking at previous versions tend to not be the same as the sorts of queries for general data manipulation.
(2) To give an instance of where (1) becomes important, suppose you change some X to X' and then want to change it back. Suppose that after the change some entity was deleted—X foreign keys to a now-deleted value, X' does not. Most applications that try to shove both current state and history into one ubertable disable a bunch of constraint checking and other suchness, and permit this dubious feature of partially-rolling-back into an inconsistent state. But if you just DELETED the row when you said you had, then you would have gotten a foreign-key-error and your user would have copy-pasted you on their “unexpected error occurred” error message and you'd immediately be able to diagnose what foreign key constraint was blocking the undo, rather than mysterious failures several weeks later.
(3) Regardless of your stance on (1), once your application supports deletion, your relational integrity usually suffers because the technically correct value for all of the columns in a deleted-row is to make them all null. This is basically the problem that databases do not have sum types. A sum type in a database is not hard to create once you need it, create a row that has 3 columns which foreign-key to other tables, plus constraints that exactly one of these values is non-null. So the very lightweight construction if you are upset about denormalizing your data is for a Cat in your Cats table in your PetStore database to be a nullable pointer to a CatVersion. So that's how to proceed if you REALLY want to normalize.
(4) All of the above assumes that for every edit to an entity you will save a new row in the versions table, copying all of the other data. The problem is that inevitably some tables get super wide as they have to hold dozens of pieces of business data together, and it's never the ones that you initially expected. There is an easy fix for this as well, it is for versions to also be “mutable.” Whaaaaa??? Yes. Snapshots plus deltas. It's not really mutable because it's append-only.
Re: Immutable Data (2015)
#9[flagged]
[flagged]
I did not downvote your comment, but the downvotes might be because your message was formulated quite strongly.
Perhaps if you offer a more nuanced view, people may agree more and upvote it as well.
If your stance on mutability is really so strong, then you have to actually persuade people about your views, and that takes quite some effort (mostly from the person to be persuaded, not from you.) Being friendly instead of dismissive might make people more eager to take on another perspective.
Hope this helps a bit for future situations :)
Re: Immutable Data (2015)
#10From a user's perspective, I can see a privacy drawback as well. Suppose that instead of a typical User table, you have a User_Revision table like suggested. Every time a user updates their account settings, you INSERT a new row there. If a user changes their email address, you get a row each time they update it. Not only the company gets an history of email addresses, but also they are tied to each other. If this in…
Slightly unrelated: A great way to solve this specific email issue is to store hashes for emails only, similarly to passwords. When a user needs a password reset, they should send an email to a specific address, the sender mail is hashed, and if it matches a user, a password reset link is replied. That way a data breach can’t expose user emails.
(This is what Tildes use: https://tildes.net/settings/account_recovery , and I found it very genius)