Logical Data Modeling Concepts

Before a single line of SQL is written, a database project benefits enormously from a deliberate design phase in which the real-world business domain is studied, understood, and expressed as a structured diagram or document. That artifact is the logical data model. It captures the things a business cares about, the facts known about each of those things, and the relationships that connect them — all without committing to any particular database engine, storage format, or programming language. The logical model is where business thinking and technical thinking meet on neutral ground, and mastering it is what separates a database professional who builds brittle, hard-to-maintain schemas from one who builds systems that age gracefully.

This topic explores the full scope of logical data modeling: why it exists, what its core building blocks are, how relationships and business rules are captured, and how the finished model becomes the authoritative blueprint from which SQL is derived. It also introduces normalization as the disciplined refinement process that keeps a logical model clean and dependable.

Purpose of Logical Data Models

A logical data model answers a single central question: what information does this business need to store, and how does that information relate to itself? The answer is expressed independently of any technology concern. There is no mention of varchar lengths, index types, storage engines, or partitioning strategies. Those details belong to the physical model that comes later. By deferring them, the logical model stays focused on business truth.

This technology-neutrality is not merely academic. When a team sketches out entities and relationships before touching SQL, three important things happen. First, business rules are surfaced early. A conversation about whether one Customer can place many Orders, or whether every Order must have at least one line item, happens at a stage when changing your mind costs nothing. Second, the model becomes a communication bridge. A business analyst who has never written SQL can read an entity-relationship diagram and confirm — or correct — what the developers have understood. Misunderstandings caught at this stage cost hours, not weeks. Third, the logical model provides a stable reference point. When the physical database is built and later modified, the team can check proposed changes against the logical model to ensure they do not silently violate a business rule that was agreed upon months earlier.

Consider a retail company beginning a new order-management system. If developers jump straight into SQL, each person might make slightly different assumptions: one creates a customer address column directly on the orders table, another creates a separate addresses table but with a different column layout. The logical model, agreed upon upfront, prevents this fragmentation. Everyone is building from the same blueprint.

Entities and Attributes

The fundamental building block of a logical model is the entity. An entity represents a distinct, identifiable thing — a noun — that the business needs to remember information about. Good candidates for entities are things that exist across time, that have multiple instances, and that are genuinely distinct from one another. In a retail domain, Product, Customer, Order, and Supplier are natural entities. In a healthcare domain, Patient, Physician, Appointment, and Diagnosis are typical examples.

Entities should not be confused with events or transactions, although those can be entities too. An Order is an event — something that happens at a point in time — yet it qualifies as an entity because the business needs to store a persistent record of it, relate it to products and customers, and query it repeatedly. The test is whether the business needs to track many instances of the thing over time. If yes, it is an entity.

Attributes are the data points associated with an entity. They answer the question: what do we know about this entity? For a Customer entity, attributes might include first name, last name, email address, phone number, and date of account creation. Each attribute eventually becomes a column definition when the logical model is translated into a SQL table.

Choosing attribute granularity carefully is essential. If a customer's full name is stored as a single attribute — full_name — it becomes very difficult to sort by last name, to address the customer by first name in an email, or to handle cultural naming conventions that differ from First Middle Last. Splitting it into first_name and last_name, or even given_name, family_name, and name_suffix, gives the application the flexibility it needs. Conversely, over-granularity adds complexity without benefit: storing each digit of a phone number as a separate attribute would be absurd. The right granularity is the smallest unit of data that the business needs to retrieve, filter, sort, or compute on independently.

An important special attribute is the identifier. Every entity must have at least one attribute, or combination of attributes, that uniquely identifies each instance. In modeling terminology this is called a key. Sometimes the business already provides a natural key — a Social Security Number for a person, an ISBN for a book — but more often a surrogate key (an arbitrary unique number generated by the system) is safer because natural identifiers can change, be reused, or turn out to be non-unique in edge cases. At the logical level the key is annotated on the entity; at the SQL level it becomes a PRIMARY KEY constraint.

Relationships Between Entities

Entities rarely exist in isolation. A customer places orders; an order contains products; a product is supplied by a supplier. These connections are relationships, and capturing them is one of the most important tasks in logical modeling. A relationship that is missed or misrepresented at this stage will almost certainly cause data integrity problems — or awkward workarounds — after the database is deployed.

Relationships are characterized by two properties: cardinality and optionality.

