Relational Modeling and MySQL Schema Design

Introduction

A database schema is the formal structure through which an application stores, relates, protects, and retrieves data. In a full-stack application, schema design is not merely a matter of creating tables. It is a process of translating business requirements into entities, attributes, relationships, keys, constraints, and rules that remain reliable as the system grows.

Relational modeling provides a disciplined way to represent data as relations, commonly implemented as tables in MySQL. Good relational design reduces duplication, prevents contradictory data, supports efficient queries, and makes application behavior easier to understand. Poor design produces update anomalies, fragile code, inconsistent records, and difficult migrations.

This chapter develops a practical method for designing MySQL schemas from application requirements. It introduces entity–relationship modeling, cardinality, keys, constraints, normalization, denormalization, and implementation decisions. A case study demonstrates how these concepts work together in an application context.

Learning Objectives

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

• identify entities, attributes, and relationships from requirements;
• distinguish candidate, primary, alternate, natural, and surrogate keys;
• model one-to-one, one-to-many, and many-to-many relationships;
• interpret cardinality and optionality;
• create and read an entity–relationship diagram;
• apply first, second, and third normal forms;
• implement a maintainable schema using MySQL data types and constraints;
• evaluate when controlled denormalization is appropriate; and
• justify schema decisions in professional design documentation.

1. The Relational Model

The relational model represents information using relations. In implementation, a relation is usually represented by a table, a tuple by a row, and an attribute by a column. For example, a customer table may contain one row per customer and columns such as customer_id, name, and email.

A table should represent one coherent subject or relationship. A customer table should not also contain repeated product columns, and an order table should not contain a variable number of product fields such as product_1, product_2, and product_3. Relational design uses separate tables and relationships to represent such structures.

A useful design principle is:

Store each important fact once, in the table whose subject the fact describes.

This principle is refined by normalization, constraints, and performance analysis. It does not mean that every value must physically occur only once in an entire database; for example, a foreign key value is deliberately repeated to represent relationships.

Important terminology

• Relation: A logical table of records.
• Tuple: A row in a relation.
• Attribute: A named property represented by a column.
• Domain: The permitted set of values for an attribute.
• Schema: The structure of tables, columns, relationships, constraints, and indexes.
• Instance: The actual data stored at a particular time.
• Entity: A distinguishable real-world or business object.
• Entity type: A category of similar entities, such as Customer or Product.

The relational model is based on sets conceptually, but practical database systems may allow duplicate rows unless uniqueness is enforced. Consequently, primary keys and unique constraints are essential for preserving identity and data quality.

2. From Requirements to a Data Model

Schema design begins with requirements, not SQL syntax. A designer should extract nouns, actions, rules, and quantities from descriptions of the application.

Consider the requirement: “A customer can place many orders. Each order contains one or more products, and the quantity and selling price must be recorded for every product in the order.”

The nouns suggest Customer, Order, and Product. The phrase “can place many” indicates a one-to-many relationship. The phrase “each order contains products” indicates a many-to-many relationship between orders and products. Quantity and selling price belong to the association between an order and a product, not to the product generally. This requires an associative entity, often called OrderItem.

A practical analysis sequence is:

1. Identify business objects and events.
2. List facts that must be stored about each object.
3. Identify how objects interact.
4. Determine whether each relationship is mandatory or optional.
5. Identify candidate identifiers.
6. State business rules explicitly.
7. Normalize the model before considering performance optimizations.
8. Map the logical model to MySQL tables.
9. Test the design against realistic operations and edge cases.

Requirements should be expressed as testable rules. “A customer has an email address” is incomplete unless the design clarifies whether the address is required, whether it must be unique, and whether comparisons are case-sensitive.

3. Entities and Attributes

An entity is something about which the application needs to retain information. Entities may be physical objects, people, places, events, or concepts. Typical entities include Student, Course, Payment, Booking, and Shipment.

An attribute describes an entity. Attributes should be atomic for the intended application. A full_name column may be acceptable for display, but separate first_name and last_name may be needed for searching, sorting, or formal communication. The correct decision depends on requirements.

Attributes can be classified as:

• Simple: Cannot be meaningfully divided for the application, such as a country code.
• Composite: Has meaningful components, such as an address.
• Single-valued: One value per entity, such as a date of birth.
• Multivalued: Several values per entity, such as multiple phone numbers. These usually require a separate table.
• Stored: Directly recorded, such as unit price.
• Derived: Calculated from other values, such as order total.
• Optional: May be absent, such as a middle name.
• Mandatory: Must be present, such as an order creation timestamp.

