Mapping Logical Models to Physical Tables

In this lesson, you will learn how to turn the abstract shapes of an Entity-Relationship (ER) diagram into the concrete reality of SQL tables.

From Concepts to Code

So far, we have discussed entities like "Customer" and "Order" and the relationships between them. A logical model is just a blueprint; it describes what data exists and how it connects. To actually store this information, we must perform a "mapping" to translate those logical blueprints into physical tables that a database engine can understand.

The Mapping Rules

When you move from a diagram to a physical schema, you apply a few standard transformations:

1. Entities become Tables: Every entity in your diagram (like Customer) becomes a physical table.
2. Attributes become Columns: Every piece of information about that entity (like Name or Email) becomes a column within that table.
3. Primary Keys become Identity: You define the unique identifier from your logical model as the PRIMARY KEY of the table to ensure data integrity.
4. Relationships become Foreign Keys: If a Customer places an Order, the relationship is physically represented by placing the Customer_ID inside the Order table.

A Quick Illustration

Imagine a logical model where an entity User has an ID and a Username. In SQL, the physical mapping looks like this:

CREATE TABLE Users (
    User_ID INT PRIMARY KEY,
    Username VARCHAR(50)
);

If we then add a Post entity that belongs to a User, we map the relationship by adding a reference:

CREATE TABLE Posts (
    Post_ID INT PRIMARY KEY,
    Content TEXT,
    User_ID INT,
    FOREIGN KEY (User_ID) REFERENCES Users(User_ID)
);

By following these rules, we ensure that our physical tables remain a faithful representation of the relationships we defined in our logical model. We have transitioned from simply describing the world to building the structure that holds our data.

Key Takeaway: Mapping logical models to physical tables is the process of converting abstract entities into CREATE TABLE statements, using columns for attributes and foreign keys to enforce the relationships you designed.