Database Design Best Practices: Principles, Normalization and Keys

Good database design gives every fact one place to live, one key that identifies it, and one path to reach it. Get that right and queries stay fast, data stays consistent, and a schema change stays cheap. Get it wrong and you pay for it in every release. The practices below are the working set.

• Model the entities before the tables: draw the relationships first, then translate them into columns.
• Give every table a primary key, and every relationship a foreign key.
• Normalize to third normal form, then denormalize only where a measured query demands it.
• Index the columns you filter, join and sort on - not every column.
• Keep names, data types and conventions consistent across the whole schema.
• Version the model in Git, so a schema change is reviewed like code.

This article is an English adaptation of a German original by Dr. Veikko Krypczyk, published in PHP Magazin[1].

Fundamentals of Database Design

Database design is the structured process of deciding how data is stored, organized and processed. The output is a logical model - tables, columns, keys and constraints - that one specific engine then implements. A relational database schema built this way should:

• Ensure data integrity: stored data stays accurate and consistent.
• Perform well as data volumes grow.
• Stay flexible enough to absorb future requirements.
• Stay easy to maintain through updates and extensions.

Five principles carry most of that work.

Clarity and Simplicity

A well-structured design is easy to read. Tables and relationships are named for what they hold, and each table holds one kind of thing. Example: customers and orders belong in two tables joined by a key, not in one table carrying both.

Data Integrity

Integrity rules keep data correct while it changes:

• Entity integrity: every table has a primary key that identifies each row exactly once.
• Referential integrity: relationships are declared as foreign-key constraints[2], so an order cannot point at a customer that does not exist.
• Domain integrity: data types, CHECK constraints and ranges keep invalid values out of a column.

Avoiding Redundancy

Redundancy means storing the same fact in more than one place, which invites contradictions and wastes storage. Normalization removes it by splitting data into separate tables and declaring the relationships. Example: a customer's name is then stored once in the Customers table, not on every order.

Flexibility and Scalability

A schema should absorb new requirements without a rewrite. Adding a column or a table should not force changes to unrelated tables, and the design should still hold up when row counts grow tenfold. Example: in an online shop, a new product category should be a row, not a schema change.

Performance

Design decisions set the ceiling for query speed:

• Indexing: add an index with CREATE INDEX[3] on the columns you filter and join on, then read the query plan to confirm the planner uses it.
• Query optimization: a clean model makes the obvious SQL the fast SQL.
• Partitioning: split a large table[4] so a query touches fewer rows - an Orders table split by year is the standard example.

Indexes and foreign keys pull against each other on write cost, so settle the trade-off per table.

The Database Design Process, Step by Step

Four steps take a requirement to a running schema, and each one produces an artefact the next step consumes. The full schema design procedure adds the checks between them.

Normalization: 1NF, 2NF and 3NF

Normalization splits a database into smaller, logically connected tables, each one about a single subject, so that a fact is stored once. It improves data integrity and simplifies maintenance, and it costs joins at query time - which is why the balance between normalization and performance is a decision, not a default.

The stages are called normal forms (NF), and most of the work sits in the first three. A fuller treatment of logical database design covers what comes after them.

First Normal Form (1NF)

A table is in 1NF when:

• All values are atomic (indivisible).
• Each column has a single data type.
• No repeated groups or arrays sit inside a column.

Example before normalization:

The products column holds multiple values, which violates 1NF. After applying 1NF:

Every field now holds one atomic value.

Second Normal Form (2NF)

A table is in 2NF when it is already in 1NF and every non-key attribute depends on the whole primary key, with no partial dependencies. Example before normalization:

Here customer_name hangs off order_id, but it logically belongs to a Customers table. Split it in two:

Third Normal Form (3NF)

A table is in 3NF when it is in 2NF and no non-key attribute depends on another non-key attribute, so there are no transitive dependencies. Example before normalization:

The customer_address column depends on customer_id, not on order_id. Split it in two:

Keys: Primary, Foreign and Secondary

Keys are how a row is identified and how two tables are joined. Three kinds do that work.

Primary Key

A primary key is a column, or a combination of columns, that uniquely identifies each row in a table. The rules and the full SQL definition are short; the consequences of choosing the wrong column are not. A primary key must be:

• Unique - no duplicate values.
• Not NULL - every row carries a value.

CREATE TABLE Customers (
  customer_id INT PRIMARY KEY,
  name VARCHAR(255),
  address VARCHAR(255)
);

Foreign Key

A foreign key is a column that points at the primary key of another table, and the database refuses any value that has no match there. That is what enforces referential integrity.

CREATE TABLE Orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  date DATE,
  FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

Secondary Key

A secondary key is a column, or group of columns, indexed to speed up searching. It does not have to be unique.

CREATE INDEX idx_phone ON Customers(phone);

Tools for Visualizing and Maintaining a Schema

A schema with hundreds of tables stops fitting in anyone's head. Visual tools draw the relationships, compare a model against a live database, generate documentation and keep a change history.

Collaboration is the harder half: developers change the schema locally and then have to merge into a shared model without overwriting each other. Before letting any tool write to a production database, put the security questions to it that you would put to a deployment tool.

• Visual diagrams make relationships and cardinalities readable at a glance.
• Schema synchronization keeps development, staging and production consistent.
• Generated documentation onboards new team members without a walkthrough.
• Scripted, repeatable tasks remove the manual steps that drift.

Designing and Documenting a Schema in DbSchema

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

DbSchema is a visual database designer for 100+ SQL and NoSQL databases. It reverse-engineers a live database into an ER diagram, keeps the design in a local .dbs model file of human-readable XML, and compares that model against the database to generate the migration script. Community connects, reverse-engineers, diagrams and runs SQL. Saving the model to a file - and the offline design, documentation, synchronization and data browsing that build on it - is Pro; logical and conceptual design is Architect.

• Design offline: edit the model file with no database connected, then synchronize on reconnect.
• Track the model in Git: the .dbs file is text, so a schema change is reviewed as a diff.
• Export interactive HTML5 documentation: diagrams with column comments as tooltips.
• Browse related data across tables: the relational data editor follows foreign keys, not hand-written joins.
• Build queries visually: pick tables and columns, read the generated SQL.
• Automate recurring work: Groovy scripts run schema synchronization and model comparison from DbSchemaCLI, using the samples that ship with the product.

On the compliance side, the design lives in a local model file and whichever Git remote you choose, so a schema need not leave your own infrastructure - and the vendor is ISO-27001 certified.

Conclusion

A schema is the hardest part of a system to change once it carries data. Clear structure, minimal redundancy, integrity rules the database itself enforces, and indexes that match the queries keep it cheap to live with - and all four are settled in the design phase.

Download DbSchema to reverse-engineer your own database into a diagram and see where the design leaks. The Community edition covers connecting, reverse-engineering, interactive diagrams and the SQL editor; saving the model to a file, HTML5 documentation, schema synchronization, relational data browse and the visual query builder are Pro.

Sources

1. Read the original article here (German)
2. PostgreSQL: Constraints
3. PostgreSQL: CREATE INDEX
4. PostgreSQL: Table Partitioning