Data Modeling and Database Implementation

Introduction

A web application is only as reliable as the data it stores and the rules that protect that data. User accounts, products, bookings, payments, messages, and audit records must be represented in a way that is accurate, searchable, secure, and maintainable. Data modeling is the disciplined process of deciding what data an application needs, how data items relate to one another, and which rules govern their creation, modification, and deletion. Database implementation converts that model into tables, collections, constraints, indexes, queries, migrations, and operational procedures.

For full-stack developers, database work is not separate from application design. Poor modeling produces duplicate data, inconsistent records, slow queries, difficult migrations, and security risks. Good modeling supports clear business rules, efficient development, reliable reporting, and future change.

This chapter focuses on relational databases while also explaining when a document-oriented or other NoSQL approach may be suitable. The emphasis is practical: students should be able to move from a business problem to a conceptual model, logical schema, implemented database, and responsible maintenance plan.

Learning Objectives

By the end of this chapter, you should be able to:

• identify entities, attributes, keys, and relationships;
• represent cardinality and participation constraints;
• convert a conceptual model into a relational schema;
• apply first, second, and third normal forms appropriately;
• define primary keys, foreign keys, unique constraints, checks, and defaults;
• design indexes based on realistic query patterns;
• write representative SQL for creating and using a schema;
• explain transactions and the ACID properties;
• compare relational and document-oriented approaches;
• use migrations, validation, backups, and retention policies responsibly;
• recognize privacy, security, and governance obligations in database design.

1. From Business Requirements to Data Models

Database design begins with questions about the business rather than with a favourite database product. A developer should first identify the application’s important objects and events.

For an online course platform, likely objects include Student, Course, Instructor, Enrollment, Lesson, Assignment, Submission, and Payment. A requirement such as “a student can enroll in many courses, and a course can have many students” indicates a many-to-many relationship. A requirement such as “each submission belongs to one assignment and one student” indicates two many-to-one relationships from Submission.

1.1 Entities, attributes, and identifiers

An entity is a distinguishable object or event about which the system stores information. An entity type is the general category, such as Customer; an entity instance is one particular customer.

An attribute describes an entity. Customer may have customer_id, full_name, email, and created_at. An attribute should be atomic for the intended use. Storing a complete address in one field may be acceptable if the application never searches by city or postal code; otherwise, separate attributes are more useful.

An identifier, commonly called a key, distinguishes one row from another. A primary key should be unique, stable, and not unnecessarily meaningful. A generated integer or UUID is often preferable to using an email address, because emails can change and may reveal personal information. A candidate key is any attribute or combination that could uniquely identify a row. A surrogate key is an artificial identifier introduced by the designer.

1.2 Relationships and cardinality

A relationship describes an association between entities. Cardinality specifies how many instances may participate:

• One-to-one: one Passport belongs to one Person, subject to the business rule.
• One-to-many: one Customer may place many Orders; each Order belongs to one Customer.
• Many-to-many: Students enroll in Courses. This is normally implemented through an associative entity such as Enrollment.

Participation may be mandatory or optional. An Order must have a customer, but a Customer may have no orders. These distinctions should be recorded explicitly because they become nullability and foreign-key decisions in the implementation.

Figure: An associative entity resolves the many-to-many relationship between orders and products.

2. Relational Schema Design

A relational database stores data in relations, normally represented as tables. Each table has rows, called tuples, and columns, called attributes. A relational schema describes table names, columns, data types, keys, and constraints.

A typical order schema is:

• Customer(customer_id, email, full_name, created_at)
• Product(product_id, name, unit_price, active)
• Order(order_id, customer_id, status, ordered_at)
• OrderItem(order_item_id, order_id, product_id, quantity, unit_price)

OrderItem is important because it records the products associated with each order. It also stores the price at the time of purchase. If Product.unit_price later changes, historical orders remain correct.

2.1 Constraints

Constraints express rules that the database enforces consistently, regardless of which application or script writes the data.

• PRIMARY KEY: uniquely identifies a row and normally implies non-nullability.
• FOREIGN KEY: requires a value to refer to an existing row in another table.
• NOT NULL: requires a value.
• UNIQUE: prevents duplicate values or duplicate combinations.
• CHECK: restricts values according to a predicate.
• DEFAULT: supplies a value when none is provided.

Constraints should be implemented in the database and also supported by application validation. Application validation gives users helpful messages; database constraints provide the final integrity boundary.

Example PostgreSQL-style SQL is shown below:

