Database Design: Conceptual, Logical, and Physical Stages

Database design turns a real workflow into data structures that can preserve meaning and support useful operations. The work proceeds through conceptual, logical, and physical decisions, but each stage should be checked against the same business rules. A table diagram is not enough if it permits impossible records, loses history that the organization needs, or becomes too slow under its actual workload. Begin with the questions the system must answer and the transactions it must perform, then make assumptions explicit.

Discover entities, relationships, and rules

Talk to the people who create and use the records. List the nouns in their work, but do not assume every noun deserves a table. Ask what identifies a customer, an order, a product, or an appointment in this organization; whether records can exist without a related record; and which facts can change over time. Capture examples of ordinary and exceptional cases. A rule such as “one order may contain many products” has consequences for relationships that are easy to miss in a drawing made from a happy-path example.

In a conceptual model, describe entities and cardinalities without committing too soon to a database vendor or index. Include definitions for ambiguous terms. “Active customer” might mean someone with an account, a purchase in the last year, or an open contract. If departments use different meanings, the design must represent or reconcile them; a column named active cannot solve the disagreement. Validate the model by walking through concrete records with stakeholders.

Translate the model into logical relations

Choose keys that identify rows reliably and express relationships with foreign keys where appropriate. A many-to-many relationship, such as orders and products, generally needs an order-line relation that also stores quantity and the price agreed at purchase. Storing a comma-separated list of product names in an order row would make updates, validation, and reporting difficult. Define optionality: can an order exist before a payment, and can a product remain after it is retired?

Normalize data so each fact has a defensible home. If a customer address is repeated on every line item, a correction may update some copies but not others. At the same time, a historical shipping address may legitimately be stored with an order because it records what was used then, not the customer’s current address. The distinction is a business rule, not a blanket instruction to remove every repeated value. Document functional dependencies and deliberate snapshots so future maintainers understand them.

Express integrity and lifecycle requirements

Specify which values are required, unique, bounded, or dependent on another record. Primary and foreign keys, uniqueness, and suitable checks can protect invariants at the data layer. Application validation is useful for feedback, but it should not be the only barrier against inconsistent records created by another integration. Decide what should happen when a referenced record is changed or deleted, especially where history or audit needs to remain intact.

Design for the full lifecycle. How will duplicate customers be merged? How are corrections distinguished from new facts? Which records must be retained, archived, anonymized, or deleted under applicable policies? A timestamp alone may not preserve who changed a value or why. Plan access control by role and protect sensitive fields, backups, and exports. A database design that exposes information to every reporting user has a problem even if its joins are elegant.

Choose physical structures for the workload

The physical stage considers data types, indexes, storage, partitioning, and platform features. Start with representative queries and transaction rates rather than indexing every column. An index may speed a lookup while adding write and storage costs. A denormalized summary may improve a costly report, but it creates a consistency problem that needs an update strategy. Measure on realistic data and explain which workload justified each choice.

Concurrency and failure are part of performance. Two users may update stock at once; a transaction should preserve the intended invariant. A backup that has never been restored in a test is an unverified recovery plan. Define recovery objectives and test that the system can reconstruct both data and essential relationships. Capacity planning should account for growth in rows, retention, and peak periods, not only a demonstration dataset.

Walk through a small order-system case

Suppose a service sells items online and must report revenue by product category. The conceptual model identifies customers, orders, products, categories, and order lines. The logical model gives each order line a reference to one order and one product, quantity, and the agreed unit price. A product’s current price can change without rewriting historical sales. An order can have several lines, while a category assignment may need a rule for products that move categories over time.

Test the model with operations: create an order with two items, change a customer’s current address, refund one line, retire a product, and reproduce last month’s report. If the report changes unexpectedly after a category edit, the team must decide whether historical category or current category is intended. That question may require a dated relationship or snapshot. The test reveals a missing business rule before deployment, when changing the schema is still relatively cheap.

Validate with evidence and document trade-offs

Review sample records with users, exercise constraints with invalid inputs, and run representative queries at expected scale. Check that reporting definitions match the conceptual model and that permissions reflect actual roles. Log the decisions behind keys, history, and deliberate redundancy. If the team chooses a simpler design for a known small workload, state the growth threshold that would prompt reconsideration. The result is a model that supports the organization’s operations and can be revised for reasons everyone can understand.