Relationship Mapping 1 1 - Learning Module

Loading content...

0/252

Merged Table Approach

When Two Become One

The merged table approach represents a fundamentally different philosophy from foreign key mapping. Rather than preserving the conceptual separation of two entity types, this strategy combines both entities into a single relational table. The relationship itself vanishes from the schema—it becomes implicit in the unified structure.

At first glance, this might seem like a violation of faithful ER-to-relational translation. After all, we modeled two entities and a relationship; shouldn't the relational schema reflect this? The answer requires understanding that the relational model serves different purposes than the ER model. While ER diagrams capture business semantics and stakeholder understanding, relational schemas optimize for data integrity, query efficiency, and storage.

When a 1:1 relationship has total participation on both sides (every instance of each entity participates), the conceptual distinction between the two entities becomes questionable. If Entity A always has exactly one Entity B, and Entity B always has exactly one Entity A, perhaps they are—at the implementation level—a single entity with rich attributes.

What You Will Learn

By the end of this page, you will understand when the merged table approach is appropriate, how to correctly combine entity attributes, the trade-offs between merged and separate tables, and the scenarios where merging produces superior designs.

The Rationale for Merging

The merged table approach is justified by a simple observation: when two entities always coexist in a 1:1 relationship, they functionally behave as one entity from a data perspective.

Consider EMPLOYEE and EMPLOYEE_MEDICAL_RECORD with total-total participation:

Every employee has exactly one medical record
Every medical record belongs to exactly one employee
They are created together, updated in relation to each other, and deleted together
Queries almost always need data from both

In this scenario, maintaining separate tables creates overhead:

Every data retrieval requires a JOIN
Foreign key and index storage is duplicated
Transaction logic must consider two tables
Application code maps to two objects that are always loaded together

Merging eliminates this overhead while sacrificing nothing—if the entities truly always coexist.

Conceptual vs. Implementation Model

The ER model is a conceptual tool for understanding and communicating domain structure. The relational schema is an implementation artifact optimized for the DBMS. Discrepancies between them don't indicate errors—they indicate appropriate abstraction at different design phases.

When Merging Makes Sense:

Total-total participation: Both entities must always participate in the relationship
Lifecycle coupling: Both entities are created, modified, and deleted together
Query coupling: Data from both entities is almost always retrieved together
No independent access requirement: Neither entity is accessed without the other
No distinct security requirements: Both entity types have the same access controls

Merging Mechanics

The process of merging two 1:1-related entities into a single table follows systematic steps:

Step 1: Identify the Primary Entity

Determine which entity is conceptually "primary" or "dominant." This entity's name typically becomes the table name, and its primary key becomes the merged table's primary key. Selection criteria include:

Which entity exists first in the business lifecycle?
Which entity is referenced by other relationships?
Which entity has more meaningful independent identity?

Step 2: Combine Attributes

Union all attributes from both entities. Handle naming conflicts by prefixing or renaming attributes from the secondary entity.

Step 3: Preserve Key Constraints

The primary entity's key becomes the table's primary key. If the secondary entity had a natural key (like passport_number), it becomes a UNIQUE constraint.

Step 4: Transfer Relationships

Any relationships the secondary entity had with other entities must now reference the merged table.

Merged Table Example
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
-- Before: Two entities with 1:1 (Total-Total)
-- Entity 1: COUNTRY
-- Entity 2: CAPITAL_CITY (every country has one; every capital is of one country)
 
-- Separate tables approach (for comparison):
CREATE TABLE country (
    country_id      INT PRIMARY KEY,
    country_name    VARCHAR(100) NOT NULL,
    population      BIGINT,
    area_km2        DECIMAL(12, 2)
);
 
CREATE TABLE capital_city (
    city_id         INT PRIMARY KEY,
    country_id      INT UNIQUE NOT NULL,
    city_name       VARCHAR(100) NOT NULL,
    city_population INT,
    founded_year    INT,
    FOREIGN KEY (country_id) REFERENCES country(country_id)
);
 
-- -----------------------------------------------
-- Merged table approach:
DROP TABLE IF EXISTS capital_city;
DROP TABLE IF EXISTS country;
 
CREATE TABLE country (
    country_id          INT PRIMARY KEY,
    country_name        VARCHAR(100) NOT NULL,
    population          BIGINT,
    area_km2            DECIMAL(12, 2),
    -- Merged capital city attributes:
    capital_name        VARCHAR(100) NOT NULL,
    capital_population  INT,
    capital_founded     INT
);
 
-- Benefits:
-- No JOIN needed to get country + capital
-- No foreign key storage overhead
-- Single table scan for full data
-- Simpler transaction logic

Attribute Naming Convention

When merging, prefix the secondary entity's attributes to maintain clarity: capital_name, capital_population, etc. This documents the source entity and prevents confusion when reading the schema. Some teams use comments to delineate merged sections.

Handling Constraints in Merged Tables

