Skip to content
LLD Learn/Relational Modeling Foundations
Browsing as a guest. Sign in to save your progress and earn XP as you complete chapters.

Entities, Attributes & Relationships

5 min read

You'll learn to

  • -Turn a plain-English scenario into entities, attributes and relationships
  • -Decide whether something is its own entity or just an attribute, using one test
  • -Name a relationship's cardinality (one-to-one, one-to-many, many-to-many) before drawing any table

A database schema is the blueprint of a database: which tables exist, which columns each has, and how the tables connect. Designing one starts the same way a good class design does, by finding the nouns. An entity is a distinct thing the system must keep track of, such as a passenger, a booking or a cabin. An attribute is one piece of data that describes a single instance of that thing, such as a passenger's age.

An Analogy: Planning a Filing Cabinet

Before you buy a filing cabinet you decide the drawers, one for each kind of thing you keep: one for passengers, one for cabins. Then you decide what goes on each card inside a drawer: a passenger card has a name and an age. Last, you decide how cards in different drawers refer to each other: a passenger card says which cabin card they sleep in. Entities are the drawers, attributes are the lines on a card, and relationships are the references between cards. Getting this first pass right matters more than any later tuning, because keys, normalization and indexing all build on it.

From a Scenario to a First Entity List

Take the scenario: "track passengers boarding a ship, each with a name, an age, and the cabin they are assigned to." Underline the nouns, exactly as the UML chapter in Phase 1 taught: passenger, name, age, cabin. Passenger and Cabin are substantial enough to be entities, because they have several attributes and an identity of their own. Name and age are not entities. They are attributes that belong to Passenger. The statement below creates the first table. CREATE TABLE belongs to DDL, data definition language, the part of SQL that describes the shape of the data.

A first-pass entity, before any relationships are added
CREATE TABLE passengers (
    id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    age INTEGER
);

The Test: Entity or Attribute?

Ask: does this thing have its own identity and its own attributes, or does it only ever exist as a property of something else? A cabin number can stay an attribute of a booking if the system never has to ask about cabins on their own. The moment the requirements say "each cabin has a deck, a class and a maximum occupancy", Cabin has earned its own table. It now has facts of its own that do not belong to any one passenger.

Relationships: The Verbs Between Entities

Relationships come from the verbs that connect the nouns: "a passenger is assigned to a cabin". Before a relationship can become a foreign key (a column that points at a row in another table), you need to know its cardinality, meaning how many of one side can relate to how many of the other.

  • -One-to-one: each passenger has at most one passport record, and each passport record belongs to one passenger.
  • -One-to-many: one cabin can sleep up to four passengers, but each passenger sleeps in one cabin.
  • -Many-to-many: a passenger can attend many dining sittings, and a sitting holds many passengers.

Each shape needs a different table structure, and the next two chapters show how. An ER diagram (entity-relationship diagram) is simply the sketch of these boxes and lines.

Sketch the entity list and a rough set of relationships before writing any CREATE TABLE. It is the same discipline as drawing a class diagram before coding. Jumping straight to DDL is where schema designs go wrong early and expensively.

Interview Signal is part of Pro

See a real weak answer next to a real strong one for this exact topic.

Quiz is part of Pro

Test what you just read with a short quiz, and bank the XP.

Ready to Build This?

Design The Passenger Manifest and Cabin Assignments in the LLD Lab's Titanic act.

Discussion0

Join the Discussion

Sign in to leave comments, reply to others, or like insights.

Sign In to ScaleDojo

No comments yet. Be the first to start the thread!