A derived value need not always be stored. Storing it can improve read performance, but it creates a consistency obligation. If an order total is stored, the system must ensure that it agrees with the order items.

4. Keys and Identity

Keys identify rows and support relationships.

Candidate and primary keys

A candidate key is a minimal attribute or combination of attributes that uniquely identifies a row. A table may have several candidate keys. One is selected as the primary key. Other candidate keys are alternate keys, commonly implemented with UNIQUE constraints.

A key is minimal when no attribute can be removed without losing uniqueness. For example, if email uniquely identifies users, (user_id, email) is not a minimal candidate key.

Natural and surrogate keys

A natural key has business meaning, such as a national registration number or an ISBN. A surrogate key is generated solely for database identity, such as an auto-incrementing integer or UUID.

Surrogate keys are often useful because business values can change, may be long, or may contain privacy-sensitive information. They do not eliminate the need to enforce natural uniqueness. For example, a users table might use user_id as its primary key while enforcing UNIQUE(email).

Foreign keys

A foreign key is a column or column set whose values reference a candidate key in another table. It represents a relationship and protects referential integrity. A foreign key value must normally match an existing referenced value or be NULL when the relationship is optional.

A foreign key should reference a candidate key, typically a primary key or a unique key. Its data type and relevant attributes should be compatible with the referenced column.

5. Relationships, Cardinality, and Optionality

A relationship describes an association between entity types. Cardinality states how many instances may participate. Optionality, sometimes called participation, states whether participation is required.

The common relationship types are:

A one-to-many relationship between customers and orders is implemented by placing customer_id in orders. A customer may have zero or many orders, while each order belongs to exactly one customer. If an order could exist without a customer, the foreign key would be nullable; that is a different business rule and should not be assumed.

A many-to-many relationship between courses and students requires an associative table such as enrollment. Its primary key may be (course_id, student_id), or it may use a surrogate enrollment_id while retaining a unique constraint on (course_id, student_id).

Figure: A customer–order–product model using an associative OrderItem entity.

In this model, OrderItem is not merely a technical bridge. It records facts that belong to the relationship: quantity and the price charged at the time of purchase. The current product price may change later, so historical unit price should generally be stored on the order item.

6. Constraints as Business Rules

Constraints move important validation into the database, where it can protect data regardless of which application or script writes it.

Common MySQL constraints include:

• PRIMARY KEY: uniquely identifies each row and is implicitly non-null.
• FOREIGN KEY: enforces referential integrity.
• UNIQUE: prevents duplicate values or combinations.
• NOT NULL: requires a value.
• CHECK: restricts values according to a Boolean expression.
• DEFAULT: supplies a value when one is not provided.

For example, CHECK (quantity > 0) expresses a domain rule. In current MySQL versions, enforced CHECK constraints should be used deliberately; database version and deployment configuration must be verified.

Referential actions require particular care. ON DELETE CASCADE deletes dependent rows automatically and may be appropriate for order items belonging exclusively to an order. It may be dangerous for audit records or financial transactions. RESTRICT or NO ACTION can prevent deletion when dependent records exist, while SET NULL requires a nullable foreign key and a clear interpretation of the missing parent.

Indexes are not constraints, although unique indexes can enforce uniqueness. Indexes improve retrieval but add storage and write overhead. Foreign key columns, search columns, and columns used in joins may need indexes, but indexing every column is poor practice.

7. Normalization

Normalization is a method for organizing relations to reduce redundancy and modification anomalies. It is based on functional dependencies: if attribute set X determines attribute Y, written informally as X → Y, then each X value should determine one Y value in the relevant relation.

First normal form

A relation is in First Normal Form (1NF) when each field contains one atomic value for the intended domain and repeating groups are removed. A row should not contain a list such as “red, blue, green” if individual colors must be searched or managed. A separate related table may be required.

Second normal form

A relation is in Second Normal Form (2NF) when it is in 1NF and every non-key attribute depends on the whole candidate key, not merely part of a composite key. Partial dependencies matter primarily when a table has a composite key.

Suppose Enrollment(student_id, course_id, student_name, course_title, grade) has key (student_id, course_id). student_name depends only on student_id, and course_title depends only on course_id. These partial dependencies cause duplication. Decompose into Student, Course, and Enrollment.

Third normal form

A relation is in Third Normal Form (3NF) when it is in 2NF and non-key attributes do not depend transitively on the key through another non-key attribute. For example, Employee(employee_id, department_id, department_name) has employee_id → department_id and department_id → department_name. Department name belongs in a Department table.

Normalization is not a mechanical guarantee of quality. Designers must understand the meaning of facts, keys, and dependencies. A table can satisfy superficial structural rules while still representing the wrong business concepts.

