Mapping Entities and Attributes to Tables and Columns

Learning outcomes

Conceptual and logical models describe domain meaning. A relational schema must turn that meaning into relations, columns, keys, constraints, and references that a DBMS can enforce. The goal is not to copy boxes into tables mechanically; it is to preserve the semantics established in earlier chapters while choosing a precise relational representation.

The basic mapping rule

For a strong entity type, the standard relational mapping is:

1. Create one relation for the entity type.
2. Create a column for each simple stored attribute.
3. Select a primary key or introduce a surrogate primary key.
4. Preserve alternate candidate keys with UNIQUE constraints.
5. Preserve mandatory attributes with NOT NULL.
6. Express business domains using suitable types, checks, and references.

Conceptual entity to relation

Suppose the conceptual model contains:

Customer  customer_id  legal_name  contact_email  registered_at

A first relational mapping could be:

CREATE TABLE customer (    customer_id    INTEGER PRIMARY KEY,    legal_name     TEXT NOT NULL,    contact_email  TEXT,    registered_at  TEXT NOT NULL);

The mapping is straightforward, but the schema is not complete until domain rules are considered. Is contact_email unique? Can it be absent? Is one customer allowed several contacts? What timestamp semantics does registered_at use? Mapping exposes implementation questions that may send you back to requirements.

Choose one row grain

For the customer table, one row should mean exactly one Customer entity instance. This sounds obvious, but many poor schemas mix multiple grains:

customer_idcustomer_nameasset_serial_numberwork_order_numbertechnician_name

One row now tries to represent Customer, Asset, WorkOrder, and Technician at the same time. Repeating facts and anomalies appear immediately.

Primary keys and alternate keys

Suppose Technician has an internal surrogate and an authoritative employee number:

CREATE TABLE technician (    technician_id   INTEGER PRIMARY KEY,    employee_number TEXT NOT NULL UNIQUE,    full_name       TEXT NOT NULL,    active          INTEGER NOT NULL DEFAULT 1);

The surrogate handles internal references; the unique business identifier is not discarded. This preserves both implementation convenience and business semantics.

Map domains, not merely generic types

A logical attribute called quantity_used should not become an unconstrained integer merely because the DBMS supports INTEGER.

quantity_used INTEGER NOT NULLCHECK (quantity_used > 0)

Similarly, a three-letter currency code can be constrained by length or, preferably, referenced to a governed Currency relation if the domain requires metadata and validation.

Nullability should follow optionality

Map required attributes to NOT NULL when the business rule truly requires the value for every valid row.

Do not store derived values automatically

If Customer has a conceptual derived attribute account_age, the relational schema normally stores the source fact:

registered_at

and derives age when needed. Storing both values creates redundancy unless a deliberate caching/materialization strategy exists.

Logical names versus physical names

A logical model might use:

Work OrderProblem DescriptionOpened At

The physical schema may adopt a consistent naming convention:

work_orderproblem_descriptionopened_at

The names differ stylistically but should preserve the same semantics. Maintain traceability between logical attributes and physical columns.

WorkshopHub core tables

CREATE TABLE customer (    customer_id    INTEGER PRIMARY KEY,    legal_name     TEXT NOT NULL,    registered_at  TEXT NOT NULL);CREATE TABLE asset (    asset_id         INTEGER PRIMARY KEY,    manufacturer_id  INTEGER,    serial_number    TEXT,    model_name       TEXT,    registered_at    TEXT NOT NULL);CREATE TABLE technician (    technician_id   INTEGER PRIMARY KEY,    employee_number TEXT NOT NULL UNIQUE,    full_name       TEXT NOT NULL);CREATE TABLE part (    part_id       INTEGER PRIMARY KEY,    sku           TEXT NOT NULL UNIQUE,    description   TEXT NOT NULL);

Relationships are intentionally omitted here because Lessons 2 and 3 map them systematically.

Reference entities become relations too

If WorkOrderStatus is a governed reference entity:

CREATE TABLE work_order_status (    status_code   TEXT PRIMARY KEY,    display_name  TEXT NOT NULL UNIQUE,    is_terminal   INTEGER NOT NULL DEFAULT 0);

WorkOrder can then reference status_code. If statuses are instead a tiny fixed application enum, a check constraint may be sufficient. The relational mapping follows the conceptual decision.

Weak entities require owner identity

A weak entity such as WorkOrderLine may map to:

CREATE TABLE work_order_line (    work_order_id INTEGER NOT NULL,    line_number   INTEGER NOT NULL,    description   TEXT NOT NULL,    PRIMARY KEY (work_order_id, line_number),    FOREIGN KEY (work_order_id)      REFERENCES work_order(work_order_id));

The owner key forms part of the child's key because line number is only unique within a work order.

Mapping should preserve vocabulary

If the conceptual model says Asset, do not rename the physical table object_master without a compelling reason. If the domain says WorkOrderAssignment, avoid reducing it to xref_2. Physical schemas are long-lived documentation.

Practice: map a small entity set

Warehouse  warehouse_code [candidate key]  name  opened_dateProduct  sku [candidate key]  name  unit_weight  active

Summary and next lesson

Strong entities map naturally to relations, but good mapping preserves row grain, keys, alternate uniqueness, domains, nullability, and semantic names. The next lesson adds one-to-many and one-to-one relationships and shows how foreign-key placement follows cardinality and participation.

References

• Ramez Elmasri and Shamkant B. Navathe, Fundamentals of Database Systems.
• C. J. Date, Database Design and Relational Theory.
• PostgreSQL and SQLite documentation for constraints and referential integrity.