Mapping Entities and Relationships to Relational Database Tables

ByJu Yeon Eum

An entity-relationship diagram describes the concepts, properties, and relationships that a database must represent. Before the database can store records, the logical design must be converted into tables, columns, keys, and constraints.

This conversion process is called mapping an entity-relationship model to a relational schema.

The basic mappings are straightforward:

  • Entities generally become tables.
  • Attributes generally become columns.
  • Identifiers become primary keys.
  • Relationships are implemented with foreign keys or associative tables.
  • Business rules become constraints.

However, a dependable database design requires more than mechanically converting rectangles into tables. Cardinality, optionality, data types, normalization, integrity, concurrency, and indexing must also be considered.

From ERD to Relational Database

An entity-relationship diagram, or ERD, visually represents:

  • Entities
  • Attributes
  • Identifiers
  • Relationships
  • Cardinality
  • Optionality
  • Business constraints

A relational database represents data using related tables consisting of rows and columns.

The mapping process translates the semantic structure of an ERD into a relational structure that a database management system can implement.

ERD
  ↓
Logical relational schema
  ↓
Physical database design
  ↓
Database tables and records

The Main Relational Components

Before performing the mapping, it is helpful to distinguish the principal relational components.

Table

A table represents a collection of records with a common structure.

BOOK
AUTHOR
PUBLISHER
LOAN

Row

A row represents one record or entity instance.

book_id: 101
title: Database Systems
publication_year: 2025

Column

A column represents an attribute stored for every applicable row.

book_id
title
isbn
publication_year

Primary Key

A primary key uniquely identifies each row.

BOOK.book_id

Foreign Key

A foreign key references a candidate or primary key in another table and helps enforce a relationship.

BOOK.publisher_id
    → PUBLISHER.publisher_id

Constraint

A constraint restricts the data that may be stored.

Examples include:

  • NOT NULL
  • UNIQUE
  • CHECK
  • PRIMARY KEY
  • FOREIGN KEY

Step 1: Map Strong Entities to Tables

A strong entity has its own identifier and can exist independently within the model.

Consider this entity:

BOOK
--------------------
book_id
title
isbn
publication_year
price

It becomes a table:

CREATE TABLE book (
    book_id BIGINT PRIMARY KEY,
    title VARCHAR(300) NOT NULL,
    isbn VARCHAR(20),
    publication_year INTEGER,
    price DECIMAL(10, 2)
);

The entity name commonly becomes the table name, although organizations should apply a consistent naming convention.

For example, choose one of the following approaches:

book, author, publisher

or:

books, authors, publishers

Do not switch between singular and plural naming without a reason.

Step 2: Map Attributes to Columns

Each simple attribute generally becomes a column in the corresponding table.

BOOK.title            → book.title
BOOK.isbn             → book.isbn
BOOK.publication_year → book.publication_year

The physical design must assign a suitable database type to each column:

AttributePossible data type
Book IDBIGINT
TitleVARCHAR(300)
ISBNVARCHAR(20)
Publication yearINTEGER
PriceDECIMAL(10,2)
Publication dateDATE

Data types should reflect the meaning and valid operations of the data.

For example, an ISBN looks numeric but should normally be stored as text because:

  • It is an identifier, not a measured quantity.
  • Leading zeros can be significant.
  • It can contain separators.
  • ISBN-10 may contain X as a check character.

Step 3: Select a Primary Key

Every independently stored entity should have a stable way to identify its records.

The BOOK table can use an internal identifier:

book_id BIGINT GENERATED ALWAYS AS IDENTITY

Complete definition:

CREATE TABLE book (
    book_id BIGINT GENERATED ALWAYS AS IDENTITY,
    title VARCHAR(300) NOT NULL,
    isbn VARCHAR(20),
    publication_year INTEGER,
    price DECIMAL(10, 2),

    CONSTRAINT pk_book
        PRIMARY KEY (book_id),

    CONSTRAINT uq_book_isbn
        UNIQUE (isbn)
);

Here:

  • book_id is the primary key.
  • isbn is an alternate identifier protected by a unique constraint.

Using an internal key can be useful because business identifiers may be:

  • Missing
  • Corrected
  • Reissued
  • Formatted inconsistently
  • Different across source systems

A primary key should be:

  • Unique
  • Non-null
  • Stable
  • As small and simple as practical

Step 4: Map One-to-One Relationships

Consider:

PERSON 1 ───── 1 PERSON_PROFILE

A one-to-one relationship is commonly implemented by placing a foreign key with a unique constraint in one table.