Cardinality describes how many instances of one entity can be associated with how many instances of another. The three canonical cardinalities are:

• One-to-One (1:1): Each instance of Entity A is associated with at most one instance of Entity B, and vice versa. A real example is the relationship between a country and its capital city — each country has exactly one capital, and each capital belongs to exactly one country. In SQL, a 1:1 relationship is usually implemented by placing a foreign key in one of the two tables, often accompanied by a UNIQUE constraint to enforce the "at most one" side.
• One-to-Many (1:N): One instance of Entity A can be associated with many instances of Entity B, but each instance of Entity B is associated with only one instance of Entity A. This is by far the most common cardinality. A single Customer can place many Orders, but each Order belongs to exactly one Customer. In SQL, this is implemented by placing a foreign key in the "many" table that references the primary key of the "one" table.
• Many-to-Many (M:N): An instance of Entity A can be associated with many instances of Entity B, and an instance of Entity B can also be associated with many instances of Entity A. A student can enroll in many courses, and a course can have many students. In SQL, a many-to-many relationship cannot be represented by a simple foreign key. It requires a separate junction table (also called an associative entity or bridge table) that holds foreign keys from both sides. The junction table's primary key is usually the combination of both foreign keys.

Optionality (also called participation or modality) specifies whether an entity's participation in a relationship is mandatory or optional. For example, every Order must belong to a Customer — that participation is mandatory. But a Customer does not need to have placed any orders yet — that participation is optional. This distinction later determines whether a foreign key column is defined as NOT NULL (mandatory) or nullable (optional).

To make these ideas concrete, consider a small library system with three entities:

• Member — library cardholders
• Book — physical volumes owned by the library
• Loan — a record of a member borrowing a book on a specific date

The relationship between Member and Loan is one-to-many: one member can have many loans over time, but each loan record belongs to exactly one member. The relationship between Book and Loan is also one-to-many: the same book can be loaned out many times (sequentially), but each loan record concerns exactly one book. Notice that the Loan entity serves as a junction between Member and Book: it resolves what would otherwise be a many-to-many relationship between members and books into two one-to-many relationships. It also carries its own attributes — loan date, due date, and return date — that belong to the act of borrowing, not to the member or book alone.

Business Rules in Logical Models

A business rule is any constraint, policy, or expectation that governs how data can exist or change. Business rules exist whether or not they are written down, but only explicitly-documented rules can be reliably enforced. The logical model is the ideal place to capture them.

Business rules fall into several categories:

• Uniqueness rules: "No two employees can share the same employee badge number." This translates to a UNIQUE constraint on the badge_number column.
• Mandatory field rules: "Every product must have a list price." This translates to a NOT NULL constraint on the list_price column.
• Domain rules: "A product's status can only be Active, Discontinued, or Pending." This translates to a CHECK constraint or a foreign key reference to a lookup table of valid statuses.
• Referential integrity rules: "An order line item must reference a valid product." This translates to a FOREIGN KEY constraint.
• Derivation rules: "An order's total amount is the sum of its line item amounts." This might be expressed as a computed column or a view, or left to application logic, but the logical model notes that the value is derived, not independently stored.
• Temporal rules: "A loan's return date must be on or after its loan date." This translates to a CHECK constraint comparing two date columns.

When business rules are embedded in the logical model, they are visible to everyone involved in the project. A rule left unrecorded is a rule that will be forgotten. And a forgotten rule will either be missing from the SQL schema — allowing bad data to enter — or will be inconsistently coded in application logic, where different screens or API endpoints may enforce it differently. Database-level constraints are far more reliable because they apply regardless of how the data gets there.

For example, suppose a business rule states: "A part-time employee cannot be assigned to more than two departments simultaneously." At the logical level, this rule is annotated on the relationship between Employee and Department. When the developer later translates this to SQL, they know to implement a trigger or a CHECK constraint that evaluates the count of active department assignments for part-time employees before allowing an insert.

Logical Model as a Blueprint for SQL

The movement from logical model to SQL schema is essentially a translation process. Each element of the logical model has a direct correspondent in SQL:

A concrete example shows how this translation works. Suppose the logical model contains two entities, Customer and Order, with a one-to-many relationship (one customer places many orders, each order belongs to one customer), and the business rules state that every order must have a customer, and every order must have an order date. The SQL translation is straightforward:

