Each database application is unique. While most of the time, deleting a record is the best approach, there are times when the application requirements demand that database records should never be physically deleted.
So who uses this technique?
For instance, StackOverflow does it for all Posts (e.g. Questions and Answers). The StackOverflow
Posts table has a
ClosedDate column which acts as a soft delete mechanism since it hides an Answer for all users who have less than 10k reputation.
If you’re using Oracle, you can take advantage of its Flashback capabilities, so you don’t need to change your application code to offer such a functionality. Another option is to use the SQL Server Temporal Table feature.
However, not all relational database systems support Flashback queries, or they allow you to recover a certain record without having to restore from a database backup. In this case, Hibernate allows you to simplify the implementation of soft deletes, and this article is gong to explain the best way to implement the logical deletion mechanism.
Continue reading “The best way to soft delete with Hibernate”
While doing my High-Performance Java Persistence training, I came to realize that it’s worth explaining how a relational database works, as otherwise, it is very difficult to grasp many transaction-related concepts like atomicity, durability, and checkpoints.
In this post, I’m going to give a high-level explanation of how a relational database works internally while also hinting some database-specific implementation details.
Continue reading “How does a relational database work”
I’ve just finished the wonderful SQL Antipatterns book by Bill Karwin. The book is a must-have reference for any developer that has to interact with a relational database system.
This post is a review of what this book is all about and why you should be interested in reading it.
Continue reading “Book Review – SQL Antipatterns”
I’m now reading the wonderful SQL Antipatterns book by Bill Karwin. The book is a great reference for any developer that needs to interact with a Relational Database System.
In this book, the Entity-Attribute-Value (EAV) model is portrayed as an Anti-Pattern. Although I must admit that a proper relational schema is almost always the best approach when designing an enterprise system, there are use cases when the EAV model is justified.
In this post, I’m going to explain when to use EAV and how to transpose a
ResultSet so that rows become columns.
Continue reading “How to map table rows to columns using SQL PIVOT or CASE expressions”
As I explained in this previous article, you can map calculated properties using Hibernate
@Formula, and the value is generated at query time.
In this post, you’ll see how you can use a custom SQL fragment to customize the JOIN relationship between two entities, or an entity and a collection of embeddable types.
Continue reading “How to customize an entity association JOIN ON clause with Hibernate @JoinFormula”