Database Design
Database design is the process of translating business requirements into a schema: tables, columns, data types, keys, relationships, constraints, and indexes.
Concept
Database design is the process of translating business requirements into a schema: tables, columns, data types, keys, relationships, constraints, and indexes. It proceeds through three levels:
Requirements
↓
Conceptual model → entities and relationships (ER diagram), technology-independent
↓
Logical model → tables, columns, keys, normalization (relational, DBMS-independent)
↓
Physical model → data types, indexes, partitions, storage for a specific DBMS
↓
Implementation → DDL scripts / migrationsWhy It Matters
Schema mistakes are expensive to fix after data and applications depend on them. A good design keeps data correct, makes queries simple, and scales with the business.
Syntax
Design outputs are expressed as ER diagrams and DDL (CREATE TABLE, constraints, indexes).
Example
We design a small service-center database for vehicle repairs.
33.1 Requirement analysis Gather requirements through interviews, documents, and existing systems. Write them as clear statements:
The center services vehicles; each vehicle has a unique VIN, a registration number, a model, and a year.
Each vehicle has one current owner (customer); a customer may own several vehicles.
A customer has a name, phone, and optional email.
A vehicle can have many service visits; each visit has a date, odometer reading, and status.
During a visit, technicians perform one or more jobs from a catalogue (oil change, brake pad replacement), each with a standard price.
A job on a visit may use multiple parts; the price charged per part is recorded at the time of the visit.
Each visit is assigned to one service advisor (an employee).
Reports needed: visits per month, revenue per job type, parts usage, vehicles due for service.
Also clarify: data volumes, retention periods, who reads/writes what, performance expectations, privacy rules.
33.2 Entity identification Nouns that represent things we store data about: Customer, Vehicle, Employee, ServiceVisit, JobType, VisitJob, Part, VisitJobPart.
33.3 Attribute identification customer_id, full_name, phone, email vehicle_id, vin, registration_no, model, model_year, owner (customer) employee_id, full_name, role visit_id, vehicle, advisor, visit_date, odometer_km, status job_type_id, code, description, standard_price visit, job_type, charged_price, technician_notes part_id, part_no, description, unit_price, stock_qty visit_job, part, quantity, unit_price_charged
Check each attribute: atomic? derived (can be computed — then usually do not store)? optional? who owns the fact?
33.5 Primary key selection Use surrogate integer identity keys for most entities (vehicle_id), plus UNIQUE constraints on natural keys (vin, registration_no, part_no, job_types.code).
Junction tables: a surrogate key (visit_job_id) when other tables reference them (VisitJobPart references VisitJob), otherwise a composite key.
33.6 Foreign key design, constraints, and normalization Normalization check: owner name is not stored in vehicles (3NF); prices charged are stored on visit rows because they are historical facts, not redundant copies; the design is in 3NF.
33.7 ER diagram (text) 33.8 Naming conventions snake_case, plural (or singular — be consistent) snake_case, descriptive visit_date, odometer_km Foreign key column
Same name as referenced PK, or role-based name vehicle_id, advisor_id is_, has_ prefix _date, _at suffix visit_date, created_at
odometer_km, voltage_mv, duration_ms pk_, fk_, uq_, chk_, df_ prefixes ix_ / ux_ prefix
Reserved words, spaces, mixed case, abbreviations nobody understands order, User Data, tblCstmr 33.9 Data integrity checklist Every table has a primary key.
Every relationship is a foreign key.
Business identifiers have UNIQUE constraints.
Required fields are NOT NULL.
Value rules use CHECK constraints or lookup tables.
Monetary values use DECIMAL; timestamps use proper date/time types.
Audit columns (created_at, updated_at, created_by) exist where needed.
Soft deletes (deleted_at) are used only when history must be kept, and queries/filtered unique indexes account for them.
Customer 1 ──── N Vehicle
Vehicle 1 ──── N ServiceVisit
Employee 1 ──── N ServiceVisit (advisor)
ServiceVisit M ──── N JobType → VisitJob (junction with charged_price)
VisitJob M ──── N Part → VisitJobPart (junction with quantity, price)CREATE TABLE customers (
customer_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
full_name VARCHAR(100) NOT NULL,
phone VARCHAR(20) NOT NULL,
email VARCHAR(120),
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT uq_customers_phone UNIQUE (phone)
);
CREATE TABLE vehicles (
vehicle_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
vin CHAR(17) NOT NULL,
registration_no VARCHAR(15) NOT NULL,
model VARCHAR(60) NOT NULL,
model_year SMALLINT NOT NULL CHECK (model_year BETWEEN 1990 AND 2100),
owner_id BIGINT NOT NULL,
CONSTRAINT uq_vehicles_vin UNIQUE (vin),
CONSTRAINT uq_vehicles_reg UNIQUE (registration_no),
CONSTRAINT fk_vehicles_owner FOREIGN KEY (owner_id) REFERENCES customers(customer_id)
);
CREATE TABLE employees_sc (
employee_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
full_name VARCHAR(100) NOT NULL,
role VARCHAR(30) NOT NULL CHECK (role IN ('ADVISOR', 'TECHNICIAN', 'MANAGER'))
);
CREATE TABLE service_visits (
visit_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
…+-------------+ 1 N +-------------+ 1 N +-----------------+ N 1 +--------------+
| customers |------------| vehicles |------------| service_visits |------------| employees_sc |
+-------------+ +-------------+ +-----------------+ (advisor) +--------------+
| 1
| N
+-----------+ 1 N +-------------+
| job_types |-----------| visit_jobs |
+-----------+ +-------------+
| 1
| N
+-----------+ 1 N +-----------------+
| parts |-----------| visit_job_parts |
+-----------+ +-----------------+Expected Result
Vehicles due for service (last completed visit more than 180 days before 2026-09-29):
Returns one row per overdue vehicle with its owner and last service date (NULL for never serviced).
SELECT v.registration_no, c.full_name, MAX(sv.visit_date) AS last_service
FROM vehicles v
JOIN customers c ON c.customer_id = v.owner_id
LEFT JOIN service_visits sv ON sv.vehicle_id = v.vehicle_id AND sv.status = 'COMPLETED'
GROUP BY v.vehicle_id, v.registration_no, c.full_name
HAVING MAX(sv.visit_date) IS NULL
OR MAX(sv.visit_date) < DATE '2026-09-29' - INTERVAL '180 days' -- PostgreSQL; adapt date arithmetic per DBMS
ORDER BY last_service NULLS FIRST;Common Mistakes
Designing tables directly from screens/forms instead of from entities.
Storing derived or duplicated data without justification.
Missing constraints ("the app validates it").
Using generic "entity-attribute-value" tables for everything (hard to query and constrain).
Poor naming (data1, flag, tbl_x).
Ignoring history requirements (overwriting prices, owners, addresses).
Not considering data volume and access patterns for indexes.
Best Practices
Start from requirements and an ER diagram; review with stakeholders.
Normalize to 3NF, then denormalize only with evidence.
Enforce integrity in the database.
Adopt and document naming conventions.
Plan for history, auditing, and soft deletes deliberately.
Keep DDL in version-controlled migrations.
Interview Relevance
High for system-design and backend interviews: "Design a schema for X (library, ride-sharing, parking lot, e-commerce)", "How do you choose primary keys?", "How do you store history?", "What naming conventions do you use?".
Practice Questions
Design a schema for a parking lot with levels, spots, vehicles, and tickets.
Add ownership history to the vehicles design so that previous owners are kept.
Write a query for revenue per job type for the last 30 days.
Identify three business rules in the service-center design that are enforced by constraints.
What indexes would you add for "all visits of a vehicle, latest first"?