Is database design just drawing tables?

No. Tables are one representation. Design also covers meaning, relationships, rules, access patterns and operational tradeoffs.

Should every database use a relational model?

No. Different models suit different requirements. Choosing another model does not remove the need to define structure and consistency expectations.

Can the design change after launch?

Yes. Changes should account for existing data, applications and migration behavior so that the intended meaning survives the transition.

AI Glossary / Data engineering and architecture / Beginner

Database design

Definition

Database design is the process of deciding how a database will represent, organize, relate and protect information so that it supports its intended uses. It includes choices about data structures, identifiers, relationships, constraints and access patterns. A good design reflects the meaning of the information as well as how applications need to work with it.

What it means

Before choosing tables or fields, a designer needs to understand the real-world facts being recorded. A person, an order and an order line are different things. Confusing them can make updates difficult and reports misleading even when the database software works perfectly.

Design also involves time. A customer's current address and the address used for last year's delivery answer different questions. An AI system drawing information from the database depends on these distinctions: a fluent answer cannot recover a historical fact that the design never retained.

How it works

Start with the database's purpose, users, required operations and important questions. Identify the things being described, their attributes and their relationships. This conceptual model should be understandable without committing to a particular product.

Translate that meaning into a logical design. In a relational database, choose tables, keys and relationships, define data types and state rules such as required values or uniqueness. Normalization helps examine dependencies and avoid problematic repetition. Other database models organize the information differently but still require deliberate decisions about meaning and access.

Physical design considers how the selected system stores and retrieves information, including indexes and partitioning where appropriate. Test the design with realistic examples and operations, including exceptions, corrections and deletion. Revise it when the tests expose ambiguity or unacceptable performance, and document intentional tradeoffs rather than leaving them implicit.

Three practical examples

1. A training provider separates learners, course offerings and enrollments. The design permits one learner to attend several offerings without overwriting earlier participation.

2. An online shop records the agreed unit price on each order line. Later changes to the current product price do not silently alter the meaning of a past sale.

3. An AI knowledge service stores document identifiers, versions and publication status. Retrieval can distinguish the current approved document from a superseded draft with similar wording.

Think of it this way

Think of designing a set of labeled filing drawers together with rules for cross-references and updates. The arrangement should help people find the right facts without creating conflicting copies. The analogy is limited: databases can enforce relationships and coordinate concurrent operations, while their performance also depends on execution plans and storage behavior invisible in a paper filing cabinet.

Worked example

Suppose a fictional workshop provider has two learners, L1 and L2, and two offerings, C1 and C2. L1 enrolls in both offerings; L2 enrolls only in C2. A Learners table holds two rows, an Offerings table holds two rows and an Enrollments table holds three rows: L1-C1, L1-C2 and L2-C2.

The learner and offering identifiers connect the enrollment to the appropriate records. If the business rule permits only one enrollment per learner per offering, the pair can be constrained to be unique. A reference to a nonexistent learner should be rejected through an appropriate relationship constraint.

The design now answers two distinct questions: there are three enrollments but only two learners. Counting enrollment rows as people would be wrong. If learners may repeat a course, the offering must identify the specific scheduled instance, or the model must otherwise represent repeat participation. Defining the row's meaning is more important than giving its table a plausible name.

Illustrative case study

Fictional case: RecordMeadow begins with one spreadsheet-like database row per customer and columns named Purchase1, Purchase2 and Purchase3. A fourth purchase requires another column, and reporting across purchases becomes awkward.

The team redesigns the structure around customers and individual purchases, linked by customer identifier. It defines how refunds, canceled purchases and historical prices are represented, then tests reports against hand-checked examples. It also checks that customer details can be updated without changing completed purchase facts.

The lesson extends beyond splitting a table: the redesign makes the business events explicit. It creates a clearer foundation for later analytics or AI assistance without claiming that structural changes alone ensure every record is accurate.

Common misconceptions and mistakes

Avoid designing solely around one screen. A convenient form layout may hide repeated groups or combine facts with different lifecycles. Do not assume an identifier proves two records represent different real-world entities; duplicate customer records can still have different generated keys.

Also avoid treating normalization as a command to eliminate every repeated value. Historical snapshots and deliberate performance structures can have legitimate purposes. Record the reason and maintenance rules. Finally, constraints enforce defined conditions, not all real-world truth: a valid date or existing customer reference can still describe the wrong event.

Business use

Database design influences maintenance costs, reporting consistency and the ability to introduce new features. Business owners should help define terms such as active customer, completed order and current document before engineers encode those meanings.

Review representative queries, update scenarios and exception cases together. Include access responsibilities and required history, not just today's fields. For an AI project, test whether the database can provide the evidence and context an answer needs. An accessible table is less useful when its rows lack a stable, shared interpretation.

Check your understanding

A report counts three enrollment rows and announces three learners, although one learner attends two offerings. What design concept helps explain the error?

Answer: The grain, or meaning, of a row. Each enrollment row represents a learner-offering relationship, not a distinct person. Count distinct learner identities when answering the people question.

Related terms

Learn next

Learn entity relationships and keys next. Model a small booking system and write down precisely what one row represents in each table. Test a cancellation, a repeat booking and a changed contact detail. Check that your design can answer both current and historical questions without confusing them.

Sources

Microsoft, Database design basics, explains purpose, tables, keys, relationships and iterative refinement: https://support.microsoft.com/en-us/access/database-design-basics

PostgreSQL, Constraints, documents mechanisms for expressing permitted values and relationships: https://www.postgresql.org/docs/current/ddl-constraints.html

The primary documentation was consulted for relational design concepts. The scenarios, worked model and fictional case are original teaching examples; broader database choices depend on project requirements.

Previous: Database connection Next: Database dump