CREATE TABLE customer (
    customer_id   INT          NOT NULL,
    first_name    VARCHAR(50)  NOT NULL,
    last_name     VARCHAR(50)  NOT NULL,
    email         VARCHAR(100) NOT NULL,
    CONSTRAINT pk_customer PRIMARY KEY (customer_id),
    CONSTRAINT uq_customer_email UNIQUE (email)
);

CREATE TABLE order (
    order_id      INT      NOT NULL,
    customer_id   INT      NOT NULL,
    order_date    DATE     NOT NULL,
    CONSTRAINT pk_order PRIMARY KEY (order_id),
    CONSTRAINT fk_order_customer
        FOREIGN KEY (customer_id)
        REFERENCES customer (customer_id)
);

Every decision in this SQL — the NOT NULL on customer_id in the order table, the FOREIGN KEY reference, the UNIQUE on email — traces directly back to a deliberate choice made in the logical model. When the model and the schema are aligned, the implementation is predictable. When they diverge — because a developer made a hasty judgment call, or a requirement was communicated only verbally and not recorded — data integrity problems emerge: orphaned records, duplicate entries, missing required values. These problems are expensive to fix retroactively because data has already accumulated and correcting the schema may require migrating or cleaning existing records.

Normalization in Logical Modeling

Normalization is the process of refining a logical model (and subsequently the SQL schema) to eliminate redundancy and ensure that each fact is stored in exactly one place. It was formalized by Edgar F. Codd in the 1970s and remains one of the most durable ideas in database design. Normalization is expressed as a series of normal forms, each building on the previous by addressing a specific type of data anomaly.

To understand why normalization matters, consider what goes wrong without it. Suppose a single table stores order information like this:

This structure has several problems. If Alice's email address changes, every row belonging to her must be updated — and if even one row is missed, the database now contains two different email addresses for the same customer (an update anomaly). If the only order containing USB Hub is deleted, the knowledge that the USB Hub costs $19.99 is lost entirely (a deletion anomaly). And inserting a new product that has not yet been ordered requires inserting a row with no valid order ID (an insertion anomaly).

Normalization resolves these anomalies. The three normal forms most commonly targeted in logical modeling are:

• First Normal Form (1NF): Every attribute must contain only atomic (indivisible) values, and every row must be uniquely identifiable. A column that stores a comma-separated list of phone numbers violates 1NF because it holds multiple values in one cell. The fix is to move the repeating data to a separate table. Additionally, there should be no repeating groups of columns (like phone1, phone2, phone3).
• Second Normal Form (2NF): The table must already be in 1NF, and every non-key attribute must depend on the entire primary key, not just part of it. This issue arises only when the primary key is composite (made up of multiple columns). In an order-line-item table with a composite key of (order_id, product_id), if product_name depends only on product_id and not on order_id, then product_name has a partial dependency and belongs in a separate Product table. Moving it there achieves 2NF.
• Third Normal Form (3NF): The table must already be in 2NF, and no non-key attribute should depend on another non-key attribute (no transitive dependency). If an employee table stores department_id and department_name, and department_name is determined by department_id rather than by employee_id, then department_name has a transitive dependency through department_id. The solution is to create a Department table and remove department_name from the employee table, leaving only the foreign key department_id.

To see normalization in action, the flat order table shown above can be decomposed step by step. After reaching 3NF, the data lives in four clean tables: Customer (customer facts), Product (product facts), Order (order-level facts referencing a customer), and OrderLine (line-item facts referencing both an order and a product). Each fact exists in exactly one place. Updating Alice's email requires changing exactly one row in the Customer table. Deleting an order does not destroy product information. Adding a new product requires only a row in the Product table.

A normalized logical model produces a SQL schema that is easier to maintain (changes are localized), more trustworthy (no contradictory copies of the same fact), and more queryable (data is organized in the way SQL joins are designed to work). While there are legitimate cases where controlled denormalization is introduced at the physical level for performance reasons, the logical model should reflect the fully normalized design — the denormalization, if ever applied, is a conscious, documented trade-off made later, not an accident baked into the foundation.

Taken together, the elements explored in this topic — purposeful modeling, well-chosen entities and attributes, explicit relationships, documented business rules, disciplined normalization, and faithful SQL translation — form the professional practice of logical data modeling. This practice is what transforms a vague requirement into a coherent, enforceable database design that serves the business reliably over time.