CREATE TABLE person (
    person_id BIGINT PRIMARY KEY,
    full_name VARCHAR(200) NOT NULL
);

CREATE TABLE person_profile (
    profile_id BIGINT PRIMARY KEY,
    person_id BIGINT NOT NULL UNIQUE,
    biography TEXT,

    CONSTRAINT fk_profile_person
        FOREIGN KEY (person_id)
        REFERENCES person (person_id)
);

The UNIQUE constraint prevents multiple profiles from referencing the same person.

Another implementation uses the parent’s key as both the primary and foreign key:

CREATE TABLE person_profile (
    person_id BIGINT PRIMARY KEY,
    biography TEXT,

    CONSTRAINT fk_profile_person
        FOREIGN KEY (person_id)
        REFERENCES person (person_id)
);

Should the Entities Be Separate?

A one-to-one relationship does not automatically require two tables.

Separate tables may be appropriate when:

  • One group of attributes is optional.
  • Sensitive data needs separate permissions.
  • Large fields are rarely accessed.
  • The records have different lifecycles.
  • The model represents distinct business concepts.

Otherwise, combining the attributes into one table may be simpler.

Step 5: Map One-to-Many Relationships

Consider:

PUBLISHER 1 ───── many BOOK

One publisher may publish multiple books, while each book belongs to one publisher in this simplified model.

The primary key from the one side becomes a foreign key on the many side:

CREATE TABLE publisher (
    publisher_id BIGINT GENERATED ALWAYS AS IDENTITY,
    publisher_name VARCHAR(200) NOT NULL,

    CONSTRAINT pk_publisher
        PRIMARY KEY (publisher_id)
);

CREATE TABLE book (
    book_id BIGINT GENERATED ALWAYS AS IDENTITY,
    title VARCHAR(300) NOT NULL,
    publisher_id BIGINT NOT NULL,

    CONSTRAINT pk_book
        PRIMARY KEY (book_id),

    CONSTRAINT fk_book_publisher
        FOREIGN KEY (publisher_id)
        REFERENCES publisher (publisher_id)
);

Many book rows can contain the same publisher_id, but each book row contains only one publisher reference.

Representing Optionality

If every book must have a publisher:

publisher_id BIGINT NOT NULL

If a book may temporarily exist without a known publisher:

publisher_id BIGINT

Nullability helps implement minimum cardinality:

  • NOT NULL indicates mandatory participation.
  • A nullable foreign key permits optional participation.

Step 6: Map Many-to-Many Relationships

Consider the following business rules:

  • A book may have multiple authors.
  • An author may write multiple books.

This is a many-to-many relationship:

AUTHOR many ───── many BOOK

It should not be represented by an author text column in the BOOK table. A single text field would make multiple authors difficult to identify, validate, query, and update.

Instead, introduce an associative table:

AUTHOR 1 ─── many BOOK_AUTHOR many ─── 1 BOOK
CREATE TABLE author (
    author_id BIGINT GENERATED ALWAYS AS IDENTITY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(254),

    CONSTRAINT pk_author
        PRIMARY KEY (author_id)
);

CREATE TABLE book (
    book_id BIGINT GENERATED ALWAYS AS IDENTITY,
    title VARCHAR(300) NOT NULL,
    isbn VARCHAR(20),

    CONSTRAINT pk_book
        PRIMARY KEY (book_id),

    CONSTRAINT uq_book_isbn
        UNIQUE (isbn)
);

CREATE TABLE book_author (
    book_id BIGINT NOT NULL,
    author_id BIGINT NOT NULL,
    author_order INTEGER,
    contribution_role VARCHAR(100),

    CONSTRAINT pk_book_author
        PRIMARY KEY (book_id, author_id),

    CONSTRAINT fk_book_author_book
        FOREIGN KEY (book_id)
        REFERENCES book (book_id),

    CONSTRAINT fk_book_author_author
        FOREIGN KEY (author_id)
        REFERENCES author (author_id),

    CONSTRAINT chk_author_order
        CHECK (
            author_order IS NULL
            OR author_order > 0
        )
);

The associative table converts one many-to-many relationship into two one-to-many relationships.

It can also store attributes of the relationship:

  • Author order
  • Contribution role
  • Royalty percentage
  • Credited name

Step 7: Map Multivalued Attributes

Suppose one author can have several email addresses.

A design such as this violates the principle that a column should contain one value of its declared meaning:

email = "a@example.com, author@example.org"

Numbered columns are also difficult to maintain:

email_1
email_2
email_3

Instead, create a related table:

