In a programmer’s mind

In my last post, I wrote about what it means to delete things these days. This has changed my vision of the common database table schema.

Take for example the classical item/category schema. You have items in a table that each have only one category associated in another table. It could be blog posts associated with a post category. My previous way of seeing this schema would look like this.

In that schema, you need a category in order to have a post. First, the users will have to enter some categories or there could be some that are added by default. Over time, the users will add some posts to his blog or he will add items that have a category.

What happens if a user wants to delete a category? It first depends if you have a foreign key that enforce integrity between the item table and the category table. If you do not have that foreign key, it will not be a problem running this query.

DELETE FROM PostCategory WHERE postCategoryId = X

However, there might be some problems afterwards when you will display the posts that had this category. You will be missing the name. I used to not setup foreign keys enforcing integrity but this has changed too.

Let’s assume that you have a foreign key enforcing integrity. Let’s also assume that you have setup a cascading delete on that foreign key. If you try to delete a category, it will also delete every item that are associated with that category. It is probably not what you or the users want.

If you really want to offer the possibility to delete a category, you might want to change the category of the existing items to a different category, either one chosen by the user or a default one. Choosing this option might add some complexity and bring some headaches with it.

Back to the basics, why would a user want to delete a category? It is probably because he does not want that category to be used anymore. He wants to make that category unusable for new posts, to make it unavailable or invisible.

Here is what my database schema without delete would look like.

It is quite simple. I added a deleted field in my category table. When I want to delete a category, instead of deleting the entry in table, I simply update the deleted field and set it to true.

When I want to list the category for a new item, I only choose those that have the deleted field set to false. When I want to display an item with his category, the relation is still there, even if the category was “deleted” so I can display the name of the category. When I want to modify the category of an item, I list every category that have the deleted field set to false and the current category so that it can still be used. This is how I create and use my new database schema.