Database Design 101: Why Relational Databases Matter
If you've ever built a web application, you've made database design decisions — whether you realised it or not. And if you're like most developers early in their career, you probably made some decisions that seemed fine at the time but came back to haunt you later.
Let's talk about relational databases, normalisation, and why spending an extra hour on database design can save you weeks of painful refactoring down the road. We'll use a real example from our CPQ (Configure, Price, Quote) system to illustrate the difference between database design that works and database design that works well.
The Bad Design: Everything in One Table
Let's start with what not to do. When we first started building the CPQ system for Vertical Engineering, the temptation was to keep things simple. We needed to store quotes with customer information and lift configurations. The naive approach? Put everything in one big quotes table.
Here's what that looked like:
| QUOTES TABLE (Bad Design) | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| quote_id | name | phone | address | city | state | lift_type | lift_capacity | lift_height | door_finish | cab_material | price | |
Every quote contains all the customer details and all the configuration details in a single row. At first glance, this seems reasonable. It's simple to query — just SELECT * FROM quotes and you have everything. No joins, no complexity.
But here's where the problems start. Imagine Customer ABC Constructions requests three different quotes for three different buildings. With this design, you have to duplicate all their information three times:
| quote_id | name | phone | address | city | state | lift_type | capacity | height | door_finish | cab_material | price | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | ABC Constructions | abc@example.com.au | 0412 345 678 | 123 Main St | Brisbane | QLD | Passenger | 1000kg | 20m | Stainless | Mirror | $45000 |
| 2 | ABC Constructions | abc@example.com.au | 0412 345 678 | 123 Main St | Brisbane | QLD | Goods | 2500kg | 15m | Powder | Brushed | $62000 |
| 3 | ABC Constructions | abc@example.com.au | 0412 345 678 | 123 Main St | Brisbane | QLD | Passenger | 800kg | 30m | Stainless | Etched | $58000 |
Notice the problem? ABC Constructions' information is copied three times. Now imagine they update their email address. You have to remember to update it in all three rows. Miss one? Now you have inconsistent data. Send an important email to the old address? The customer never receives it.
This is called data redundancy, and it's the enemy of reliable databases.
The Good Design: Relational Structure
Now let's look at how we actually built it using proper relational database design. Instead of one massive table, we separated the data into logical entities:
| CUSTOMERS TABLE | ||||||
|---|---|---|---|---|---|---|
| customer_id | name | phone | address | city | state | |
| 1 | ABC Constructions | abc@example.com.au | 0412 345 678 | 123 Main St | Brisbane | QLD |
| CONFIGURATIONS TABLE | |||||
|---|---|---|---|---|---|
| config_id | lift_type | capacity | height | door_finish | cab_material |
| 101 | Passenger | 1000kg | 20m | Stainless | Mirror |
| 102 | Freight | 2500kg | 15m | Powder | Brushed |
| 103 | Passenger | 800kg | 30m | Stainless | Etched |
| QUOTES TABLE | ||||
|---|---|---|---|---|
| quote_id | customer_id | config_id | price | created_date |
| 1 | 1 | 101 | $45000 | 2025-01-05 |
| 2 | 1 | 102 | $62000 | 2025-01-07 |
| 3 | 1 | 103 | $58000 | 2025-01-09 |
See the difference? The customer information lives in one place. The configurations are defined separately. The quotes table simply references these entities using IDs (foreign keys). This is the essence of relational database design.
Now when ABC Constructions updates their email address, we change it once in the customers table, and all their quotes automatically reflect the current information. No duplication. No inconsistency. No missed updates.
Why This Matters: Data Integrity
The relational approach solves several critical problems. First, it eliminates redundancy. Customer data exists in exactly one place.
The Power of Flexibility
Here's where relational design really shines: future modifications. With the bad design, imagine your client decides they want to track multiple contact people per company.
Normalisation: The Technical Term
What we're describing is called database normalisation — the process of organising data to reduce redundancy and improve integrity.
Real-World Impact on the CPQ System
When we built the CPQ system for Vertical Engineering, this relational structure made all the difference. Sales consultants can pull up a customer and instantly see all their historical quotes.
When to Break the Rules
Now, here's the nuance: relational design isn't always the right answer. For applications with massive scale, you might denormalise for performance.
The Bottom Line
Good database design is like good foundation work on a building. Nobody sees it, nobody appreciates it — until something goes wrong.
Looking for something similar? Get in touch to discuss your project.