ER Modeling: Entities, Relationships, Roles, and Time

ER modeling starts before tables, foreign keys, and junction tables. It begins with a more important question:

What does each piece of data mean, and where does it actually belong?

That question connects the basic parts of ER modeling to more advanced ideas such as associative entities, role assignments, temporal data, and constraints across rows.

Start with the domain, not the tables

An entity is a real-world thing represented in a model. In an organisation, the entities might be:

Employee
Department
Company
Skill

These may later become tables, but the ER model comes first. It describes the domain before any decisions are made about how PostgreSQL will store it.

An attribute describes an entity. Attributes can have different meanings:

Simple
employee_id

Composite
name
 ├─ first_name
 └─ last_name

Multivalued
skills = {Node.js, PostgreSQL, AWS}

Derived
age ← calculated from date_of_birth

This distinction is conceptual rather than physical. Calling skills multivalued does not require an array or JSONB column. The values could be stored as separate rows. The ER model says what the data represents; the relational schema says how it is stored.

The same applies to derived data. Age changes over time, while a date of birth does not. Modeling age as derived makes that meaning explicit before deciding whether to calculate it in a query, a view, or somewhere else.

Cardinality and optionality answer different questions

A relationship connects entities. Cardinality asks how many instances on one side can relate to the other:

1:N   Company → Departments
M:N   Employees ↔ Skills
1:1   Employee ↔ EmployeeProfile

In a one-to-many relationship, the foreign key normally goes on the many side. If one company has many departments, company_id belongs on Department.

In a many-to-many relationship, a junction table normally connects the two entities:

EMPLOYEE_SKILL
- employee_id
- skill_id

Cardinality is sometimes confused with whether a relationship is required, but they are separate questions.

employee.department_id NULL

This allows an employee to have no department.

employee.department_id NOT NULL

This requires every employee to have one.

Cardinality asks how many? Participation, or optionality, asks is the relationship required?

Some entities need their parent for identity

A weak entity cannot be uniquely identified without a related parent.

BUILDING
- building_id

ROOM
- building_id
- room_number

PRIMARY KEY (building_id, room_number)

Room 101 is not necessarily unique across every building. Its identity depends on both the building and the room number.

This is more than choosing a composite primary key. It expresses something about the domain: the room's identity only makes sense within its building.

A role is not always an employee type

At first, it is tempting to model employees using inheritance:

Employee
 ├─ Engineer
 └─ Manager

ER inheritance can allow overlapping subtypes, so this structure does not necessarily prevent the same employee from being both. But in many organisations, Engineer and Manager are roles with their own assignment details:

Employee A → Engineer
Employee A → Manager

Representing each assignment directly gives those details a natural place:

This model also handles an employee who is an Engineer in one department and a Manager in another. In that case, department_id does not describe the employee as a whole. It describes a particular role assignment.

A junction can become a real domain concept

EMPLOYEE_ROLE begins like a junction between employees and roles. It stops being “just a junction table” when the relationship itself has meaningful data:

EMPLOYEE_ROLE
- id
- employee_id
- role_id
- department_id
- assigned_at
- starts_at
- ends_at

At that point, EmployeeRole is better understood as an associative entity. The assignment has its own identity, attributes, and rules.

For example, primary_language does not belong to the generic Engineer role because Engineer itself does not have one language. It could belong to a particular employee's Engineer assignment or profile.

The test is not “which table is easiest to reach?” It is “which thing does this fact describe?”

Time turns assignments into history

Adding starts_at and ends_at lets the model preserve changes instead of overwriting them:

Engineer | Platform | 2024 → 2025
Engineer | Platform | 2026 → NULL

Here, ends_at = NULL means the second assignment is ongoing. The two rows show that the employee held the same role in the same department during separate periods.

This creates a rule that a simple unique constraint cannot fully express:

The same employee, role, and department must not have overlapping assignment periods.

The rule compares a time range with ranges in other rows. NOT NULL, UNIQUE, and FOREIGN KEY constraints are not always enough for that kind of invariant.

PostgreSQL range types and exclusion constraints are a natural next topic because they can express rules about intervals that must not overlap.

Questions to ask before writing a schema

These questions help test an ER model before turning it into a schema:

1. What real-world things does the model represent?
2. What does each attribute describe?
3. How many relationships are allowed on each side?
4. Is each relationship optional or mandatory?
5. Does an entity have its own identity, or does it depend on a parent?
6. Is a category really a type, or can it be a role held alongside other roles?
7. Does a relationship carry enough data to become a first-class entity?
8. Does the model need to preserve history?
9. Which rules must remain true across more than one row?

ER modeling makes relational thinking more precise. Good database design starts before table definitions. It starts by identifying what things mean, where their attributes belong, and which constraints must always remain true.