Aggregate Data vs Intermediary Tables in SQL

SQL best practices

In SQL, Joins are very powerful. They can allow you to create data that is an aggregate of multiple tables. In modern frameworks, they can even be abstracted by ORMs to make our lives easier as developers. But the idea of having aggregate data can be quite a challenge in some cases, however not many, but it is likely you might face the challenge at some point in some project!

My Argument

Aggregate data, represents a state or a snapshot of your data, at a certain point in time, or at the moment which you are actually fetching the data. Joins combine data from multiple tables and using other aggregate methods, such as sum , average , max , etc we can manipulate and get refined data with more clarity, without affecting the existing data.

But, sometimes it is very important to store the state of the data at any point. This stored state can later be analyzed and used to debug the system in case of any unexpected behavior. Furthermore, the stored state can be used to quickly generate reports, without recalculating the data from the database, and hence have less time per query.

Conclusion

It is, in some cases, important to store the aggregated data in the database instead of manipulating data on every request. Although when storing aggregate data in the database, you risk having your database taking too much space, you can have a faster query as the database grows.

Thanks for taking the time to read this article. The article is purposefully kept short to encourage discussion, in case you have any questions, feel free to shoot in the comments!