The Cost of Poor Database Design
Welcome to the first post of Hands-on DB. I'm excited to share my thoughts on the importance of database design and how to avoid poor design choices that can lead to costly and frustrating consequences for both users and developers.
When we talk about database design, we're referring to the way data is structured and organized within a database. A well-designed database should be efficient, scalable, and easy to maintain. However, when the database design is poor, the consequences can be detrimental.
Most of business owner might don’t understand about the costs of poor database design. But what are the costs?
Cost #1: Slow Performance
One of the most common consequences of poor database design is slow performance. Poorly indexed or organized data can result in longer query execution times, leading to slower load times and increased frustration for users. This is just one example of the costs associated with poor database design.
Let's take a look at a sql database schema design that exemplifies poor design:
This is a legacy system for a product that features withdrawing digital money.
The "withdraw_request" table is created when a user requests to withdraw their digital funds.
The "withdraw_log" table is used to log all disbursing activities.
This schema works well when the number of withdraw requests is low, but it can quickly become problematic when the number of requests increases. This works well when withdraw traffic just around 500-1000 request per month. Until one day, there is a problem with the disbursement server (third party), resulting in the "withdraw_log" table being inserted million datas with status DISBURSE_FAILED. The database server get slow down significantly. This is because queries that get the withdraw status, such as the one below, will take longer to execute
SELECT w.*,
(SELECT wl.status
FROM withdraw_log wl
WHERE wl.withdraw_id = w.id
ORDER BY created_at DESC
LIMIT 1
) as status
FROM withdraw w
Cost #2: Increase Development Time and Cost
When a database is poorly designed, it can be time-consuming and difficult for developers to add new features or make updates. This can result in increased development time and cost, as well as a higher risk of errors and bugs.
Suppose you are developing a content management system that can store text articles and audio podcasts, and you have decided to design your database schema as follows:
The schemas looks fine, but it can become complicated if you want to display the latest content, including both articles and podcasts. This is because you may need to use a UNION
to combine the data and then order it, which can make the logic more complex.
SELECT id, user_id, title, featured_image, body, '' as description, '' as media_url
FROM article
UNION
SELECT id, user_id, title, featured_image, '' as body, description, audio_url
FROM podcast
ORDER BY created_at
The query above is quite complex and can result in slower query times. Moreover, if you plan to add more content types, such as videos, you may need to add yet another union, making the logic even more complicated.
A better approach is to have a single "content" table that includes columns for all types of content, such as articles, podcasts, and videos. This makes the database schema more straightforward and eliminates the need for complex queries involving UNION operators. See the following schema
This approach is much more straightforward. If you want to display the latest content, including articles and podcasts, you can simply query the "content" table and sort it by the "created_at" column in descending order.
SELECT * FROM content ORDER BY created_at DESC
Cost #3: Bugs
Imagine you're creating an e-commerce platform, and you've designed your database schema according to the example given above. Initially, this might appear to be a logical choice. However, this can cause issues. For instance, what would happen if the "name" in the "product" table was updated? While this may be technically acceptable, it would create a problem from a business perspective. Consider the scenario where the invoice you received three months ago is not the same as the invoice you receive today.
What’s The Cause & How to Avoid
Poor database design can be attributed to two main categories: technical and non-technical. Technical causes refer to the lack of fundamental knowledge or skills required for designing a well-structured database. However, designing a database is not just about technical expertise; it can also be influenced by various factors such as the specific products or use cases, and the prevailing circumstances.
Some common technical causes of poor database design:
Ignoring database constraint
Using the wrong data types
Inadequate indexing
Lack of normalization - depend on the case but often this leads to data inconsistency
Overcomplication that leads to unnecessary tables, columns, and relationships
Non-technical causes can also contribute to poor database design
Lack of understanding of business requirements
This is the most common reason I have encountered. It is essential to understand that whether a database design is good or bad depends on the situation, product, or business requirements. The same database design that works well for one use case may not be suitable for another. Let's take an example to illustrate this.
This product table is straightforward and would be suitable for a small e-commerce website. However, if you were designing it for a hotel system that requires dynamic price changes, this table would not be a good fit and would be considered a poor database design example.
Inadequate planning and analysis
Another common reason for a poorly designed database is a lack of time spent on planning. Database design is a complex process that requires careful planning and analysis. Rushing through this process or not dedicating enough time to it can result in a poorly structured database schema.
Now that we understand the causes of poor database design, we can take steps to prevent it. To design a good database, we must understand the business requirements, follow best practices for database design, and allocate sufficient time for planning and analysis.
Thanks for reading Hands-on DB! Subscribe for free to receive new posts and support my work.