Identifying Entities in Database Design

An entity is a distinct "thing" the business needs to store information about — something with its own identity that exists independently of other things. In a requirement statement, entities usually show up as nouns: customer, order, product, invoice, employee. Recognizing which nouns deserve to become their own table, and which are just attributes of another entity, is one of the first and most consequential decisions in database design.

Getting this wrong in either direction causes real damage. Turning every noun into a table produces a needlessly fragmented schema full of one-row lookup tables. Failing to split out a genuine entity — for example, storing "shipping address" as a single text column on the customer table instead of its own Address entity — makes it impossible to support customers with multiple addresses later without a disruptive rewrite.

Why it matters

The first design decision is what counts as a thing in its own right. Fold an address into the customer table and the day a customer needs a second address you are adding columns such as address2_city. Recognising a separate entity early keeps the model able to grow.

Entities vs. Attributes

The test for "is this an entity or just an attribute" is whether the thing has its own identity, its own attributes, and potentially its own relationships to other things. A customer's email address is an attribute — it has no meaning or lifecycle independent of the customer. A customer's shipping address, on the other hand, often deserves to be its own entity: it can be reused across orders, a customer can have several, and it has its own structure (street, city, postal code) worth naming as a unit.

• Entity signal: the thing can exist, be created, or be referenced independently ("this address," "this product," "this employee").
• Entity signal: multiple instances can belong to the same parent (a customer can have many addresses, many payment methods).
• Attribute signal: the value only ever describes one specific parent and has no independent meaning (a customer's date of birth, a product's color).
• Attribute signal: the value is a simple scalar with no internal structure worth breaking out (a boolean flag, a single price).

Strong Entities vs. Weak Entities

A strong entity has its own unique identifier and can exist independently — Customer, Product, Employee. A weak entity depends on another entity for its identity and typically cannot exist without it — an OrderLineItem has no meaning without its parent Order, and its uniqueness is often defined in terms of the parent (order_id + line_number). Recognizing weak entities early tells you that a foreign key to the parent should usually be part of the primary key, or at minimum a NOT NULL, non-optional column.

Naming and Attribute Inventory

Once an entity is identified, list every attribute it needs, and for each one note its data type, whether it is required, and whether it should be unique. This attribute inventory becomes the column list for the CREATE TABLE statement, and doing it deliberately — rather than adding columns reactively as features are built — keeps entities focused on a single, coherent concept instead of accumulating unrelated fields over time.

Example

-- Weak first pass: address flattened directly onto the customer
-- CREATE TABLE customers (
--     id BIGINT PRIMARY KEY,
--     full_name VARCHAR(150),
--     shipping_street VARCHAR(200),
--     shipping_city VARCHAR(100),
--     shipping_postal_code VARCHAR(20)
-- );
-- Problem: cannot support a second shipping address or a separate
-- billing address without adding more duplicate columns.

-- Better: Address recognized as its own entity
CREATE TABLE customers (
    id          BIGINT PRIMARY KEY,
    full_name   VARCHAR(150) NOT NULL,
    email       VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE addresses (
    id           BIGINT PRIMARY KEY,
    customer_id  BIGINT NOT NULL REFERENCES customers(id),
    address_type VARCHAR(20) NOT NULL CHECK (address_type IN ('billing', 'shipping')),
    street       VARCHAR(200) NOT NULL,
    city         VARCHAR(100) NOT NULL,
    postal_code  VARCHAR(20) NOT NULL
);

A customer can now have zero, one, or many address rows, each tagged as
billing or shipping, without any change to the customers table. Adding a
second shipping address is just inserting another row in addresses, not
a schema migration.

Exercise

A customer can have several addresses, so an address is an entity of its own and not a set of columns on the customer. Create an addresses table that belongs to a customer. The data is inserted for you. Then count Asha's addresses as total.

Expected output: total then 2

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL);

-- TODO: addresses (id, customer_id, city, kind) where customer_id refers to customers

INSERT INTO customers (id, name) VALUES (1, 'Asha'), (2, 'Ravi');
INSERT INTO addresses (id, customer_id, city, kind) VALUES
  (1, 1, 'Pune', 'home'),
  (2, 1, 'Mumbai', 'office'),
  (3, 2, 'Delhi', 'home');

-- TODO: count Asha's addresses as "total"

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL);

CREATE TABLE addresses (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  city TEXT NOT NULL,
  kind TEXT NOT NULL
);

INSERT INTO customers (id, name) VALUES (1, 'Asha'), (2, 'Ravi');
INSERT INTO addresses (id, customer_id, city, kind) VALUES
  (1, 1, 'Pune', 'home'),
  (2, 1, 'Mumbai', 'office'),
  (3, 2, 'Delhi', 'home');

SELECT COUNT(*) AS total
FROM addresses a
JOIN customers c ON c.id = a.customer_id
WHERE c.name = 'Asha';

Quick quiz

Interview questions