Entities and Attributes

In one line

An entity is a thing the business needs to remember (customer, order, room); an attribute is a fact about it. Strong entities have their own identity, weak entities only exist inside a parent. Attributes can be simple, composite, derived, or multivalued, and each kind maps to tables differently.

0/4 · 0%

Think of it like this

A school register. Each student is an entity with attributes (name, date of birth). A student's list of phone numbers is multivalued, their age is derived from the date of birth, and their address (street, city, PIN) is composite.

Key ideas

01
Find entities by listing the nouns in the requirements that have identity and a lifecycle: "customers place orders containing products" gives customer, order, product, and order line.
02
Strong vs weak: a room in a hotel can be identified as (hotel_id, room_number); it doesn't exist without its hotel, so it's weak and its key includes the parent's key. An order_item is weak relative to its order.
03
Simple attributes map to one column. Composite attributes (address) become several columns (street, city, postal_code), or a separate table when shared or when a customer has many addresses.
04
Derived attributes (age, order total) are usually computed, not stored. Store them only deliberately (denormalization, Phase 3.4), with a plan to keep them correct.
05
Multivalued attributes (phone numbers, tags) get a child table (customer_phone(customer_id, phone, type)), not a comma-separated column. Arrays or JSONB are acceptable when you never query or constrain individual values relationally.

Code & diagrams

attributes.sqlsql

CREATE TABLE customer (
  id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  full_name     text NOT NULL,
  date_of_birth date,                     -- age is derived, not stored
  street        text,                     -- composite attribute: address
  city          text,
  postal_code   text
);

-- multivalued attribute -> child table
CREATE TABLE customer_phone (
  customer_id bigint NOT NULL REFERENCES customer(id) ON DELETE CASCADE,
  phone       text   NOT NULL,
  kind        text   NOT NULL CHECK (kind IN ('mobile','home','work')),
  PRIMARY KEY (customer_id, phone)
);

-- weak entity: a room only exists inside a hotel
CREATE TABLE room (
  hotel_id    bigint NOT NULL REFERENCES hotel(id),
  room_number text   NOT NULL,
  floor       int,
  PRIMARY KEY (hotel_id, room_number)
);

Interview problem

The problem

Extract entities from a hospital brief

"Patients book appointments with doctors in departments. Each appointment can produce prescriptions listing several medicines with dosages. Patients have several emergency contacts." List entities and attributes, and mark weak entities, multivalued and derived attributes.

When it breaks

Comma-separated values in a column

What you see

tags = 'red,sale,summer' can't be indexed per value, can't be constrained, and "find products tagged sale" needs LIKE '%sale%', which also matches wholesale.

Fix & prevent

Use a child table (product_tag) or, if values are never joined, a text[] column with a GIN index.

Explain it without notes

What makes an entity weak, and how does that show up in its primary key?

Practice

Model a customer with multiple shipping addresses, one marked default. Enforce at most one default per customer.

Trade-offs

↔
Child tables are more joins but keep values queryable and constrained; arrays/JSONB are simpler but weaker.

Done when you can

I can turn a requirements paragraph into entities and correctly typed attributes.

Command Palette