Designing a database schema is more than just deciding what columns go into which tables. It's an exercise in modelling the real world in a way that is both logical and efficient. At the heart of this process is a fundamental tension: the push for perfect data organisation versus the pull of high-speed performance.

The theoretical foundation for organising data is the concept of functional dependency. A functional dependency exists when one attribute in a table uniquely determines another. For example, in a Users table, a userID uniquely determines the userEmail. So, we say userEmail is functionally dependent on userID.

Understanding these dependencies is the key to normalisation, a process for refining tables to minimise redundancy and improve data integrity.

For most applications, the practical goal of normalisation is to reach Third Normal Form (3NF). A table is in 3NF if it's already in Second Normal Form (meaning all non-key attributes depend on the whole primary key) and all its attributes depend only on the primary key.

This second part sounds a bit abstract, but it simply means we're eliminating transitive dependencies. A transitive dependency is like a chain of dependency: the primary key determines a non-key column, which in turn determines another non-key column.

Imagine a table of Orders that includes the customer's name and their city. The orderID determines the customerID, and the customerID determines the customerCity. The city doesn't depend directly on the order itself, but on the customer who placed it. This is a transitive dependency that violates 3NF.

Here’s how that refactoring looks.

The benefits of 3NF are significant. It drastically reduces data redundancy, which saves space and prevents inconsistencies. If a customer updates their email, you only have to change it in one place, the Customers table. This improves data integrity and makes the database much easier to maintain.

Every table needs a primary key, but choosing one isn't always straightforward. You often face a choice between a composite key and a surrogate key.

A composite key is a key made up of two or more columns that, together, uniquely identify a row. For example, in a table linking students to courses, the combination of studentID and courseID could be the primary key. This is a natural way to model the relationship.

An alternative is a surrogate key—an artificial key, usually an auto-incrementing integer (id), that has no business meaning. Its only job is to be a unique identifier.

While composite keys accurately reflect the data's relationships, they can be cumbersome. JOIN operations become more complex, and if the values in the composite key need to change, it can cause cascading updates. Surrogate keys are simple, stable, and make JOINs faster. For these reasons, many designers prefer surrogate keys as a default, even when a natural composite key is available.

A perfectly normalized database is a beautiful thing in theory, but it can be slow in practice. To fetch a complete set of data for a user's profile page, you might need to perform several JOIN operations across users, profiles, addresses, preferences, and more. Each JOIN adds computational overhead.

This is where denormalization comes in. It's the strategic, deliberate process of breaking normalisation rules to improve read performance. Instead of splitting data into many small tables, you intentionally add redundant data to reduce the need for JOINs.

When does this make sense?

• High-Read Workloads: For applications like analytics dashboards, reporting systems, or content management systems where data is read far more often than it's written, denormalization is a common strategy. A single, wide table with all the necessary information can be queried much faster than a set of normalized tables.

• High-Write Workloads: Conversely, systems that handle a high volume of transactions (like an e-commerce checkout or a banking system) usually benefit from a highly normalized schema (3NF). Normalisation ensures that writes and updates are fast and consistent, as data isn't duplicated across multiple tables.

The key is to start with a normalized design (3NF is a great baseline) and only denormalize selectively, where performance bottlenecks have been identified through testing. It's a pragmatic compromise, not a free-for-all.

Ultimately, schema design is about finding the right balance for your specific application's needs. There is no single correct answer, only a series of informed trade-offs.