What are the 6 steps in the database design process?

The six steps of the database design process are requirements gathering, conceptual design, logical design, physical design, implementation, and testing and maintenance. Each step takes the output of the step before it and adds detail. The first three steps decide what data the system stores and how the pieces relate. The last three steps decide how that data is stored, built, and kept correct in a specific database management system (DBMS), which is the software that stores and serves the data.

Step 1: Requirements Gathering

The first step in creating a database is determining what data will be stored. Creating tables and relationships comes later, because both depend on knowing the data first. The designer interviews the people who will use the system and lists every fact the system must record. For an online store, that list includes customers, products, orders, payments, and shipments.

The output is a requirements document. It records functional needs, such as "a customer can have many orders". It also records non-functional needs, such as the expected number of orders per day and how long records must be kept. A requirement missed here costs more to add at every later step.

Step 2: Conceptual Design

Conceptual design turns the requirements into entities, attributes, and relationships. An entity is a thing the system stores facts about, such as Customer or Order. An attribute is one fact about an entity, such as an email address. A relationship states how two entities connect, with a multiplicity such as one-to-many.

The usual output is an entity-relationship diagram (ERD). The diagram does not mention tables, data types, or any DBMS. That is deliberate. A conceptual model should be readable by the business users who supplied the requirements, so they can confirm it before any technical decision is made.

Step 3: Logical Design

Logical design converts the ERD into tables, columns, primary keys, and foreign keys. A primary key is the column that identifies one row. A foreign key is a column that holds the primary key of a row in another table, which is how a relationship is stored. A many-to-many relationship becomes a third table that holds two foreign keys.

The tables are then normalized. Normalization is a set of rules that remove repeated data so that each fact is stored once. Most designs stop at third normal form (3NF), which means every non-key column depends only on the key. The output is a logical schema that any relational DBMS could implement.

Step 4: Physical Design

Physical design decides how the logical schema is stored in one specific DBMS. The designer picks the product, such as PostgreSQL or MySQL, and sets exact data types and column sizes. Then come the decisions that affect speed: which columns get indexes, whether large tables are partitioned, and where the data files are placed.

Some designs denormalize here, which means copying a value into a second table to avoid a slow join. The trade is faster reads for slower writes and more storage. Security is also set here, including roles and which users can read each table.

Step 5: Implementation

Implementation creates the database from the physical design and loads the data. The designer runs the data definition language (DDL) scripts, which are the SQL statements that create tables, indexes, views, and constraints. Existing data is migrated into the new tables, and each row is checked against the constraints as it is loaded.

Application code and reports are connected at this point. The output is a running database that matches the design documents.

Step 6: Testing and Maintenance

Testing confirms the database works, and maintenance keeps it working. Functional tests check that inserts, updates, deletes, and queries return the expected results. Performance tests run realistic query loads and measure response times, so slow queries can be fixed with better indexes. Security tests confirm that users can reach only what their role permits.

Maintenance continues for the life of the system. It includes backups and tested restores, monitoring of query times and disk use, and schema changes as requirements change.

Why Database Design Matters

A design error is cheap to fix on a diagram and expensive to fix in production. Poor design causes duplicate data, which then disagrees after updates. It causes missing keys, which allow rows that point to a parent that does not exist. It also causes slow queries, because indexes and partitions were never planned. A database that follows the six steps stores each fact once, enforces its rules with constraints, and answers its common queries quickly.

Six Steps or Eight Stages

Some textbooks describe eight stages instead of six. They add a planning stage before requirements and split DBMS selection out of physical design. The content is the same. The six-step version groups those activities into the steps above.

Key Takeaways

• The six steps are requirements gathering, conceptual design, logical design, physical design, implementation, and testing and maintenance.
• The first three steps are independent of any DBMS; the last three depend on the chosen product.
• Normalize to 3NF in logical design, then denormalize only for a measured performance need.
• Database design is one part of the larger design skill taught in Grokking System Design Fundamentals.
• For data modeling questions in interviews, see Grokking the System Design Interview.
• Class and object models that map onto these tables are covered in Grokking the Object-Oriented Design Interview.