Merging affects how constraints are expressed and enforced. Understanding constraint transformation ensures the merged schema preserves all original semantic guarantees.

Constraint Transformation in Merged Tables
Original Constraint	Separate Tables	Merged Table	Notes
Primary Key (E₁)	PK on E₁ table	PK on merged table	Natural transformation
Primary Key (E₂)	PK on E₂ table	UNIQUE constraint or eliminated	If E₂ key is natural (passport no.), preserve as UNIQUE
Foreign Key (1:1)	FK in E₂ referencing E₁	Eliminated	Relationship now implicit
NOT NULL (E₁)	Column constraint	Column constraint	Directly transferred
NOT NULL (E₂)	Column constraint	Column constraint (if total-total)	May become nullable if E₂ had partial participation
UNIQUE (E₂)	Column constraint	Column constraint	Preserved for natural keys
CHECK (E₂)	Table constraint	Table constraint	Logic may reference new column names

Constraint Handling Example
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
-- Original: PERSON ↔ PASSPORT (1:1, partial-partial - NOT ideal for merge)
-- Let's use: USER ↔ USER_SETTINGS (1:1, total-total - good for merge)
 
-- Separate tables:
CREATE TABLE app_user (
    user_id         INT PRIMARY KEY,
    username        VARCHAR(50) UNIQUE NOT NULL,
    email           VARCHAR(100) UNIQUE NOT NULL,
    created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
 
CREATE TABLE user_settings (
    setting_id      INT PRIMARY KEY,
    user_id         INT UNIQUE NOT NULL,
    theme           VARCHAR(20) DEFAULT 'light' CHECK (theme IN ('light', 'dark', 'auto')),
    language        CHAR(2) DEFAULT 'en',
    notifications   BOOLEAN DEFAULT true,
    
    FOREIGN KEY (user_id) REFERENCES app_user(user_id) ON DELETE CASCADE
);
 
-- -----------------------------------------------
-- Merged table:
CREATE TABLE app_user (
    user_id             INT PRIMARY KEY,
    username            VARCHAR(50) UNIQUE NOT NULL,
    email               VARCHAR(100) UNIQUE NOT NULL,
    created_at          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    -- Merged settings:
    settings_theme      VARCHAR(20) DEFAULT 'light' 
                        CHECK (settings_theme IN ('light', 'dark', 'auto')),
    settings_language   CHAR(2) DEFAULT 'en',
    settings_notify     BOOLEAN DEFAULT true
);
 
-- Observations:
-- 1. user_id remains the sole primary key
-- 2. setting_id is eliminated (no longer needed)
-- 3. CHECK constraint preserved with updated column name
-- 4. DEFAULT values preserved
-- 5. Foreign key constraint eliminated (implicit now)

The NULL Problem with Partial Participation

The merged table approach works elegantly for total-total participation because every row has values for all attributes. But what happens when participation is partial?

If Entity E₂ has partial participation (some E₁ instances don't have an associated E₂), then merging would require:

Making all E₂ attributes NULLable
Storing NULL for E₂ attributes whenever E₁ lacks an E₂ association

This creates NULL proliferation—many columns with many NULL values—which violates normalization principles and indicates poor design.

NULL Proliferation ExampleWhy merging is wrong for partial participation

Input

EMPLOYEE ↔ COMPANY_VEHICLE

- 10,000 employees
- Only 500 have company vehicles
- Merged table approach

Output

If merged:
- 10,000 rows in employee table
- 9,500 rows have NULL for all vehicle columns (95%)
- vehicle_make, vehicle_model, vehicle_plate, vehicle_year all NULL
- Wasted storage for 9,500 × (size of vehicle columns)
- Queries must filter IS NOT NULL constantly
- Violates 3NF: NULLs indicate perhaps separate relation needed

The Merge-Only-When-Total Rule

Only merge when both entities have total participation. Partial participation means some instances exist without the related entity—these become NULL-laden rows in a merged table. Keep entities separate when partial participation exists on either side.

Quantifying the NULL Problem:

Consider a table with 1 million rows where only 1% have values for the "merged" columns:

Storage waste: 990,000 rows × size of NULL columns = significant disk usage
Index bloat: If NULLable columns are indexed, the index stores mostly NULL entries
Query complexity: WHERE clauses need IS NOT NULL checks
Business logic: NULL handling complicates application code
Reporting: Aggregations and analytics must handle NULL cases

The intersection of entities (rows with values in all columns) is small relative to the union. This geometric mismatch makes merging inappropriate.

Implementation Considerations

Successfully implementing the merged table approach requires attention to several practical considerations:

Key Implementation Considerations

•Column ordering: Group related attributes together. Place original E₁ columns first, followed by E₂ columns with clear prefixes. This aids schema readability.
•Documentation: Since the merge obscures the original conceptual model, document the decision. Record that columns X-Y come from the conceptual entity E₂.
•View creation: Create views that present the data as if it were in separate tables. This supports legacy code expecting the original structure.
•ORM mapping: Object-Relational Mappers may expect separate entities. Configure the ORM to map multiple classes to a single table, or use embedded objects.
•Schema evolution: If the 1:1 relationship might become 1:N in the future, avoid merging. Separation now prevents complex refactoring later.
•Row size limits: Merged tables have wider rows. Ensure the total row size stays within DBMS limits (typically 8KB to 64KB per row).

Views for Conceptual Separation
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- Merged table
CREATE TABLE employee (
    employee_id         INT PRIMARY KEY,
    first_name          VARCHAR(50) NOT NULL,
    last_name           VARCHAR(50) NOT NULL,
    hire_date           DATE NOT NULL,
    department          VARCHAR(50),
    -- Merged from PERSONNEL_FILE:
    pf_salary           DECIMAL(12, 2),
    pf_performance      VARCHAR(20),
    pf_bonus_eligible   BOOLEAN DEFAULT false
);
 
-- View presenting Employee-only columns
CREATE VIEW v_employee AS
SELECT employee_id, first_name, last_name, hire_date, department
FROM employee;
 
-- View presenting PersonnelFile-like columns
CREATE VIEW v_personnel_file AS
SELECT employee_id,
       pf_salary AS salary,
       pf_performance AS performance,
       pf_bonus_eligible AS bonus_eligible
FROM employee;
 
-- Application code can query views as if tables were separate:
SELECT * FROM v_employee WHERE department = 'Engineering';
SELECT * FROM v_personnel_file WHERE salary > 100000;
 
-- Join is trivial (same underlying table):
SELECT e.first_name, pf.salary
FROM v_employee e
JOIN v_personnel_file pf ON e.employee_id = pf.employee_id;
-- (The join is optimized away since it's the same table)

ORM Considerations

Modern ORMs like JPA/Hibernate, Entity Framework, and Prisma support embedded objects or value types that map multiple conceptual objects to a single table. Use these features to maintain clean object models despite table merging.

Comparison: Merged vs. Foreign Key Approach

When should you merge, and when should you use the foreign key approach? The decision depends on multiple factors that we can systematically evaluate:

Decision Matrix: Merged vs. Foreign Key
Factor	Favors Merged	Favors Foreign Key
Participation	Total-Total on both sides	Partial on either or both sides
Query patterns	Almost always queried together	Often queried independently
Lifecycle	Created/deleted simultaneously	Independent lifecycles
Security	Same access requirements	Different access policies
Row width	Small combined row size	Large combined row size
Future cardinality	Will remain 1:1 forever	May evolve to 1:N
Conceptual clarity	Entities are tightly coupled	Entities are distinct concepts
Transaction scope	Both modified in same transaction	May be modified separately

Merge When...

•Both entities have total participation
•The application always needs both datasets
•Entities are conceptually "parts" of a whole
•No independent access control is needed
•The relationship will never become 1:N
•Schema simplicity is valued

Keep Separate When...

•Either entity has partial participation
•Entities are frequently accessed independently
•Different security policies apply
•One entity might gain multiple related instances later
•Row size would exceed practical limits
•Conceptual distinction aids understanding

The Conservative Default

When in doubt, use the foreign key approach. It preserves flexibility, aligns with ER structure, and can always be merged later if needed. Splitting a merged table is harder than merging separate tables.

Real-World Merged Table Examples

Let's examine scenarios where the merged table approach is the clear winner:

USER ↔ USER_PROFILE (Private Information)

Original separation reason: Decomposed for organizational purposes during design

Participation: Total-Total

Every user has a profile (created at registration)
Every profile belongs to one user

Why merge:

Profile is created with user, never independently
Every page load needs user + profile data
Same access control applies
No future 1:N possibility

Result: Single users table with profile columns integrated. Cookie preference, timezone, language all become user columns.

Summary: Merged Table Approach

The merged table approach offers simplicity and performance benefits when used appropriately, but requires strict adherence to its applicability criteria.

Key Takeaways

•Merge only for total-total participation — Partial participation causes NULL proliferation that violates normalization principles.
•The relationship becomes implicit — No foreign key, no constraint; the 1:1 is embedded in table structure.
•Prefix merged columns for clarity — Naming conventions document which conceptual entity columns originated from.
•Views restore conceptual separation — Use views to present the merged table as if it were still two tables.
•Consider future evolution — If 1:1 might become 1:N, avoid merging; splitting is more complex than merging.
•When in doubt, keep separate — The foreign key approach is more flexible and aligns with ER structure.

What's Next:

With both the foreign key and merged table approaches understood, we're ready to examine the decision factors in depth. The next page provides a systematic framework for choosing between these strategies, considering performance, maintainability, semantics, and practical constraints.

Page Complete

You now understand the merged table approach for 1:1 mapping, including its rationale, implementation, and the critical limitation of requiring total-total participation. This prepares you to make informed decisions between merging and keeping tables separate.