Mapping Strong Entities and Attributes to Tables

When designing a relational database from an Entity-Relationship (ER) diagram, one of the most fundamental tasks is converting the diagram's entities and their attributes into concrete tables that a database management system can store and query. The first category of entity you will encounter — and the most straightforward to map — is the strong entity. Understanding exactly what a strong entity is, how it differs from other constructs in an ER diagram, and how each variety of attribute attached to it should be handled gives you a reliable, repeatable process for producing well-structured relational tables.

A strong entity is any entity type that can be uniquely identified by its own attributes alone, without relying on a relationship with another entity. In ER diagrams, strong entities are drawn as solid rectangles, and each one is accompanied by at least one key attribute — the attribute (or combination of attributes) whose value is guaranteed to be unique across every instance of that entity. For example, an entity called Employee might have a key attribute EmployeeID; no two employees share the same ID, so any individual employee record can be pinpointed without referencing any other entity. This self-sufficiency is the defining characteristic of a strong entity and the property that makes the mapping process relatively clean and direct.

Strong entities stand in contrast to weak entities, which lack a key attribute of their own and must borrow part of their identity from a related (owner) entity. Because strong entities carry their own identity, they are always the first candidates for table creation — and often the tables created from them become anchor points to which other tables, derived from weak entities or relationships, are later connected via foreign keys.

Recognizing strong entities is therefore the indispensable first step before any mapping begins. Walk through your ER diagram and label every rectangle that has a key attribute of its own. These entities will each produce exactly one primary table, and every other construct in the diagram that depends on them — weak entities, multi-valued attributes, relationships — will reference those tables.