Anomalies

• Insertion anomaly: A fact cannot be inserted without an unrelated fact.
• Update anomaly: The same fact must be changed in multiple rows.
• Deletion anomaly: Removing one fact accidentally removes another required fact.

Normalization reduces these risks by separating subjects and using foreign keys to relate them.

8. Mapping a Logical Model to MySQL

A logical model must be translated into data types and SQL definitions. Choose the smallest type that accurately represents the domain, but do not sacrifice valid range or meaning.

Typical choices include:

For DECIMAL(p,s), p is total precision and s is the number of digits after the decimal point. Thus, DECIMAL(10,2) permits up to ten total digits, including two fractional digits. Currency requirements, maximum values, and rounding policy should determine the choice.

Character set and collation affect storage, comparison, sorting, and uniqueness. New applications should normally use utf8mb4 and select a collation appropriate to language and case-sensitivity requirements. Email uniqueness, for example, requires an explicit policy rather than an assumption about case.

A maintainable example schema is:

CREATE TABLE customers (
    customer_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    email VARCHAR(254) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uq_customers_email UNIQUE (email)
) ENGINE = InnoDB;

CREATE TABLE products (
    product_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(160) NOT NULL,
    current_price DECIMAL(10,2) NOT NULL,
    active BOOLEAN NOT NULL DEFAULT TRUE,
    CONSTRAINT chk_products_price CHECK (current_price >= 0)
) ENGINE = InnoDB;

CREATE TABLE orders (
    order_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id BIGINT UNSIGNED NOT NULL,
    ordered_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    CONSTRAINT chk_orders_status
        CHECK (status IN ('pending', 'paid', 'cancelled', 'completed'))
) ENGINE = InnoDB;

CREATE TABLE order_items (
    order_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    quantity INT UNSIGNED NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (order_id, product_id),
    CONSTRAINT fk_items_order
        FOREIGN KEY (order_id) REFERENCES orders(order_id)
        ON DELETE CASCADE,
    CONSTRAINT fk_items_product
        FOREIGN KEY (product_id) REFERENCES products(product_id),
    CONSTRAINT chk_items_quantity CHECK (quantity > 0),
    CONSTRAINT chk_items_price CHECK (unit_price >= 0)
) ENGINE = InnoDB;

The composite primary key prevents the same product from appearing twice in one order. If the business permits separate lines for the same product—for example, different discounts or fulfilment sources—an order_item_id may be added and a different uniqueness rule selected.

9. Worked Design Example

Assume an education application has these requirements:

1. A student may enroll in many courses.
2. A course may have many students.
3. A student may enroll in the same course only once per academic term.
4. Each enrollment records the enrollment date and grade.
5. Courses are taught by one instructor, while an instructor may teach many courses.

The entities are Student, Course, Instructor, Term, and Enrollment. Student–course is many-to-many, so Enrollment is an associative entity. The rule “only once per course per term” produces a candidate key (student_id, course_id, term_id), which should be enforced with a unique constraint or used as the primary key.

A suitable design is:

CREATE TABLE enrollment (
    student_id BIGINT UNSIGNED NOT NULL,
    course_id BIGINT UNSIGNED NOT NULL,
    term_id BIGINT UNSIGNED NOT NULL,
    enrolled_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    grade DECIMAL(5,2) NULL,
    PRIMARY KEY (student_id, course_id, term_id),
    CONSTRAINT chk_enrollment_grade
        CHECK (grade IS NULL OR (grade >= 0 AND grade <= 100))
);

The grade is nullable because it may not exist before assessment. The constraint permits a missing grade but restricts an entered grade to the stated range. Whether a percentage is the correct grading representation is a policy decision; some institutions require letter grades or a separate assessment model.

10. Case Study: Illustrative Online Bookshop

Background

This illustrative case concerns a small online bookshop that initially stores each order in a single spreadsheet-like table containing customer details, product details, quantity, and payment status.

Problem

The design repeats customer and product information for every order line. Updating a customer email requires many edits. A product cannot be recorded before it is ordered, and deleting the last order containing a product removes useful product information. The application also needs historical selling prices, which differ from current catalogue prices.

Analysis

The principal entities are Customer, Product, Order, OrderItem, and Payment. Customer–Order is one-to-many. Order–Product is many-to-many and therefore requires OrderItem. Payment may be one-to-one with Order if the business permits only one final payment record, or one-to-many if retries, refunds, and partial payments must be retained. The latter is usually more realistic.

The current product price belongs to Product. The price actually charged belongs to OrderItem. This separation preserves historical accuracy. Payment status should not be treated as a substitute for an auditable payment transaction table.

