"Breaking Tables, Delivering Features: A Developer's Guide to SQL Normalization"
Tags: database, developer, tutorial
Published: 2023-11-14 08:58:41

Your new feature will break sophisticated, well-thought, beautiful, current SQL database structure? Worry not. This guide is here to help fellow developers to handle feature that involves changing database structure. With example, of course.

Please note that this guide is focused on normalizing SQL database. This is not related to programming language, nor this applies to NoSQL database.

Let's dive in.

## Database Change is Inevitable

First, a little background. As time passes, product usually grows following business needs. Complexity also grows. You will have more feature tickets awaiting. On one hand, this is good sign. People are using the product, which turns into revenue, which turns into your salary. Hopefully.

On the other hand, you will be pressed to work on new features, and more often than not, require changes on your codebase and database. It is not due to lack of planning. No one could cover all possible cases and put it into code. Therefore, the **first tip is to embrace the change** and deal with it.

I have encountered quite some breaking changes in my career. The early ones felt frustrating. I thought "They are messing with my desgin? Why didn't they thought about it?". Then sometimes I even doubt myself "Why didn't I thought about it?". That was bad. Then I realized it is just inherent reality of evolving product. After some more time, I finally developed simple guide to deal with it.

## Real World Example: NEW Split Bill Payment 

Suppose that your product is a restaurant management system that allows customer to see menu, make orders, and pay through app/website while dining-in. It reduces restaurant's staff cost and improve efficiency. 

Now the product have implemented order with payment. However, everyone agreed that one order is paid once. In other words, order and payment have `1:1` relation. Few weeks later, the company received feedback. Now people want to split bills. This means `1:1` relation between order and payment turns to `1:N` relation.

What do you have to do now? Keep reading and follow these steps

## SQL Normalization Guide

### 1. Identify Impacted Tables and Columns

Once it is known that some table relations will be changed from `1:1` to `1:N`, quickly determine which columns are impacted.

In example case above, let's say that existing `orders` table is structured like this

```sql
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  order_date TIMESTAMP,
  bill_base_amount BIGINT,
  tax_amount BIGINT,
  other_fee_amount BIGINT,
  bill_total_amount BIGINT,
  payment_amount BIGINT,
  payment_type VARCHAR(255),
  payment_timestamp TIMESTAMP,
  payment_completed BOOLEAN,
  created_at TIMESTAMP,
  updated_at TIMESTAMP
);
```
This is the table `orders` with sample data

| id | order_date            | bill_base_amount | tax_amount | other_fee_amount | bill_total_amount | payment_amount | payment_type  | payment_timestamp      | payment_completed | created_at            | updated_at            |
|----|-----------------------|------------------|------------|------------------|-------------------|----------------|---------------|-------------------------|---------------------|------------------------|------------------------|
| 1  | 2023-11-14 12:00:00   | 5000             | 1000       | 200              | 6200              | 6200           | Credit Card   | 2023-11-14 12:05:00    | true                | 2023-11-14 12:00:00    | 2023-11-14 12:05:00    |

In this case, following columns should be separated as new table
- `payment_amount`
- `payment_type` 
- `payment_timestamp` 
- `payment_completed`

Why `bill_total_amount` and some others are not included in new table? Because the information is related to order as a whole. Furthermore, for `bill_total_amount` it is practical later as checker to make sure sum of all split bills are equal to bill on order.

Once impacted columns are identified, you need to make new table.

### 2. Create New Table

Now, a new table is needed to store payment data. Such table requires
- Identifier --> `id`
- Foreign key to `orders` --> `order_id`
- Impacted columns above
- Timestamps --> just good practice

In SQL, this is one example

``` sql
CREATE TABLE payments (
  id SERIAL PRIMARY KEY,
  order_id INT REFERENCES orders(id),
  payment_amount INT,
  payment_type VARCHAR(255),
  payment_timestamp TIMESTAMP,
  payment_completed BOOLEAN,
  created_at TIMESTAMP,
  updated_at TIMESTAMP
);

```
Now, you have the building blocks in the database. Time to start refactoring.

### 3. Change Existing Codebase Logic

At this point, you'd know `orders` and `payments` has 1:N relation, meaning one order could have at least one payment. You just need to insert data to `orders` and `payments`. However, it's still unclear **_when_** to insert the data. There are at least 2 options:
1. Assuming there's `order_menu` table (it's common sense for dev, right?), you can split by menu price. The splitting process is another part entirely, but from database perspective, all payment records can be generated and inserted together.
2. Split by amount. This approach relies on amount input. This allow each payment to be generated independently. However, the total amount must be thoroughly checked.

Which one is better? It depends on what product team wants :)

This could be an implementation in Node.js
 Example in Node.js
  ```javascript
const pgp = require('pg-promise')();
const db = pgp('your_connection_string');

// Sample data for an order
const orderData = {
  order_date: new Date(),
  bill_base_amount: 5000,
  tax_amount: 1000,
  other_fee_amount: 200,
  bill_total_amount: 6200,
  payment_amount: 6200,
  payment_type: 'Credit Card',
};

// Query for inserting into orders
const insertOrderQuery = `INSERT INTO orders -- etc`;
const insertPaymentQuery = `INSERT INTO payments -- etc`;
const mappingToPayments = (order_id) => {
    // do something mapping here
    // should be more complex, but you get the idea
    return paymentList
}

// Function to insert payments for a specific order_id
function insertPayments(order_id) {
  const insertPaymentPromises = mappingToPayments(order_id).map((paymentData) => {
    // Modify paymentData to include the order_id for the query
    const modifiedPaymentData = { ...paymentData, order_id };
    
    // Execute the query to insert into payments
    return db.none(insertPaymentQuery, modifiedPaymentData);
  });

  // Return a promise that resolves when all payments are inserted
  return Promise.all(insertPaymentPromises);
}

// Execute the first query to insert into orders
db.one(insertOrderQuery, orderData)
  .then((result) => {
    // Use the returned order_id in the function to insert payments
    const order_id = result.id;
    
    // Call the function to insert payments for the order_id
    return insertPayments(order_id);
  })
  .then(() => {
    console.log('Order and payments inserted successfully.');
  })
  .catch((error) => {
    console.error('Error inserting order and payments:', error);
  })
  .finally(() => {
    pgp.end(); // Close the database connection when done
  });

  ```

Also, don't forget to refactor the query to fetch data. Plus, make sure to test your code.

### 4. Migrate Existing Data

Now that you've taken care of the development. It is time to migrate the data. As you've known, existing data is in `orders`. After deployment, they better be in `payments` as well.

Fortunately, there's one-time query for this.
```sql
INSERT INTO payments (order_id, payment_amount, payment_type, payment_timestamp, payment_completed, created_at, updated_at)
SELECT
  id AS order_id,
  bill_total_amount AS payment_amount, -- Assuming one payment for one order
  payment_type,
  payment_timestamp,
  true AS payment_completed,  -- Assuming all existing payments are completed
  created_at,
  updated_at
FROM orders
WHERE payment_type IS NOT NULL;  -- Add additional conditions if needed

```

If there's more complexities (e.g not all payments are completed), you need to handle them first before executing query above.

### 6. Deploy & Run

This is time where you actually execute all steps above in production environment. Fingers crossed!

That's it! I hope this guide helps you. Comments and feedbacks are always appreciated. Stay sane, developers :)

(Cover image generated by [playground.ai](https://playgroundai.com/))