UML diagram keys – what is a role?

by

Summary

The “role” box in a UML class diagram represents the semantic role an entity plays in a relationship. It is not a table, not an attribute, and not a standalone object. It is simply a label describing how one class participates in an association. When converting UML to SQL, the role name almost never becomes a column.

Root Cause

The confusion comes from assuming every UML box is a database entity. In UML:

  • A class becomes a table.
  • An association becomes a foreign key.
  • A role is just a name for one end of an association, not a data structure.

Why This Happens in Real Systems

Engineers often misinterpret UML because:

  • UML diagrams mix conceptual modeling with implementation modeling.
  • Role names look like attributes but are actually metadata about relationships.
  • Many UML tools draw roles in small boxes, which resemble classes.

Real-World Impact

Misinterpreting roles can lead to:

  • Incorrect schema design (extra tables that should not exist)
  • Redundant columns that do not map to real data
  • Broken foreign key relationships
  • Overcomplicated SQL that does not match the domain model

Example or Code (if necessary and relevant)

If the UML shows something like:

Crew 1 --- * FlightInstance
       role: pilot

The SQL implementation is simply a foreign key, not a “role” table:

create table crew (
  airline varchar(20) not null,
  employee_number int unique not null,
  fname varchar(20),
  lname varchar(20)
);

create table flight_instance (
  id serial primary key,
  gate varchar(3),
  boarding_time time,
  departure_time time,
  arrival_time time,
  pilot_employee_number int,
  foreign key (pilot_employee_number) references crew(employee_number)
);

The role (“pilot”) is just the name of the relationship end.

How Senior Engineers Fix It

Experienced engineers resolve this by:

  • Mapping classes → tables
  • Mapping associations → foreign keys
  • Ignoring role names unless they clarify which foreign key is which
  • Using clear FK column names (e.g., pilot_employee_number, copilot_employee_number)

Why Juniors Miss It

Less experienced engineers often struggle because:

  • UML diagrams look like database diagrams, but they are not.
  • Role names appear in boxes, which resemble attributes.
  • They assume every labeled element must be implemented in SQL.
  • They lack exposure to conceptual vs. logical vs. physical modeling layers.

If you want, you can describe the exact UML diagram textually and I can map it precisely to SQL.

Leave a Comment