Loading learning content...
The transition from conceptual design to logical design represents one of the most critical phases in the database development lifecycle. While conceptual models capture what data exists and how entities relate at a business-semantic level, logical design determines how this information will be structured within a specific data model—most commonly, the relational model.
This transformation is far from mechanical. It requires careful decision-making, trade-off analysis, and deep understanding of both the source model's semantics and the target model's capabilities and constraints. A poorly executed mapping can result in databases that are normalized but unusable, complete but unperformant, or technically correct but semantically distorted.
The stakes are significant:
Logical design errors propagate downstream into physical design, application development, and ultimately into production systems that may serve millions of users. Correcting structural flaws after deployment is orders of magnitude more expensive than addressing them during the design phase.
This page provides a comprehensive treatment of conceptual-to-logical mapping: the systematic algorithms that transform ER diagrams into relational schemas, the decision frameworks that guide mapping choices, the quality criteria that ensure fidelity, and the common pitfalls that even experienced designers encounter.
Before diving into algorithms, we must understand what conceptual-to-logical mapping actually accomplishes. This understanding frames every subsequent decision.
The Semantic Preservation Imperative:
Conceptual models (typically Entity-Relationship diagrams) capture real-world semantics: entities represent distinguishable objects or concepts, attributes describe their properties, and relationships encode how entities interact. The fundamental goal of mapping is to preserve these semantics in a form executable by a database management system.
However, the relational model has a fundamentally different vocabulary:
The challenge lies in the fact that this is not a 1:1 translation. Multiple valid relational representations can encode the same conceptual model, and choosing among them requires understanding the trade-offs involved.
| Conceptual Element (ER) | Logical Element (Relational) | Mapping Complexity |
|---|---|---|
| Strong Entity | Base Table with Primary Key | Low — Direct translation |
| Weak Entity | Table with Composite Key (includes owner PK) | Medium — Dependency must be explicit |
| Simple Attribute | Column with Domain | Low — Direct translation |
| Composite Attribute | Multiple Columns OR Separate Table | Medium — Flattening decision required |
| Multivalued Attribute | Separate Table with Foreign Key | Medium — First Normal Form enforcement |
| Derived Attribute | Computed Column OR Application Logic | Medium — Storage vs. computation trade-off |
| 1:1 Relationship | Foreign Key OR Table Merge | Medium — Multiple valid approaches |
| 1:N Relationship | Foreign Key on N-side | Low — Standard pattern |
| M:N Relationship | Junction/Bridge Table | Medium — Additional table required |
| Supertype/Subtype | Single Table OR Multiple Tables | High — Three distinct strategies |
| N-ary Relationship (n>2) | Junction Table with Multiple FKs | High — Complex key design |
Mapping should preserve all information from the conceptual model, but it often adds information through constraint specifications. Conversely, some semantic nuances (like relationship names describing purpose) may be lost unless captured in documentation or naming conventions. The mapping process should explicitly track both preservation and enhancement.
The canonical approach to ER-to-relational mapping follows a structured, seven-step algorithm. While variations exist, this algorithm forms the foundation of virtually all mapping methodologies and is essential knowledge for any database professional.
Step 1: Map Strong (Regular) Entity Types
For each strong entity type E:
Step 2: Map Weak Entity Types
For each weak entity type W with identifying entity E:
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849
-- ==================================================-- STEP 1: Strong Entity Mapping-- ================================================== -- ER Model:-- Entity: EMPLOYEE-- - Attributes: EmployeeID (PK), FirstName, LastName, -- Address (Composite: Street, City, State, PostalCode),-- Email (Alternate Key) -- Logical Schema:CREATE TABLE Employee ( EmployeeID INT PRIMARY KEY, FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL, -- Composite attribute flattened: Street VARCHAR(100), City VARCHAR(50), State CHAR(2), PostalCode VARCHAR(10), -- Alternate key documented: Email VARCHAR(100) NOT NULL UNIQUE); -- ==================================================-- STEP 2: Weak Entity Mapping -- ================================================== -- ER Model:-- Entity: DEPENDENT (Weak, identified by EMPLOYEE)-- - Partial Key: DependentName-- - Attributes: DateOfBirth, Relationship -- Logical Schema:CREATE TABLE Dependent ( -- Composite Primary Key includes owner's PK EmployeeID INT NOT NULL, DependentName VARCHAR(100) NOT NULL, DateOfBirth DATE, Relationship VARCHAR(20) NOT NULL, PRIMARY KEY (EmployeeID, DependentName), -- Existential dependency: cascade deletion FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID) ON DELETE CASCADE ON UPDATE CASCADE);Step 3: Map Binary 1:1 Relationship Types
For 1:1 relationships, three strategies exist:
Strategy 3A (Foreign Key Approach):
Strategy 3B (Merged Relation Approach):
Strategy 3C (Cross-Reference Table):
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748
-- ==================================================-- STEP 3: 1:1 Relationship Mapping Options-- ================================================== -- ER Model:-- EMPLOYEE (1) --- manages --- (1) DEPARTMENT-- Constraint: Each department has exactly one manager;-- Not every employee manages a department -- Strategy 3A: Foreign Key (Preferred)-- Place FK on the side with total participationCREATE TABLE Department ( DepartmentID INT PRIMARY KEY, DepartmentName VARCHAR(100) NOT NULL, Budget DECIMAL(15,2), -- Foreign key for 1:1 relationship ManagerID INT NOT NULL UNIQUE, ManagerStartDate DATE NOT NULL, -- Relationship attribute FOREIGN KEY (ManagerID) REFERENCES Employee(EmployeeID) ON DELETE RESTRICT -- Cannot delete manager without reassignment); -- Strategy 3B: Merged Relation-- Used when entities ALWAYS exist togetherCREATE TABLE EmployeeWithCredentials ( EmployeeID INT PRIMARY KEY, FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL, Email VARCHAR(100) NOT NULL, -- Credentials merged (1:1 total-total) PasswordHash VARCHAR(256) NOT NULL, LastLoginAt TIMESTAMP, FailedAttempts INT DEFAULT 0); -- Strategy 3C: Cross-Reference (Rarely used for 1:1)-- Useful for optional-optional with relationship attributesCREATE TABLE ParkingAssignment ( EmployeeID INT PRIMARY KEY, -- Also UNIQUE ParkingSpaceID INT UNIQUE, -- Ensures 1:1 AssignedDate DATE NOT NULL, MonthlyFee DECIMAL(8,2), FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID), FOREIGN KEY (ParkingSpaceID) REFERENCES ParkingSpace(SpaceID));Step 4: Map Binary 1:N Relationship Types
For 1:N (one-to-many) relationships S:T where S is the "one" side:
This is the most common and straightforward mapping scenario. The logic is simple: each instance of T is associated with exactly one instance of S, so storing S's identifier in T directly establishes the connection without data duplication.
Step 5: Map Binary M:N Relationship Types
For M:N (many-to-many) relationships between S and T:
This is necessary because neither S nor T can directly hold the other's key—each instance of S relates to multiple Ts, and vice versa.
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667
-- ==================================================-- STEP 4: 1:N Relationship Mapping-- ================================================== -- ER Model:-- DEPARTMENT (1) --- employs --- (N) EMPLOYEE-- Constraint: Every employee works in exactly one department CREATE TABLE Department ( DepartmentID INT PRIMARY KEY, DepartmentName VARCHAR(100) NOT NULL UNIQUE, Location VARCHAR(100)); CREATE TABLE Employee ( EmployeeID INT PRIMARY KEY, FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL, Salary DECIMAL(12,2), HireDate DATE NOT NULL, -- Foreign key captures 1:N relationship DepartmentID INT NOT NULL, -- Total participation FOREIGN KEY (DepartmentID) REFERENCES Department(DepartmentID) ON DELETE RESTRICT -- Cannot delete dept with employees ON UPDATE CASCADE); -- ==================================================-- STEP 5: M:N Relationship Mapping-- ================================================== -- ER Model:-- EMPLOYEE (M) --- works_on --- (N) PROJECT-- Relationship Attributes: Hours, Role CREATE TABLE Project ( ProjectID INT PRIMARY KEY, ProjectName VARCHAR(100) NOT NULL, StartDate DATE, EndDate DATE, Budget DECIMAL(15,2)); -- Junction/Bridge table for M:N relationshipCREATE TABLE EmployeeProject ( EmployeeID INT NOT NULL, ProjectID INT NOT NULL, -- Relationship attributes HoursPerWeek DECIMAL(4,1) DEFAULT 0, Role VARCHAR(50) NOT NULL DEFAULT 'Contributor', AssignedDate DATE NOT NULL DEFAULT CURRENT_DATE, -- Composite primary key PRIMARY KEY (EmployeeID, ProjectID), FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID) ON DELETE CASCADE, -- If employee leaves, remove assignments FOREIGN KEY (ProjectID) REFERENCES Project(ProjectID) ON DELETE CASCADE -- If project ends, remove assignments); -- Index for reverse lookups (find all employees on a project)CREATE INDEX idx_empproject_project ON EmployeeProject(ProjectID);The compound primary key (FK1, FK2) assumes each employee works on each project at most once. If the relationship can have multiple instances (e.g., an employee works on the same project multiple times in different time periods), include a discriminator (like StartDate) in the primary key, or use a surrogate key with a unique constraint on the natural key combination.
Step 6: Map Multivalued Attributes
Multivalued attributes violate First Normal Form (1NF) if included as repeated columns or delimited values. The correct approach is to create a separate relation:
Handling Derived Attributes:
Derived attributes (calculated from other attributes) present a design choice:
Option A: Store explicitly — Denormalize for read performance, accept maintenance overhead Option B: Compute on demand — Use views or computed columns, ensure consistency Option C: Materialized views — Precompute and refresh periodically
The choice depends on read/write ratios, computation complexity, and staleness tolerance.
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889
-- ==================================================-- STEP 6: Multivalued Attribute Mapping-- ================================================== -- ER Model:-- Entity: EMPLOYEE-- - Multivalued Attribute: PhoneNumbers (can have multiple)-- - Multivalued Composite: Skills (SkillName, ProficiencyLevel) -- Wrong Approach (violates 1NF):-- CREATE TABLE EmployeeBad (-- EmployeeID INT PRIMARY KEY,-- PhoneNumbers VARCHAR(500) -- "555-1234,555-5678,555-9999"-- ); -- Correct Approach: Separate tableCREATE TABLE EmployeePhone ( EmployeeID INT NOT NULL, PhoneNumber VARCHAR(20) NOT NULL, PhoneType VARCHAR(20) DEFAULT 'Mobile', IsPrimary BOOLEAN DEFAULT FALSE, PRIMARY KEY (EmployeeID, PhoneNumber), FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID) ON DELETE CASCADE, -- Ensure only one primary phone per employee CONSTRAINT chk_single_primary CHECK (IsPrimary = FALSE OR NOT EXISTS ( SELECT 1 FROM EmployeePhone ep2 WHERE ep2.EmployeeID = EmployeeID AND ep2.IsPrimary = TRUE AND ep2.PhoneNumber != PhoneNumber )) -- Note: Complex constraint; often enforced in application layer); -- Multivalued Composite AttributeCREATE TABLE EmployeeSkill ( EmployeeID INT NOT NULL, SkillName VARCHAR(100) NOT NULL, ProficiencyLevel INT CHECK (ProficiencyLevel BETWEEN 1 AND 5), CertifiedDate DATE, PRIMARY KEY (EmployeeID, SkillName), FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID) ON DELETE CASCADE); -- ==================================================-- DERIVED ATTRIBUTE OPTIONS-- ================================================== -- ER Model:-- Entity: EMPLOYEE-- - Derived Attribute: Age (calculated from DateOfBirth)-- - Derived Attribute: TotalProjectHours (sum from EmployeeProject) -- Option A: Stored/Denormalized (Update triggers or application logic needed)ALTER TABLE Employee ADD COLUMN Age INT;-- Must be updated by trigger or scheduled job -- Option B: Computed Column (Database-supported)-- PostgreSQL:ALTER TABLE Employee ADD COLUMN Age INT GENERATED ALWAYS AS ( EXTRACT(YEAR FROM AGE(CURRENT_DATE, DateOfBirth)) ) STORED; -- SQL Server:-- Age AS DATEDIFF(YEAR, DateOfBirth, GETDATE()) -- Option C: View for Derived ValuesCREATE VIEW EmployeeWithCalculations ASSELECT e.*, EXTRACT(YEAR FROM AGE(CURRENT_DATE, e.DateOfBirth)) AS Age, COALESCE( (SELECT SUM(HoursPerWeek) FROM EmployeeProject ep WHERE ep.EmployeeID = e.EmployeeID ), 0 ) AS TotalWeeklyProjectHoursFROM Employee e;If a multivalued attribute has known maximum cardinality (e.g., at most 3 phone numbers), you might choose to denormalize with columns Phone1, Phone2, Phone3. This trades flexibility for query simplicity but is generally discouraged unless the limit is truly fixed and queries predominantly need all values together.
Step 7: Map N-ary Relationship Types (n > 2)
For relationships involving three or more entity types:
Determining the primary key for n-ary relationships requires careful analysis. Generally, include the primary keys of all entities that can participate multiple times for the same combination of the others.
Recursive (Unary) Relationships:
When an entity relates to itself (e.g., Employee supervises Employee):
For 1:N recursive:
For M:N recursive:
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394
-- ==================================================-- STEP 7A: Ternary Relationship Mapping-- ================================================== -- ER Model:-- SUPPLIER --- supplies --- PART --- to --- PROJECT-- Ternary Relationship: SUPPLY (Supplier, Part, Project)-- Relationship Attribute: Quantity -- The relationship means: A supplier supplies a particular part-- to a particular project in a specific quantity CREATE TABLE Supplier ( SupplierID INT PRIMARY KEY, SupplierName VARCHAR(100) NOT NULL, ContactEmail VARCHAR(100)); CREATE TABLE Part ( PartID INT PRIMARY KEY, PartName VARCHAR(100) NOT NULL, UnitPrice DECIMAL(10,2)); CREATE TABLE Project ( ProjectID INT PRIMARY KEY, ProjectName VARCHAR(100) NOT NULL); -- Junction table for ternary relationshipCREATE TABLE Supply ( SupplierID INT NOT NULL, PartID INT NOT NULL, ProjectID INT NOT NULL, Quantity INT NOT NULL CHECK (Quantity > 0), SupplyDate DATE DEFAULT CURRENT_DATE, -- Primary key includes all three FKs -- (assumes same supplier can supply same part to same project once) PRIMARY KEY (SupplierID, PartID, ProjectID), FOREIGN KEY (SupplierID) REFERENCES Supplier(SupplierID), FOREIGN KEY (PartID) REFERENCES Part(PartID), FOREIGN KEY (ProjectID) REFERENCES Project(ProjectID)); -- If multiple supplies allowed (e.g., different dates):-- PRIMARY KEY (SupplierID, PartID, ProjectID, SupplyDate)-- OR use surrogate key with unique constraint -- ==================================================-- STEP 7B: Recursive (Unary) Relationship Mapping-- ================================================== -- ER Model:-- EMPLOYEE (1) --- supervises --- (N) EMPLOYEE -- 1:N Recursive: Self-referencing foreign keyCREATE TABLE Employee ( EmployeeID INT PRIMARY KEY, FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL, -- Recursive relationship: supervisor SupervisorID INT, -- NULL for top-level employees FOREIGN KEY (SupervisorID) REFERENCES Employee(EmployeeID) ON DELETE SET NULL -- If supervisor leaves, set to NULL); -- Index for efficient hierarchy queriesCREATE INDEX idx_employee_supervisor ON Employee(SupervisorID); -- ER Model:-- PART (M) --- component_of --- (N) PART-- Relationship Attribute: Quantity -- M:N Recursive: Junction tableCREATE TABLE PartComposition ( ParentPartID INT NOT NULL, ChildPartID INT NOT NULL, Quantity INT NOT NULL CHECK (Quantity > 0), PRIMARY KEY (ParentPartID, ChildPartID), -- Prevent part being component of itself CHECK (ParentPartID != ChildPartID), FOREIGN KEY (ParentPartID) REFERENCES Part(PartID), FOREIGN KEY (ChildPartID) REFERENCES Part(PartID)); -- Note: Preventing circular references requires application logic-- or recursive constraints (database-specific)For n-ary relationships, identifying the correct primary key is crucial. Analyze the cardinality constraints carefully: if an entity on the '1' side determines the others, it may not need to be in the primary key. Document your reasoning, as incorrect key selection leads to either lost relationships or unintended duplicates.
Supertype/subtype (generalization/specialization or IS-A) hierarchies present the most complex mapping scenario. Three primary strategies exist, each with distinct trade-offs:
Strategy A: Single Table (Table-Per-Hierarchy)
Create one table containing all attributes from the supertype and all subtypes:
Strategy B: Multiple Tables (Table-Per-Type)
Create a table for the supertype and one for each subtype:
Strategy C: Subtype Tables Only (Table-Per-Concrete-Class)
Create tables only for subtypes, duplicating supertype attributes:
| Criteria | Single Table | Multiple Tables | Subtype Tables Only |
|---|---|---|---|
| Storage Efficiency | Low (many NULLs) | High (no NULLs) | Medium (duplication) |
| Query Simplicity (Subtype) | Medium | Medium (join needed) | High (single table) |
| Query Simplicity (Supertype) | High | High (single table) | Low (UNION required) |
| Constraint Enforcement | Weak | Strong | Strong |
| Schema Evolution | Easy | Moderate | Difficult |
| Best For | Few subtypes, few specific attrs | Many specific attrs, strong typing | Rarely queried at supertype level |
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116
-- ==================================================-- GENERALIZATION/SPECIALIZATION MAPPING-- ================================================== -- ER Model:-- VEHICLE (supertype)-- |-- CAR (subtype): NumberOfDoors, TrunkCapacity-- |-- TRUCK (subtype): CargoCapacity, NumberOfAxles-- |-- MOTORCYCLE (subtype): EngineDisplacement, HasSidecar -- Common Vehicle attributes: VehicleID, Make, Model, Year, LicensePlate -- ============================================-- STRATEGY A: Single Table (Table-Per-Hierarchy)-- ============================================CREATE TABLE Vehicle_SingleTable ( VehicleID INT PRIMARY KEY, Make VARCHAR(50) NOT NULL, Model VARCHAR(50) NOT NULL, Year INT NOT NULL, LicensePlate VARCHAR(20) UNIQUE, -- Discriminator VehicleType VARCHAR(20) NOT NULL CHECK (VehicleType IN ('CAR', 'TRUCK', 'MOTORCYCLE')), -- Car-specific (nullable) NumberOfDoors INT, TrunkCapacity DECIMAL(5,2), -- Truck-specific (nullable) CargoCapacity DECIMAL(10,2), NumberOfAxles INT, -- Motorcycle-specific (nullable) EngineDisplacement INT, HasSidecar BOOLEAN); -- Partial constraint enforcement via CHECKALTER TABLE Vehicle_SingleTable ADD CONSTRAINT chk_vehicle_attrs CHECK ( (VehicleType = 'CAR' AND NumberOfDoors IS NOT NULL) OR (VehicleType = 'TRUCK' AND CargoCapacity IS NOT NULL) OR (VehicleType = 'MOTORCYCLE' AND EngineDisplacement IS NOT NULL)); -- ============================================-- STRATEGY B: Multiple Tables (Table-Per-Type)-- ============================================CREATE TABLE Vehicle ( VehicleID INT PRIMARY KEY, Make VARCHAR(50) NOT NULL, Model VARCHAR(50) NOT NULL, Year INT NOT NULL, LicensePlate VARCHAR(20) UNIQUE); CREATE TABLE Car ( VehicleID INT PRIMARY KEY, NumberOfDoors INT NOT NULL CHECK (NumberOfDoors BETWEEN 2 AND 5), TrunkCapacity DECIMAL(5,2) NOT NULL, FOREIGN KEY (VehicleID) REFERENCES Vehicle(VehicleID) ON DELETE CASCADE); CREATE TABLE Truck ( VehicleID INT PRIMARY KEY, CargoCapacity DECIMAL(10,2) NOT NULL, NumberOfAxles INT NOT NULL CHECK (NumberOfAxles >= 2), FOREIGN KEY (VehicleID) REFERENCES Vehicle(VehicleID) ON DELETE CASCADE); CREATE TABLE Motorcycle ( VehicleID INT PRIMARY KEY, EngineDisplacement INT NOT NULL, HasSidecar BOOLEAN NOT NULL DEFAULT FALSE, FOREIGN KEY (VehicleID) REFERENCES Vehicle(VehicleID) ON DELETE CASCADE); -- View to reconstruct complete Car entityCREATE VIEW CarComplete ASSELECT v.*, c.NumberOfDoors, c.TrunkCapacityFROM Vehicle vJOIN Car c ON v.VehicleID = c.VehicleID; -- ============================================-- STRATEGY C: Subtype Tables Only-- ============================================CREATE TABLE Car_Standalone ( VehicleID INT PRIMARY KEY, Make VARCHAR(50) NOT NULL, Model VARCHAR(50) NOT NULL, Year INT NOT NULL, LicensePlate VARCHAR(20) UNIQUE, NumberOfDoors INT NOT NULL, TrunkCapacity DECIMAL(5,2) NOT NULL); CREATE TABLE Truck_Standalone ( VehicleID INT PRIMARY KEY, Make VARCHAR(50) NOT NULL, Model VARCHAR(50) NOT NULL, Year INT NOT NULL, LicensePlate VARCHAR(20) UNIQUE, CargoCapacity DECIMAL(10,2) NOT NULL, NumberOfAxles INT NOT NULL); -- View to query all vehicles (supertype perspective)CREATE VIEW AllVehicles ASSELECT VehicleID, Make, Model, Year, LicensePlate, 'CAR' AS VehicleTypeFROM Car_StandaloneUNION ALLSELECT VehicleID, Make, Model, Year, LicensePlate, 'TRUCK' AS VehicleTypeFROM Truck_Standalone;A rigorous mapping process includes systematic validation to ensure the logical schema correctly and completely represents the conceptual model.
Semantic Completeness Check:
Verify that every element of the conceptual model has a corresponding representation:
Referential Integrity Verification:
Ensure all foreign key relationships are properly defined with appropriate actions:
Naming Convention Compliance:
Consistent naming aids maintenance and understanding:
Always document mapping decisions, especially where multiple valid approaches exist. Record why Strategy A was chosen over Strategy B for a particular generalization. This documentation is invaluable during maintenance, audits, and when onboarding new team members.
Conceptual-to-logical mapping is both a systematic process and a design discipline. Let's consolidate the essential knowledge:
What Comes Next:
With mapping fundamentals established, the next logical step is understanding relational schema notation and representation. We'll examine how to formally document logical schemas, the conventions used in industry and academia, and how schema diagrams communicate structure to stakeholders.
Subsequent pages will dive deep into normalization (ensuring minimal redundancy), constraint specification (encoding business rules), and the iterative refinement process that transforms initial schemas into production-ready designs.
You now understand the systematic process of transforming conceptual ER models into logical relational schemas. The seven-step algorithm, combined with strategies for complex constructs like generalizations and n-ary relationships, equips you to handle real-world database design challenges with confidence.