Logical vs Physical Modeling

In this lesson, you will learn how to distinguish between the abstract business structure of your data and the technical reality of how it sits on a hard drive.

The Logical Model: "What"

The Logical Model is a blueprint of your business requirements. It ignores the database software (like PostgreSQL or SQL Server) and focuses entirely on the entities and their relationships. At this stage, you aren't worried about performance or storage limits; you are simply defining:

• What are the objects? (e.g., Customer, Order, Product)
• What attributes do they have? (e.g., EmailAddress, UnitPrice)
• How do they relate? (e.g., A Customer places many Orders)

This is the conceptual map that ensures your database serves the business needs correctly before you write a single line of SQL.

The Physical Model: "How"

The Physical Model is the implementation of that blueprint. This is where you translate the logical concepts into concrete storage instructions. Here, you decide exactly how the database will handle the data to ensure performance, security, and integrity.

When you create a physical model, you make decisions based on what you have already learned in this course:

• Data Types: Deciding if a column should be VARCHAR(255) or TEXT.
• Indexing: Choosing where to place indexes based on Index Selectivity to speed up searches.
• Normalization/Denormalization: Deciding whether to split tables for Normalization or join them for performance via Denormalization.
• Constraints: Implementing Entity Integrity (Primary Keys) and Reference Integrity (Foreign Keys).

For example, your Logical Model might say "Every Order belongs to a Customer." Your Physical Model converts that into specific SQL code:

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Email VARCHAR(100) UNIQUE
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

While the logical model is about the "truth" of the data, the physical model is about the "performance and structure" of the database engine. If the logical model is the architectural floor plan of a house, the physical model is the blueprint showing exactly where to run the plumbing, wiring, and steel beams to keep the structure standing and functional.

Key Takeaway: The Logical Model defines the business requirements, while the Physical Model defines the concrete implementation strategies needed to store and access that data efficiently.