Approach and solution

The schema is normalized to at least 3NF. Primary keys are generated identifiers, while email and any externally defined product code receive unique constraints. Foreign keys enforce relationships. Transaction processing should insert the order and its items atomically: either all required records are committed or none are.

The order total can be calculated as the sum of each item’s quantity multiplied by its unit price:

Here, i identifies an order item, quantityᵢ is the number of units on item i, and unit_priceᵢ is the historical price charged per unit. The formula assumes prices are expressed in the same currency and that tax, shipping, discounts, and refunds are either included in the item price or represented separately. For financial systems, rounding rules must be specified rather than left to application coincidence.

Outcome

The redesigned database supports consistent updates, reliable reporting, product catalogue management, and historical order reconstruction. Queries require joins, but the additional structure is justified by correctness and maintainability.

Learning points

• Events such as orders often require their own entities.
• Relationship attributes belong on associative entities.
• Current values and historical transaction values are not interchangeable.
• Payment records should model real business events rather than merely duplicate an order status.
• Constraints are part of the design, not an afterthought.

11. Denormalization and Performance

Denormalization intentionally introduces controlled redundancy to improve performance, simplify frequent reads, or support reporting. It should occur only after measuring a real problem and understanding the consistency cost.

Examples include storing a cached order total, maintaining a summary table, or copying a product name into a reporting fact table. Each duplicated value requires a synchronization strategy. Options include transactional updates, scheduled refreshes, triggers, materialized-view-like processes, or event-driven pipelines.

Denormalization is justified when:

• a query is demonstrably expensive;
• the duplicated value has a clear source of truth;
• acceptable staleness is defined;
• update logic is testable; and
• storage and maintenance costs are acceptable.

It is not justified merely because joins appear inconvenient. Premature denormalization often creates contradictory data and makes future requirements harder to implement.

12. Transactions, Security, and Governance

Schema design interacts with transaction management. A transaction should preserve atomicity, consistency, isolation, and durability (ACID). For example, creating an order and its order items should normally occur in one transaction. Isolation level, locking, and deadlock handling must be considered in high-concurrency systems.

Security should influence schema decisions. Store only necessary personal data, restrict database accounts by least privilege, and avoid storing plaintext passwords. Passwords must be handled using an established password-hashing algorithm in the application layer. Sensitive data may require encryption at rest, controlled access, retention limits, and audit logging.

Regulatory obligations vary by jurisdiction and sector. Privacy principles commonly require purpose limitation, data minimization, accuracy, retention control, and appropriate access safeguards. A schema should support deletion or anonymization policies without destroying legally required records. Audit requirements may make cascading deletion inappropriate.

Artificial intelligence can assist with schema suggestions, SQL generation, and test-data creation, but generated designs require human review. AI tools may reproduce incorrect assumptions, expose confidential requirements, or omit constraints. They should not replace requirement analysis, threat modeling, or data governance.

13. Best Practices and Common Errors

Best practices include:

• name tables and columns consistently;
• use singular or plural naming deliberately and document the convention;
• give every table a clear purpose;
• define primary keys and foreign keys explicitly;
• enforce important business rules with constraints;
• avoid storing comma-separated lists in columns;
• distinguish current facts from historical facts;
• document nullability and deletion behavior;
• use migrations and version control for schema changes;
• test inserts, updates, deletes, boundary values, and concurrent operations; and
• inspect query plans before adding indexes or denormalizing.

Common errors include using a nullable foreign key where participation is mandatory, confusing a unique attribute with a primary key, storing calculated values without a refresh strategy, choosing FLOAT for exact currency, and using CASCADE deletion without considering audit requirements.

A schema review should ask: What does one row represent? What uniquely identifies it? Which facts depend on that key? What happens when the parent is deleted? Can invalid data enter through another application? Can the design preserve historical truth? These questions often reveal defects before implementation.

Chapter Summary

Relational modeling translates application requirements into entities, attributes, relationships, keys, and constraints. Entities represent subjects or events; attributes describe them; relationships connect them. Cardinality and optionality express how many records may participate and whether participation is required. Primary keys identify rows, candidate and alternate keys enforce uniqueness, and foreign keys protect referential integrity.

Normalization, especially 1NF, 2NF, and 3NF, reduces redundancy and insertion, update, and deletion anomalies. Many-to-many relationships require associative entities, where relationship-specific facts such as quantity, grade, or historical price belong. MySQL implementation requires deliberate choices about types, character sets, nullability, indexes, foreign-key actions, and constraints.