CREATE TABLE customers (
    customer_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email VARCHAR(320) NOT NULL UNIQUE,
    full_name VARCHAR(120) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
    product_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    unit_price NUMERIC(12,2) NOT NULL CHECK (unit_price >= 0),
    active BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE orders (
    order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id BIGINT NOT NULL REFERENCES customers(customer_id),
    status VARCHAR(20) NOT NULL CHECK (status IN ('pending','paid','cancelled')),
    ordered_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_items (
    order_item_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id BIGINT NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
    product_id BIGINT NOT NULL REFERENCES products(product_id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price NUMERIC(12,2) NOT NULL CHECK (unit_price >= 0),
    UNIQUE (order_id, product_id)
);

The choice of ON DELETE behavior requires care. CASCADE may be appropriate for dependent order items if an order is removed, but deleting historical orders may conflict with accounting, legal, or audit requirements. In many production systems, orders are never physically deleted; instead, they are cancelled or archived.

3. Normalization and Controlled Denormalization

Normalization organizes data to reduce unnecessary duplication and update anomalies. It is not a rule that every database must be maximally normalized; it is a reasoning tool for producing dependable structures.

3.1 First normal form

A relation is in first normal form when each field contains an atomic value for the intended domain and repeating groups are removed. A table with columns product_1, product_2, and product_3 violates this principle because the number of products is built into the schema. A separate OrderItem table represents an arbitrary number of products correctly.

3.2 Second normal form

A relation is in second normal form when it is in first normal form and every non-key attribute depends on the whole candidate key. This matters especially with composite keys. If Enrollment has composite key (student_id, course_id), then grade depends on the complete enrollment, whereas student_name depends only on student_id and should be stored in Student.

3.3 Third normal form

A relation is in third normal form when it is in second normal form and non-key attributes do not depend transitively on another non-key attribute. For example, storing department_id and department_name in Employee can cause inconsistency if department_name changes in one row but not another. Department should be a separate table referenced by Employee.

Normalization reduces insertion, update, and deletion anomalies:

• An insertion anomaly occurs when a fact cannot be recorded without an unrelated fact.
• An update anomaly occurs when one fact must be changed in multiple rows.
• A deletion anomaly occurs when removing one fact unintentionally removes another.

3.4 Denormalization

Denormalization intentionally duplicates or precomputes data to improve read performance or simplify reporting. Examples include storing a cached order_total or maintaining a summary table. It introduces consistency obligations. A denormalized value must have a clear update strategy, such as a transaction, scheduled rebuild, or materialized view. Denormalization should be justified by measured workload evidence, not assumption.

4. Data Types, Nulls, and Domain Decisions

Data types communicate meaning and prevent invalid representation. Use numeric decimal types for monetary values rather than binary floating-point types when exact currency arithmetic is required. Store timestamps with timezone awareness when records cross geographical regions. Use a suitable text or Unicode type for international names and messages.

NULL does not mean zero, empty text, false, or unknown in every context. It represents missing or inapplicable information. SQL’s three-valued logic means a comparison involving NULL may evaluate to UNKNOWN. Therefore, use IS NULL or IS NOT NULL rather than equality comparisons with NULL.

A design should distinguish:

• unknown value: information has not been supplied;
• inapplicable value: the attribute does not apply;
• empty value: a valid value exists but contains no characters;
• default value: a value deliberately supplied by system policy.

5. Indexes and Query Performance

An index is an auxiliary data structure that helps the database locate rows without scanning the entire table. Indexes improve selected reads but consume storage and impose maintenance cost on inserts, updates, and deletes.

Common indexes include:

• B-tree indexes for equality, ordering, and range queries;
• unique indexes for enforcing uniqueness;
• composite indexes over multiple columns;
• full-text or specialized indexes for text search, where supported;
• partial or filtered indexes for a subset of rows, where supported.

Index design should follow actual queries. If the application frequently runs WHERE customer_id = ? ORDER BY ordered_at DESC, an index beginning with customer_id and continuing with ordered_at may help. Column order matters in composite indexes; the most useful leading columns should correspond to filtering, joining, or ordering patterns. The database query planner and an execution-plan tool should be used to verify the benefit.

CREATE INDEX idx_orders_customer_date
    ON orders (customer_id, ordered_at DESC);

CREATE INDEX idx_order_items_product
    ON order_items (product_id);

Do not index every column. Low-selectivity columns, such as a boolean active flag, may not benefit from a conventional index unless combined with other columns or used in a partial index. Indexes also require monitoring after data volume and workload change.

6. Transactions and Reliable Updates

A transaction groups related operations into one logical unit. Either all required changes become visible, or none do. The classic ACID properties are:

• Atomicity: the transaction is all-or-nothing.
• Consistency: committed changes preserve declared rules and application invariants.
• Isolation: concurrent transactions do not produce prohibited interference.
• Durability: committed data survives an appropriate failure, subject to the system’s durability configuration.

Consider placing an order. Creating the order, adding its items, and reducing inventory should not leave a half-completed result. A simplified transaction is:

BEGIN;

INSERT INTO orders (customer_id, status)
VALUES (42, 'pending')
RETURNING order_id;

-- The application uses the returned order_id.
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (1001, 7, 2, 19.99);

UPDATE products
SET active = active
WHERE product_id = 7;

COMMIT;

The example illustrates transaction boundaries, but inventory should normally be represented in a dedicated inventory table and updated with a conditional statement that prevents overselling. If any operation fails, the application should issue ROLLBACK.

Isolation levels vary between database systems. Stronger isolation can reduce anomalies but may reduce concurrency or increase waiting. Developers should understand the database’s default behavior and explicitly handle conflicts, retries, and deadlocks where required.

7. SQL and NoSQL Approaches

Relational databases are usually appropriate when data has strong relationships, transactions, structured constraints, and reporting requirements. SQL provides declarative querying, joins, aggregation, and mature integrity mechanisms.

Document databases store related information as documents, often in JSON-like form. They can be effective when:

• records have flexible or evolving attributes;
• an aggregate is normally read and written as one document;
• horizontal distribution is a primary concern;
• joins are limited or undesirable.

A document representing a product catalogue item might embed localized names and display attributes. However, embedding large or frequently changing data can create duplication and document-size problems. Referencing separate documents may be more appropriate when entities have independent lifecycles.

The choice should follow access patterns, consistency requirements, team expertise, operational maturity, and regulatory needs. “NoSQL” is not synonymous with “faster,” and a relational database is not automatically unsuitable for large systems. Hybrid architectures are possible, but they increase operational and consistency complexity.

8. Migrations and Schema Evolution

A migration is a versioned, repeatable change to database structure or controlled reference data. Migrations allow a team to reproduce the schema in development, testing, staging, and production.

A safe migration commonly follows an expand-and-contract pattern:

1. Add a new nullable column or table without breaking old code.
2. Deploy application code that can read both old and new forms.
3. Backfill data in controlled batches.
4. Begin writing the new form.
5. Verify completeness and correctness.
6. Remove the old structure only after dependent code has been retired.

Large migrations should consider locks, transaction duration, replication lag, rollback strategy, and monitoring. Database changes must be reviewed like application code. Destructive operations require explicit approval and verified backups.

9. Validation, Security, Backup, and Retention

Validation belongs at multiple layers. Client-side validation improves usability but cannot be trusted. Server-side validation checks authorization, type, range, format, and business rules. Database constraints enforce durable integrity. Input must be parameterized to prevent SQL injection; concatenating untrusted strings into SQL is unsafe.

Access should follow least privilege. An application account should receive only the permissions it needs, rather than unrestricted administrative access. Secrets should be stored in a secure configuration or secret-management system, not committed to source control. Sensitive fields should be encrypted in transit and, where appropriate, at rest. Logging must avoid exposing passwords, authentication tokens, and unnecessary personal data.

A backup is useful only if it can be restored. A practical backup plan specifies frequency, retention period, encryption, storage separation, access controls, and restoration testing. Recovery objectives should be stated:

• Recovery Point Objective (RPO): the maximum acceptable amount of data loss measured in time.
• Recovery Time Objective (RTO): the target maximum time to restore service.

Privacy-aware retention means keeping data only for a justified purpose and period. A system should identify personal data, define retention schedules, support correction where applicable, restrict access, and securely delete or anonymize data when retention ends. Legal obligations may require preserving some records, so retention rules should be developed with organizational policy and applicable law rather than improvised by developers.

10. Illustrative Case Study: Campus Equipment Booking System

Background

A technical college wants a web application through which students and staff can reserve cameras, laptops, and laboratory equipment. The system must prevent overlapping bookings, record the responsible borrower, and provide administrators with an audit history.

Problem analysis

Initial requirements reveal these entities: User, Equipment, EquipmentType, Booking, BookingItem, and AuditEvent. One booking is made by one user, but a booking may contain several equipment items. An equipment item may appear in many bookings over time. Each physical item has a unique asset tag. A booking has start and end times and a lifecycle status.

A weak design might store equipment names in a comma-separated field in Booking. This prevents reliable searching, makes overlap checking difficult, and creates update anomalies. A normalized design uses BookingItem as an associative table.

Proposed schema

• User(user_id, institutional_email, role, created_at)
• EquipmentType(type_id, name)
• Equipment(equipment_id, type_id, asset_tag, condition_status)
• Booking(booking_id, user_id, start_at, end_at, status, created_at)
• BookingItem(booking_id, equipment_id)
• AuditEvent(event_id, actor_id, action, entity_name, entity_id, occurred_at)

Constraints require end_at to be later than start_at, asset_tag to be unique, and each BookingItem pair to be unique. Role values should be controlled, preferably through a reference table or database-supported domain rule.

Overlap rule

For a requested interval [new_start, new_end), an existing booking overlaps it when:

existing_start < new_end AND existing_end > new_start

The half-open interval convention allows a booking ending at 10:00 to be followed by another beginning at 10:00 without overlap. The application must check active bookings for each requested item. Because two users could pass an application-level check simultaneously, the final implementation should use an appropriate transaction and concurrency-control strategy. Depending on the database, this may include locking, serializable transactions, or an exclusion constraint over time ranges.

Outcome

The resulting model supports reliable availability searches, clear audit records, and future extensions such as approval workflows and maintenance blocks. The team also decides not to delete completed bookings because they may be needed for accountability. Instead, records are retained according to policy, while personal details are restricted and eventually anonymized where legally permissible.

Learning points

The case demonstrates that relationships and business rules must be modeled explicitly. A join table is preferable to repeating fields, application checks must be reinforced by database mechanisms, and operational requirements such as auditability and retention affect schema design from the beginning.

11. Contemporary Practice and Professional Implications

Modern applications increasingly use managed database services, infrastructure as code, observability tools, and automated migration pipelines. These tools reduce operational effort but do not remove the need for sound modeling. Cloud services still require decisions about region, backup, access control, encryption, cost, and vendor dependence.

AI-assisted coding can generate SQL and schema suggestions, but generated code must be reviewed for injection risks, incorrect constraints, privacy violations, inefficient indexes, and destructive migrations. Developers remain accountable for correctness and governance. A useful practice is to ask an AI tool for alternatives, then verify them against documentation, execution plans, tests, and organizational policy.

Professional database work also requires communication. Developers should document entity definitions, assumptions, ownership, migration risks, retention decisions, and known performance limits. A schema is not merely an implementation detail; it is an executable representation of organizational knowledge.

Challenges and Limitations

No model is permanently perfect. Requirements change, data volumes grow, and new access patterns emerge. Normalization may increase joins; denormalization may create synchronization problems. Strong consistency can increase latency, while eventual consistency may produce temporarily different views. Indexes can improve one query and slow writes. Backups can fail silently if restoration is never tested.

Database design also involves ambiguity. Business terms may have different meanings across departments. For example, “customer” could mean a person, an organization, or a billing account. Early workshops, examples, data dictionaries, and representative queries reduce this ambiguity. The design should be validated with realistic data and failure scenarios, not only idealized diagrams.

Best-Practice Checklist

• Begin with business rules and access patterns.
• Give every important entity a stable identifier.
• Represent many-to-many relationships with an associative table.
• Use database constraints for durable integrity.
• Normalize first; denormalize only for a measured reason.
• Choose data types deliberately, especially for money and timestamps.
• Create indexes from observed queries and verify them with execution plans.
• Use transactions for related changes and design for concurrency.
• Manage schema changes through reviewed, versioned migrations.
• Apply least privilege and parameterized queries.
• Classify data and define retention, deletion, and anonymization rules.
• Test backups by restoring them.
• Document assumptions and monitor production behavior.

Chapter Summary

Data modeling translates business requirements into entities, attributes, relationships, identifiers, and constraints. Relational schemas provide strong structure through tables, keys, foreign keys, and validation rules. Normalization reduces duplication and anomalies, while carefully justified denormalization may improve performance. Indexes should be designed from real query patterns and evaluated with execution plans. Transactions preserve the integrity of related operations through atomicity, consistency, isolation, and durability.

Relational and NoSQL systems offer different trade-offs; the appropriate choice depends on relationships, access patterns, consistency, flexibility, and operational requirements. Migrations make schema evolution controlled and reproducible. Validation, least privilege, parameterized queries, backup testing, privacy-aware retention, and auditability are essential parts of professional database implementation. Effective developers treat the database as a governed foundation of the whole web application.

Key Terms

Entity: A distinguishable object or event about which data is stored.

Attribute: A descriptive property of an entity.

Primary key: A column or set of columns that uniquely identifies each row.

Candidate key: Any attribute set capable of uniquely identifying rows.

Surrogate key: An artificial identifier generated for database use.

Foreign key: An attribute that references a key in another table.

Cardinality: The number of instances that may participate in a relationship.

Associative entity: An entity used to represent a many-to-many relationship.

Constraint: A rule enforced to protect data validity.

Normalization: Organizing data to reduce redundancy and anomalies.

Denormalization: Deliberate duplication or precomputation for a justified purpose.

Index: A data structure that accelerates selected data-access operations.

Transaction: A logical unit of database work.

ACID: Atomicity, consistency, isolation, and durability properties of transactions.

Migration: A versioned change to database structure or controlled data.

RPO: Maximum acceptable data loss measured as a time interval.

RTO: Target time for restoring service after disruption.

Data retention: The policy governing how long data is kept and when it is deleted or anonymized.

Least privilege: Giving an account only the access required for its duties.

Review Questions

Short Answer Questions

1. What is the purpose of a primary key?
2. Why is an associative entity used for a many-to-many relationship?
3. State the four ACID properties.
4. What problem does normalization address?
5. Why should database backups be restoration-tested?

Long Answer Questions

1. Explain how business requirements are transformed into a relational schema, using a booking system as an example.
2. Compare first, second, and third normal forms and describe the anomalies they help prevent.
3. Explain how constraints, application validation, and authorization work together to protect data.
4. Discuss the factors that should influence the choice between a relational database and a document database.
5. Describe an expand-and-contract migration and explain why it reduces deployment risk.

Analytical / Application Questions

1. Design a normalized schema for an online marketplace in which sellers list products and customers place orders containing multiple products. Identify keys, relationships, and at least five constraints.
2. A query filtering orders by customer and sorting by date has become slow. Propose an index, explain its column order, and state how you would verify its usefulness.
3. A booking application checks availability before inserting a reservation, but overlapping reservations still occur under heavy traffic. Explain why this happens and propose a transaction or concurrency-control solution.

Multiple Choice Questions with Answers

Question 1

Which database structure normally resolves a many-to-many relationship between students and courses?

A. A repeating group of course columns in Student

B. An associative table such as Enrollment

C. A single text field containing course names

D. A database index on Student

Correct Answer: B. An associative table such as Enrollment

Question 2

Which constraint prevents two customers from using the same email address?

A. CHECK

B. DEFAULT

C. UNIQUE

D. FOREIGN KEY

Correct Answer: C. UNIQUE

Question 3

What is the main purpose of normalization?

A. To remove all database indexes

B. To reduce redundancy and update anomalies

C. To make every table contain one row

D. To replace transactions with application code

Correct Answer: B. To reduce redundancy and update anomalies

Question 4

Which ACID property means that a transaction is all-or-nothing?

A. Atomicity

B. Consistency

C. Isolation

D. Durability

Correct Answer: A. Atomicity

Question 5

Why might OrderItem store unit_price even when Product has a current price?

A. To prevent all joins permanently

B. To preserve the historical price at purchase time

C. To make product IDs unnecessary

D. To allow duplicate primary keys

Correct Answer: B. To preserve the historical price at purchase time

Question 6

Which statement about indexes is correct?

A. Adding an index to every column always improves performance

B. Indexes can improve reads but add storage and write-maintenance costs

C. Indexes replace foreign-key constraints

D. Indexes guarantee that transactions cannot conflict

Correct Answer: B. Indexes can improve reads but add storage and write-maintenance costs

Question 7

For half-open booking intervals [start, end), which condition identifies an overlap?

A. existing_start < new_end AND existing_end > new_start

B. existing_start > new_end AND existing_end < new_start

C. existing_start = new_start OR existing_end = new_end only

D. existing_start < new_start AND existing_end < new_end only

Correct Answer: A. existing_start < new_end AND existing_end > new_start

Question 8

What does RPO describe?

A. The maximum acceptable amount of data loss measured in time

B. The number of indexes on a table

C. The time required to execute a query

D. The number of users allowed to connect

Correct Answer: A. The maximum acceptable amount of data loss measured in time

Question 9

Which practice most directly reduces SQL injection risk?

A. Concatenating user input into SQL strings

B. Using parameterized queries

C. Removing all foreign keys

D. Storing passwords in logs

Correct Answer: B. Using parameterized queries

Question 10

What is the safest general approach when changing a production schema used by old application code?

A. Immediately drop the old column

B. Use an expand-and-contract migration

C. Delete all existing records first

D. Disable database backups during deployment

Correct Answer: B. Use an expand-and-contract migration