CREATE TABLE author_email (
    author_email_id BIGINT GENERATED ALWAYS AS IDENTITY,
    author_id BIGINT NOT NULL,
    email VARCHAR(254) NOT NULL,
    email_type VARCHAR(30),
    is_primary BOOLEAN NOT NULL DEFAULT FALSE,

    CONSTRAINT pk_author_email
        PRIMARY KEY (author_email_id),

    CONSTRAINT uq_author_email
        UNIQUE (author_id, email),

    CONSTRAINT fk_author_email_author
        FOREIGN KEY (author_id)
        REFERENCES author (author_id)
);

This supports any reasonable number of email addresses without changing the table structure.

Step 8: Map Composite Attributes

A composite attribute can be divided into meaningful components.

For example:

author_name
├── first_name
├── middle_name
└── last_name

If applications must search, sort, or display the components independently, store them separately:

first_name VARCHAR(100),
middle_name VARCHAR(100),
last_name VARCHAR(100)

An address might be represented as:

street_line_1
street_line_2
city
region
postal_code
country_code

Do not decompose a value automatically. The appropriate structure depends on how the application will use it.

Step 9: Map Weak Entities

A weak entity depends on another entity for identification.

Consider an order item:

ORDER
- order_id

ORDER_ITEM
- line_number
- product_id
- quantity

A line number is meaningful only within its order. Therefore, the order item can use a composite primary key:

CREATE TABLE order_item (
    order_id BIGINT NOT NULL,
    line_number INTEGER NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INTEGER NOT NULL,

    CONSTRAINT pk_order_item
        PRIMARY KEY (order_id, line_number),

    CONSTRAINT fk_order_item_order
        FOREIGN KEY (order_id)
        REFERENCES customer_order (order_id),

    CONSTRAINT chk_order_item_quantity
        CHECK (quantity > 0)
);

The parent identifier forms part of the child entity’s identifier.

Step 10: Map Recursive Relationships

An entity can have a relationship with itself.

For example:

EMPLOYEE manages EMPLOYEE

Implementation:

CREATE TABLE employee (
    employee_id BIGINT PRIMARY KEY,
    employee_name VARCHAR(200) NOT NULL,
    manager_id BIGINT,

    CONSTRAINT fk_employee_manager
        FOREIGN KEY (manager_id)
        REFERENCES employee (employee_id),

    CONSTRAINT chk_employee_not_own_manager
        CHECK (
            manager_id IS NULL
            OR manager_id <> employee_id
        )
);

The self-referencing foreign key represents the reporting relationship.

This design can support organizational hierarchies, although preventing longer cycles may require additional application or database logic.

Step 11: Define Validation Constraints

Data validation should occur as close to the data as practical.

Required Values

title VARCHAR(300) NOT NULL

Unique Values

isbn VARCHAR(20) UNIQUE

Valid Ranges

publication_year INTEGER
    CHECK (
        publication_year BETWEEN 1450 AND 2200
    )

Positive Monetary Values

price DECIMAL(10, 2)
    CHECK (
        price IS NULL
        OR price >= 0
    )

Valid Enumerated Values

status VARCHAR(20)
    CHECK (
        status IN (
            'available',
            'on_loan',
            'lost',
            'repair'
        )
    )

Application validation improves the user experience, but database constraints provide protection regardless of which application writes the data.

Do Not Use “Unknown” as a Universal Default

The source suggests using the string "Unknown" when an author is unavailable. This is usually inappropriate for a normalized design.

If the author is a separate entity, the book should be connected through BOOK_AUTHOR. If no author has been recorded yet, no corresponding relationship row exists.

A text value such as "Unknown" can be ambiguous:

  • Is the author genuinely unknown?
  • Has the information not been entered?
  • Does the book have no individual author?
  • Is the author an organization?
  • Is the information deliberately withheld?

Use explicit modeling where these distinctions matter.

Possible approaches include:

  • A nullable value
  • No relationship record
  • A clearly defined status column
  • A specific organization-author entity
  • A documented controlled vocabulary

Defaults should represent genuine business defaults, not conceal missing information.

Step 12: Normalize the Schema

Normalization reduces unnecessary duplication and prevents modification anomalies.

Consider this table:

book_idtitleauthor_nameauthor_email
1Data SystemsMaria Chenmaria@example.com
2SQL DesignMaria Chenmaria@example.com

The author’s information is duplicated. If the email changes, several rows must be updated.

A better structure is:

BOOK
AUTHOR
BOOK_AUTHOR

This design stores the author once and connects that author to multiple books.

Why Normalize?

