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:

table: withdraw_request

table: withdraw_log

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:

table: article

table: podcast

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

table: content

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

table: product

table: order

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.

product
- id          INT AUTO_INCREMENT (PK)
- name        TEXT
- description TEXT
- price       DECIMAL(16,2) NOT NULL
- created_at  TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
- updated_at  TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP

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.

Thank you for reading today's newsletter! If you find it valuable, here are some actions you can take:

1) ✉️ Subscribe — if you aren’t already, consider becoming a paid subscriber.

2) ❤️ Share — you can help spread the word by sharing the article with your team or someone who might find it useful!