Once a strong entity has been identified, the conversion to a relational table follows a clear rule: the name of the strong entity becomes the name of the table. This naming convention is not arbitrary; it preserves traceability between the original design and the implemented schema, making the database easier to understand and maintain. If the ER diagram contains an entity called Product, the resulting table is called Product (or Products, depending on your organisation's naming standards).

The correspondence between the ER model and the relational model is direct: each instance of the entity in the ER diagram — meaning each individual thing of that type in the real world — becomes one row (tuple) in the table. A company with 500 employees would have 500 rows in its Employee table, one for each person. This one-to-one correspondence between entity instances and table rows is a foundational rule of ER-to-relational mapping and should be kept clearly in mind throughout the process.

Consider the following simple example. Suppose an ER diagram contains the strong entity Student with attributes StudentID (key), FirstName, LastName, and DateOfBirth. The resulting relational table looks like this:

Each row represents one student; each column represents one attribute. This clean, flat structure is the direct result of applying the entity-to-table mapping rule.

The most common kind of attribute in an ER diagram is the simple (atomic) attribute — one that holds a single, indivisible value. Simple attributes translate directly into columns of the relational table. When performing this translation, you must assign each column an appropriate data type that matches the nature of the values it will store:

• VARCHAR(n) or CHAR(n) — for text-based values such as names, codes, or descriptions.
• INTEGER or BIGINT — for whole-number values such as counts, IDs, or quantities.
• DECIMAL(p, s) or NUMERIC — for monetary amounts or other values requiring precise decimal representation.
• DATE, TIME, or DATETIME — for calendar and time-based values.
• BOOLEAN — for true/false flags.

Each column must store one atomic value per row. Placing multiple values (for example, a comma-separated list of phone numbers) into a single column violates First Normal Form (1NF), the most basic level of relational normalisation, and creates serious problems for querying, updating, and indexing. Attribute names from the ER diagram are typically preserved as column names; this keeps the implementation consistent with the design and makes it easier for developers and analysts to trace a column back to the original conceptual model.

A composite attribute is one that is itself made up of several simpler sub-attributes. A classic example is Address, which might be composed of Street, City, State, and PostalCode. In an ER diagram, this is shown as an oval (Address) with smaller ovals branching off it for each component. When mapping to a relational table, the standard and recommended approach is to decompose the composite attribute into its individual components, each of which becomes its own column:

CREATE TABLE Customer (
    CustomerID   INTEGER      PRIMARY KEY,
    FirstName    VARCHAR(50)  NOT NULL,
    LastName     VARCHAR(50)  NOT NULL,
    Street       VARCHAR(100),
    City         VARCHAR(50),
    State        CHAR(2),
    PostalCode   VARCHAR(10)
);

Storing the components separately provides significant practical advantages:

• You can filter or sort on individual parts — for example, retrieving all customers in a particular city with WHERE City = 'Austin'.
• You can validate each component independently — a postal code can be checked against a pattern; a state code can be verified against a reference table.
• You can index individual components for faster lookups.

In rare cases, if the application will never need to access individual parts of a composite attribute separately, the whole composite may be stored as a single column (for example, storing a full address as one VARCHAR string). However, this is considered an anti-pattern in most contexts because it sacrifices flexibility and complicates any future need to query or manipulate components individually.

The primary key is arguably the most important decision made during the mapping of a strong entity. The primary key column (or columns) must satisfy two strict constraints:

• Uniqueness: No two rows in the table may have the same primary key value.
• Non-nullability: Every row must have a primary key value; nulls are not permitted.

Together, these constraints enforce entity integrity — the guarantee that every row in the table represents a distinct, identifiable entity instance. The primary key in the relational table corresponds directly to the key attribute (marked with an underline in the ER diagram) of the strong entity.

Sometimes a single attribute is sufficient as a primary key — for example, EmployeeID for an Employee entity. In other cases, the ER diagram shows a composite key: two or more attributes that together (but not individually) form a unique identifier. For instance, a CourseOffering entity might be uniquely identified by the combination of CourseCode and Semester. In the relational table, this is expressed as a composite primary key:

CREATE TABLE CourseOffering (
    CourseCode   CHAR(8)     NOT NULL,
    Semester     VARCHAR(10) NOT NULL,
    Room         VARCHAR(20),
    MaxEnrolment INTEGER,
    PRIMARY KEY (CourseCode, Semester)
);

Choosing the right primary key matters beyond the table itself. Primary keys are the mechanism by which foreign keys in other tables reference rows in this one, forming the relational links that correspond to relationships in the ER diagram. A poorly chosen key — one that is not truly unique, or that might change over time — will propagate errors and update anomalies throughout the schema. Natural keys (meaningful real-world values) can work well when stable, but many designs prefer surrogate keys (system-generated integers with no real-world meaning) for their guaranteed uniqueness and immutability.

A multi-valued attribute is one that can hold more than one value for a single entity instance. In an ER diagram, multi-valued attributes are drawn with a double oval. A common example is PhoneNumber on a Person entity — an individual may have a mobile number, a home number, and a work number simultaneously. Because a relational column can store only one atomic value per row, placing multiple phone numbers in a single column is not a valid relational solution.

The standard mapping strategy is to create a separate table for the multi-valued attribute. This new table contains:

• A foreign key column that references the primary key of the original entity's table.
• A column for the attribute value itself.
• A primary key composed of the foreign key plus the attribute value (since the same value might theoretically appear for different entity instances, but the combination is unique).

For example, the multi-valued attribute PhoneNumber on the Person entity maps as follows:

CREATE TABLE Person (
    PersonID  INTEGER      PRIMARY KEY,
    FullName  VARCHAR(100) NOT NULL
);

CREATE TABLE PersonPhone (
    PersonID    INTEGER     NOT NULL,
    PhoneNumber VARCHAR(20) NOT NULL,
    PRIMARY KEY (PersonID, PhoneNumber),
    FOREIGN KEY (PersonID) REFERENCES Person(PersonID)
);

With this structure, each row in PersonPhone records one phone number for one person. A person with three phone numbers has three rows in PersonPhone, all sharing the same PersonID. This approach preserves relational integrity, allows any number of values per entity instance, and makes it easy to query, add, or remove individual values without touching the main entity table.

Person 101 has two phone numbers; Person 102 has one. The relationship is clear, normalised, and fully queryable.

A derived attribute is one whose value can be calculated from other stored data rather than being independently recorded. In ER diagrams, derived attributes are depicted with a dashed oval. A familiar example is Age, which can always be derived from DateOfBirth and the current date, or TotalOrderValue, which can be computed by summing the line items of an order.

Because derived attributes can always be recalculated, they do not need to be stored as columns. The preferred approach in most cases is to omit them from the table and instead compute them in queries or application code whenever needed:

-- Compute Age on the fly from DateOfBirth
SELECT
    StudentID,
    FirstName,
    LastName,
    TIMESTAMPDIFF(YEAR, DateOfBirth, CURDATE()) AS Age
FROM Student;

This approach guarantees that the derived value is always current and eliminates the risk of the stored value becoming stale.

However, there are scenarios in which storing a derived attribute as a physical column — a practice sometimes called materialisation — is justified on performance grounds. If a derived value is extremely expensive to compute (for example, aggregating millions of sales records to produce a running total) and is read far more frequently than the underlying data changes, it may be acceptable to cache it as a stored column. The trade-off is significant: every time the underlying data changes, the materialised value must be updated, either by application logic or a database trigger. Failure to keep the materialised value in sync creates data inconsistency, which is one of the most damaging problems a database can suffer.

The decision framework for derived attributes can be summarised as:

• Omit and compute — use when the computation is inexpensive or when data changes frequently. This is the default and safest choice.
• Materialise (store) — use only when computation is expensive, the value is read very frequently, and a reliable update mechanism (trigger, scheduled job, or application layer) is in place to maintain accuracy.

Bringing all of these mapping rules together, the overall process for converting a strong entity and its attributes into a relational table can be summarised:

Applying these rules consistently ensures that the relational schema faithfully represents the conceptual model, supports efficient querying, maintains data integrity, and remains extensible as requirements evolve. The mapping of strong entities is the foundation on which the rest of the schema — relationships, weak entities, and specialisation hierarchies — is subsequently built.