Lesson 1

Up to this point in the course, you have focused on the logical design phase of the database life cycle (DBLC). You have:

• Identified entities and relationships and built a normalized ER diagram
• Created base tables and enforced keys and constraints
• Designed joins and user views to present information
• Assigned permissions so that each user or role can safely work with the data

Logical design answers the question: “How should the data be structured so that it accurately represents the business?”

In Module 6, you move into the next major phase: physical database design. This introduction explains what physical design is, why it matters, and how the rest of the module will build on the logical model you have already created.

Physical database design focuses on how the database is implemented on actual hardware and within a specific DBMS. Where logical design is platform-independent, physical design is concerned with concrete implementation choices, including:

• How tables and indexes are stored on disk or SSD
• How rows and columns are organized to support typical queries
• How large tables are clustered or partitioned to improve performance and manageability
• How the data dictionary records these structures so the RDBMS can optimize access

The goal is to optimize performance, scalability, and reliability while still preserving the correctness of the logical model.

Unlike normalization,where the benefits are generally predictable—physical design decisions such as adding indexes or partitions involve trade-offs. A change that speeds up one workload can slow down another or increase storage and maintenance cost. For that reason, physical design is an experimental and iterative phase: you propose a design, measure its effect, and refine it based on real workload behavior.

This module provides a structured introduction to the core topics of physical database design. You will see how a logical schema is transformed into a concrete implementation and how to reason about performance-related choices in a relational database.
The lessons in Module 6 will show you how to:

• Use indexes to accelerate data retrieval and understand their trade-offs for write performance
• Apply clustering and related storage techniques to keep related rows physically close on disk
• Use horizontal partitioning to slice a large table into smaller, more manageable segments (for example, by date or region)
• Use vertical partitioning to split wide tables into narrower ones when column usage patterns differ
• Leverage SQL DDL to create databases and tables in ways that reflect your physical design choices
• Read and interpret entries in the data dictionary, where the RDBMS records physical structures and metadata

Throughout the module, you will connect each technique back to real-world goals: faster queries, predictable response times, and maintainable systems.

After completing this module, you should be able to:

1. Describe physical design^[1] and explain its role in optimizing database performance.
2. Explain the purpose and trade-offs of creating indexes^[2] on tables and views.
3. Explain the purpose and trade-offs of clustering^[3] related data on disk.
4. Explain the purpose and trade-offs of horizontal partitioning^[4] for large tables.
5. Explain the purpose and trade-offs of vertical partitioning^[5] for wide tables.
6. Use SQL DDL to create a database and define its tables in a way that reflects your physical design.
7. Explain how SQL statements and metadata entries together “build” the implemented database.
8. Describe the entries that appear in the data dictionary^[6] and how they relate to physical structures such as tables, indexes, and constraints.

The remaining lessons in Module 6 dive into each of these topics in more detail, using examples to show how physical design decisions affect real workloads.

The six footnotes on this page define key terms that you will encounter throughout Module 6. Each of them directly supports the theme of physical database design by naming and briefly describing a core physical concept: indexes, clustering, partitioning strategies, and the data dictionary. You will refine and deepen your understanding of these definitions as you work through the module.

[1]Physical design: The phase of database design in which a logical schema is mapped onto specific storage structures, access paths, and configuration options—such as indexes, clustering, and partitioning—to meet performance, scalability, and reliability goals.

[2]Index: An auxiliary data structure (for example, a B-tree or bitmap) that stores ordered key values and pointers to table rows, allowing the RDBMS to locate data more efficiently than by scanning the entire table.

[3] Clustering: A physical design technique that places related rows from one or more tables close together on disk, improving the performance of joins and range queries that frequently access those rows together.

[4] Horizontal partitioning: A strategy that divides a table’s rows into multiple physical partitions (often by range, list, or hash), so that queries can target only the relevant subset of data and maintenance operations can be performed per partition.

[5] Vertical partitioning: A strategy that splits a wide table into multiple tables containing different column groups, typically to reduce I/O for queries that only need a subset of the columns or to isolate infrequently used or sensitive attributes.

[6] Data dictionary (catalog): A set of system tables that describes the database itself—schemas, tables, columns, indexes, constraints, users, and permissions—and records how logical objects are implemented physically so the RDBMS can parse, optimize, and secure queries.