Normalization helps prevent:

  • Update anomalies
  • Insertion anomalies
  • Deletion anomalies
  • Conflicting duplicate values
  • Unnecessary storage duplication

Normalization should be guided by dependencies and business meaning, not applied mechanically without considering the workload.

Step 13: Create Views for Simplified Access

A database view is a stored query that presents data through a virtual table.

For example:

CREATE VIEW book_author_list AS
SELECT
    b.book_id,
    b.title,
    a.author_id,
    a.first_name,
    a.last_name,
    ba.author_order
FROM book AS b
JOIN book_author AS ba
    ON ba.book_id = b.book_id
JOIN author AS a
    ON a.author_id = ba.author_id;

Users can query the view:

SELECT *
FROM book_author_list
ORDER BY title, author_order;

Views can help:

  • Simplify complex joins
  • Present consistent business definitions
  • Restrict access to selected columns
  • Provide stable interfaces
  • Support reporting

A conventional view does not normally store a separate copy of the result. A materialized view may store results and require refreshes, depending on the DBMS.

Step 14: Design for Concurrency

A relational database may serve many users and applications simultaneously.

Concurrency control protects data when operations overlap.

Important mechanisms include:

  • Transactions
  • Isolation levels
  • Row or table locking
  • Multi-version concurrency control
  • Optimistic concurrency checks
  • Unique and referential constraints

A last_modified timestamp can help detect changes, but adding the column alone does not prevent conflicts.

Example:

ALTER TABLE book
ADD COLUMN updated_at TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP;

An optimistic update can include the previously read timestamp:

UPDATE book
SET
    title = 'Advanced Database Systems',
    updated_at = CURRENT_TIMESTAMP
WHERE book_id = 101
  AND updated_at = :previous_updated_at;

If no row is updated, another transaction may have modified the record.

Some databases provide dedicated row-version or system-version mechanisms that are more reliable than timestamps for concurrency tokens.

Step 15: Add Indexes Deliberately

Indexes can accelerate filtering, joining, and sorting, but they also consume storage and make inserts and updates more expensive.

Foreign-key columns frequently benefit from indexes:

CREATE INDEX idx_book_publisher
ON book (publisher_id);

CREATE INDEX idx_book_author_author
ON book_author (author_id);

A search requirement may justify another index:

CREATE INDEX idx_book_title
ON book (title);

Do not index every column automatically. Indexes should reflect actual query patterns and be validated with execution plans and workload measurements.

Step 16: Load Data Only After Defining the Structure

Adding records is not part of mapping an entity to a table. It is a subsequent data-loading or transaction activity.

The design sequence is:

  1. Define the entity.
  2. Identify its attributes and relationships.
  3. Select keys.
  4. Map it to tables and columns.
  5. Add constraints and indexes.
  6. Create the physical schema.
  7. Load or enter records.

Example inserts:

INSERT INTO author (
    first_name,
    last_name,
    email
)
VALUES (
    'Maria',
    'Chen',
    'maria@example.com'
);
INSERT INTO book (
    title,
    isbn
)
VALUES (
    'Practical Data Modeling',
    '9780123456789'
);

The database structure exists before these records are added.

Complete Library Mapping Example

The following logical entities might appear in a library ERD:

PUBLISHER
AUTHOR
BOOK
BOOK_AUTHOR
BOOK_COPY
BORROWER
LOAN

Their relationships are:

PUBLISHER 1 ─── many BOOK

AUTHOR 1 ─── many BOOK_AUTHOR
BOOK 1 ─── many BOOK_AUTHOR

BOOK 1 ─── many BOOK_COPY

BORROWER 1 ─── many LOAN
BOOK_COPY 1 ─── many LOAN

A simplified relational schema is:

CREATE TABLE publisher (
    publisher_id BIGINT GENERATED ALWAYS AS IDENTITY
        PRIMARY KEY,
    publisher_name VARCHAR(200) NOT NULL
);

CREATE TABLE author (
    author_id BIGINT GENERATED ALWAYS AS IDENTITY
        PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(254)
);

CREATE TABLE book (
    book_id BIGINT GENERATED ALWAYS AS IDENTITY
        PRIMARY KEY,
    publisher_id BIGINT,
    title VARCHAR(300) NOT NULL,
    isbn VARCHAR(20) UNIQUE,
    publication_year INTEGER,

    CONSTRAINT fk_book_publisher
        FOREIGN KEY (publisher_id)
        REFERENCES publisher (publisher_id),

    CONSTRAINT chk_publication_year
        CHECK (
            publication_year IS NULL
            OR publication_year BETWEEN 1450 AND 2200
        )
);

