Database Design 101: Why Relational Databases Matter

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:

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.

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. If ABC Constructions has 50 quotes over two years, their contact information is stored once, not 50 times. This dramatically reduces storage requirements and, more importantly, prevents data inconsistencies.

Second, it ensures data integrity. With the bad design, if you update customer details on one quote, those changes don't propagate to other quotes. You end up with multiple versions of the 'truth' scattered across your database. Which email is current? Which phone number is valid? You can't know without checking every record.

With proper relational design, there's one source of truth. Update the customer record, and every quote that references that customer immediately reflects the change. No manual synchronisation. No risk of divergent data.

Third, it prevents deletion anomalies. Imagine a customer requests you delete their information for privacy reasons. With the bad design, you'd have to delete all their quotes too, losing valuable business data. With the relational design, you can mark the customer as deleted or anonymise their data while preserving quote history for business analytics.

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. Maybe they need the project manager's email, the finance contact's phone, and the CEO's name for approvals.

With everything in one table, you're stuck. Do you add contact_person_2_name, contact_person_2_email, contact_person_3_name fields? What if they need five contacts? Ten? You end up with a nightmarish table with 50+ columns, most of which are NULL most of the time.

With relational design, you simply create a new contacts table:

Now each customer can have unlimited contacts, each with their own role. Your existing quotes table doesn't change. Your customers table doesn't change. You just added a new relationship. That's the power of proper database design — you can extend functionality without breaking existing structure.

The same principle applies to configurations. Want to add a new field for elevator speed? Just add a column to the configurations table. All existing quotes continue to work. Want to track configuration revision history? Create a configuration_versions table that references configurations. The core system remains intact.

Normalisation: The Technical Term

What we're describing is called database normalisation — the process of organising data to reduce redundancy and improve integrity. The relational model we showed follows what's called Third Normal Form (3NF), which is the sweet spot for most applications.

The key principles are: separate distinct entities into their own tables, use foreign keys to establish relationships between tables, ensure each piece of information exists in exactly one place, and design for flexibility and future changes.

You don't need to be a database theory expert to apply these principles. Just ask yourself: 'If this information changes, how many places do I need to update it?' If the answer is more than one, you probably need to refactor.

Another good rule: if you find yourself adding numbered columns (contact_1, contact_2, contact_3), that's a red flag. You need a separate table with a one-to-many relationship instead.

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. They can duplicate a previous configuration for a new quote with one click — no copy-pasting data. They can update customer details and know those changes apply everywhere.

When the client requested we add tracking for multiple project sites per customer, we added a sites table. When they wanted revision history for quotes, we added a quote_versions table. When they needed to track which sales consultant created each quote, we added a users table and a foreign key reference. Each addition was straightforward because the foundation was solid.

Contrast this with a another system we had to integrate with on a previous project. They had built everything in flat tables with duplicated data. Adding a new feature meant updating dozens of records manually. Generating reports required complex string parsing because customer names were stored inconsistently. Fixing bugs often created new bugs because data lived in multiple places.

Their system technically worked, but maintenance was a nightmare. Our relational design wasn't just theoretically better — it was measurably faster to extend, easier to maintain, and far less error-prone in production.

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. For analytics databases, you often use star schemas that intentionally duplicate data. For document-based data, NoSQL databases like MongoDB might be more appropriate.

But for the vast majority of web applications — especially business applications like CPQ systems, CRMs, inventory management, project management tools — proper relational design is the right foundation. It scales well, maintains data integrity, and adapts to changing requirements gracefully.

The key is understanding the principles so you know when to apply them and when to deviate. Start with good relational design as your default. If you encounter specific performance or scalability issues, optimise strategically. But don't prematurely optimise by denormalising before you've proven you need it.

The Bottom Line

Good database design is like good foundation work on a building. Nobody sees it, nobody appreciates it — until something goes wrong. A poorly designed database makes every feature harder to build, every bug harder to fix, and every change riskier to deploy.

Invest the time upfront to design your database properly. Separate logical entities into their own tables. Use foreign keys to establish relationships. Eliminate redundancy. Think about how data will evolve over time.

The hour you spend planning your database structure will save you weeks of refactoring later. And your future self — the one who has to add that new feature the client just requested — will thank you.

Relational databases aren't old technology. They're proven technology. And in an era of flashy frameworks and trendy tools, the fundamentals still matter. Master them, and you'll build applications that scale, adapt, and stand the test of time.