Denormalization can improve measured performance but introduces consistency obligations and should be controlled. Transactions, security, privacy, auditability, and migration practices are essential parts of professional schema design. A maintainable schema is one whose structure clearly represents business meaning and whose constraints protect that meaning over time.

Key Terms

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

Attribute: A property describing an entity.

Relation: A logical table in the relational model.

Tuple: A row in a relation.

Domain: The permitted set of values for an attribute.

Schema: The structure of database objects, relationships, and rules.

Candidate key: A minimal attribute set that uniquely identifies a row.

Primary key: The candidate key selected as the principal row identifier.

Alternate key: A candidate key not selected as the primary key.

Surrogate key: A generated identifier without direct business meaning.

Natural key: An identifier with business meaning.

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

Cardinality: The permitted number of related instances.

Optionality: Whether participation in a relationship is required.

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

Normalization: Organizing relations to reduce redundancy and anomalies.

Functional dependency: A rule in which one attribute set determines another.

Constraint: A database rule restricting valid data.

Denormalization: Deliberate introduction of redundancy for a justified purpose.

Referential integrity: The consistency of references between related tables.

Review Questions

Short Answer Questions

1. What is the difference between an entity and an attribute?
2. Why is an associative entity required for a many-to-many relationship?
3. Distinguish a primary key from a foreign key.
4. What problem does a NOT NULL constraint address?
5. State the main purpose of normalization.

Long Answer Questions

1. Explain how requirements can be transformed into entities, attributes, relationships, and constraints.
2. Compare natural keys and surrogate keys, including advantages and risks of each.
3. Explain 1NF, 2NF, and 3NF using a student-enrollment example.
4. Discuss how foreign-key deletion actions affect data integrity and auditability.
5. Explain why historical transaction prices should normally be stored separately from current product prices.

Analytical / Application Questions

1. Design tables and relationships for a clinic in which patients book appointments with doctors. State cardinalities, keys, and important constraints.
2. A single table stores student details, course details, instructor details, and grades for every enrollment. Identify likely anomalies and propose a normalized decomposition.
3. An order-reporting query is slow after the database grows significantly. Propose a measurement-based process for deciding whether to add indexes, cache totals, or denormalize.

Multiple Choice Questions with Answers

Question 1

Which structure is normally required to represent a many-to-many relationship between students and courses?

A. A nullable column containing course names in Student

B. An associative table such as Enrollment

C. A second primary key in Student

D. A database view without stored rows

Correct Answer: B. An associative table such as Enrollment

Question 2

What is the main purpose of a foreign key?

A. To sort rows automatically

B. To encrypt sensitive values

C. To enforce a valid reference to another table

D. To calculate derived attributes

Correct Answer: C. To enforce a valid reference to another table

Question 3

Which normal form removes partial dependencies on part of a composite key?

A. First normal form

B. Second normal form

C. Third normal form

D. Fourth normal form

Correct Answer: B. Second normal form

Question 4

Where should the historical selling price normally be stored in an order schema?

A. Only in Product

B. Only in Customer

C. In OrderItem

D. In the database index

Correct Answer: C. In OrderItem

Question 5

Which constraint prevents two customers from having the same email value?

A. CHECK

B. DEFAULT

C. FOREIGN KEY

D. UNIQUE

Correct Answer: D. UNIQUE

Question 6

In a one-to-many customer–order relationship, where is the foreign key usually placed?

A. In the customer table

B. In the order table

C. In a separate index-only table

D. In both tables as primary keys

Correct Answer: B. In the order table

Question 7

Why is DECIMAL generally preferred to FLOAT for exact currency amounts?

A. DECIMAL stores arbitrary text

B. DECIMAL avoids the same type of binary approximation used by floating point

C. DECIMAL automatically applies tax

D. DECIMAL requires no precision decision

Correct Answer: B. DECIMAL avoids the same type of binary approximation used by floating point

Question 8

What does a CHECK constraint such as CHECK (quantity > 0) enforce?

A. Every quantity must be a positive value

B. Every quantity must be unique

C. Quantity must reference another table

D. Quantity must be stored as text

Correct Answer: A. Every quantity must be a positive value

Question 9

Which situation is a deletion anomaly?

A. A query returns rows in a different order

B. Deleting the last order removes the only stored record of a product

C. An index consumes additional storage

D. A foreign key prevents an invalid insert

Correct Answer: B. Deleting the last order removes the only stored record of a product

Question 10

When is denormalization most defensible?

A. Before requirements are analyzed

B. Whenever joins exist

C. After a measured performance problem and with a consistency strategy

D. Whenever a table has more than five columns

Correct Answer: C. After a measured performance problem and with a consistency strategy