CREATE TABLE book_author (
    book_id BIGINT NOT NULL,
    author_id BIGINT NOT NULL,
    author_order INTEGER,

    CONSTRAINT pk_book_author
        PRIMARY KEY (book_id, author_id),

    CONSTRAINT fk_book_author_book
        FOREIGN KEY (book_id)
        REFERENCES book (book_id),

    CONSTRAINT fk_book_author_author
        FOREIGN KEY (author_id)
        REFERENCES author (author_id)
);

CREATE TABLE book_copy (
    copy_id BIGINT GENERATED ALWAYS AS IDENTITY
        PRIMARY KEY,
    book_id BIGINT NOT NULL,
    status VARCHAR(20) NOT NULL
        DEFAULT 'available',

    CONSTRAINT fk_copy_book
        FOREIGN KEY (book_id)
        REFERENCES book (book_id),

    CONSTRAINT chk_copy_status
        CHECK (
            status IN (
                'available',
                'on_loan',
                'lost',
                'repair'
            )
        )
);

CREATE TABLE borrower (
    borrower_id BIGINT GENERATED ALWAYS AS IDENTITY
        PRIMARY KEY,
    borrower_name VARCHAR(200) NOT NULL,
    email VARCHAR(254)
);

CREATE TABLE loan (
    loan_id BIGINT GENERATED ALWAYS AS IDENTITY
        PRIMARY KEY,
    borrower_id BIGINT NOT NULL,
    copy_id BIGINT NOT NULL,
    checkout_date DATE NOT NULL,
    due_date DATE NOT NULL,
    return_date DATE,

    CONSTRAINT fk_loan_borrower
        FOREIGN KEY (borrower_id)
        REFERENCES borrower (borrower_id),

    CONSTRAINT fk_loan_copy
        FOREIGN KEY (copy_id)
        REFERENCES book_copy (copy_id),

    CONSTRAINT chk_loan_due_date
        CHECK (due_date >= checkout_date),

    CONSTRAINT chk_loan_return_date
        CHECK (
            return_date IS NULL
            OR return_date >= checkout_date
        )
);

Exact identity syntax, data types, and constraint capabilities may vary across database products. A physical model should always be adjusted for its target DBMS.

Relational Database Design Checklist

Before implementing the schema, verify the following:

Entities and Attributes

  • Does each table represent one clear concept?
  • Does each column contain one type of value?
  • Are multivalued attributes stored in related tables?
  • Are names and definitions consistent?

Keys and Relationships

  • Does each table have a stable primary key?
  • Are foreign keys defined?
  • Are one-to-one relationships protected with uniqueness?
  • Are many-to-many relationships resolved with associative tables?
  • Does foreign-key nullability match optionality?

Integrity

  • Are required values marked NOT NULL?
  • Are unique business identifiers protected?
  • Are valid ranges enforced?
  • Are delete and update behaviors defined deliberately?
  • Are missing and unknown values represented meaningfully?

Performance and Access

  • Do indexes support important queries and joins?
  • Are views useful for common access patterns?
  • Have concurrency requirements been considered?
  • Are permissions and sensitive fields addressed?
  • Does the design match the expected workload?

Key Takeaways

  • Mapping converts an ERD into a relational database schema.
  • Strong entities generally become tables.
  • Simple attributes generally become columns.
  • Entity identifiers become primary keys.
  • One-to-one relationships usually require a unique foreign key.
  • One-to-many relationships place the foreign key on the many side.
  • Many-to-many relationships require an associative table.
  • Multivalued attributes should normally become related tables.
  • Business rules should be enforced with database constraints where practical.
  • The author of a book should not be a single text column when books can have multiple authors.
  • "Unknown" should not be used as a universal substitute for missing data.
  • Views simplify access but do not replace a sound underlying schema.
  • Timestamps can support optimistic concurrency but do not control concurrency by themselves.
  • Data is loaded after the database structure has been designed and created.

Conclusion

Mapping an ERD to relational tables transforms a conceptual representation into a structure that a database can enforce and applications can use.

The transformation requires careful attention to identifiers, relationship cardinality, optionality, normalization, data types, and constraints. When these rules are mapped correctly, the resulting schema protects data integrity and remains easier to query, maintain, and extend.

One-sentence summary: Mapping an ERD to a relational schema converts entities into tables, attributes into columns, identifiers into primary keys, and relationships into foreign keys or associative tables.

Similar Posts

Questions, corrections, or additional insights?