Database Design and the GDPR
Tags: todayilearned, discuss, database, sql
Published: 2020-12-12 15:35:35

*knowledge dependencies for understanding this article include [beginner's SQL]  (https://www.youtube.com/watch?v=27axs9dO7AE)(including [ON_DELETE CASCADE] (https://www.youtube.com/watch?v=xDti3Y66Naw)) and beginner's [database design](https://www.youtube.com/watch?v=cepspxPAUTA)

_The first thing I want to do is acknowledge that the inspiration and one of the resources for this article was a conversation I had with [Don Omondi](https://github.com/The-Don-Himself). He's a wonderful teacher._

Ok, so [what is the GDPR](https://gdpr.eu/what-is-gdpr/)? It's a privacy and security law written by the EU, which affects any company that targets or collects data related to people in the EU. The effect it had (and still has as cookies evolve) on cookies is extensive. It also affected database design. 

One of the rights of EU citizens according the GDPR is the [right to erasure] (https://gdpr-info.eu/art-17-gdpr/). This means that, under a [certain set of circumstances](https://ico.org.uk/for-organisations/guide-to-data-protection/guide-to-the-general-data-protection-regulation-gdpr/individual-rights/right-to-erasure/#ib1), EU citizens have the right to have their personal data erased. It's also known as the 'right to be forgotten.' 

What impact does this have on database design? Say you're running a site where users can repost other user's posts. User Sally has written a post that user Jenna has reposted.

 Now, Sally wants to delete her account on your site. 

If you've set up your tables such that ON_DELETE CASCADE deletes Sally's data and all the associated data, you've also deleted the content of Jenna's repost. 

What are you going to do?

Enter the soft delete. 

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/by4b47svw1fkpafyrpq8.jpeg)

Soft deletes have a lot of advantages, and many websites use them for [many reasons](https://www.brentozar.com/archive/2020/02/what-are-soft-deletes-and-how-are-they-implemented/). 

Soft deletes allow you to un-delete easily: 

"Without the soft delete in place, a delete() call on an object will delete the record from the table using a DELETE statement. With the soft delete in place, an UPDATE statement is sent instead (that sets the deletedAt field to the current time)." [source] (https://guides.cfwheels.org/docs/soft-delete)


So now, you have to decide what deleting Sally's account means now that you're using a soft delete. If you've implemented a soft delete to prevent things like Jenna's content on her repost disappearing, you might call it a 'deactivation' instead. Sally can come back and retrieve all her data at any time by reactivating her account, and Jenna's repost content can stick around. 

Then the GDPR comes out.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/d05lt29ot3s5xdhbw673.jpeg)

 If your database is set up like this, you can't easily comply with the right to be forgotten. How are you going to solve this problem?

You might license the content that users write under the Creative Commons Attribution ShareAlike license.  That way you can save content, but the user can effectively delete their personal info. You remove their info from your database and anonymize their content. Stack Overflow has [taken this route](https://stackoverflow.com/help/licensing). For example of what this looks like, take this user on Stack Overflow: 
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/hnbzhq45gejie528z8eb.png)

The GDPR is awesome for a lot of reasons-- it's introduced protection for the data of EU citizens, and along with this protection has come exciting new problems to solve. I'm sure there are myriad ways to protect the rights of users under the GDPR. I'd love to hear other solutions in the comments. 🤔