Identifying Entities and Attributes: The Building Blocks of Database Design

What Counts as an Entity

An Entity is a distinct real-world object, concept, or event that a database needs to store information about — a customer, a product, an order, or an appointment are all typical entities. Identifying entities is the first step of the conceptual design phase discussed earlier in this series, and it requires asking a simple but important question: what are the "things" this application needs to keep track of?

Example entities for an online bookstore:
- Book
- Author
- Customer
- Order
- Publisher

Each entity eventually becomes a table, but at this early stage the goal is simply to identify the distinct concepts involved, without yet worrying about columns, data types, or SQL syntax.

Attributes: Describing an Entity's Details

An Attribute is a specific piece of information that describes an entity. A Book entity, for example, might have attributes like title, publication year, and price — each of these eventually becomes a column in the corresponding table.

Book entity and its attributes:
- title
- isbn
- publication_year
- price
- page_count

A common mistake at this stage is including too many or too few attributes: omitting an attribute the application will genuinely need forces a difficult schema change later, discussed earlier in this series regarding the cost of late design fixes, while including irrelevant attributes clutters the design and can create genuine confusion about what each piece of data actually represents.

Types of Attributes

Attributes are not all the same kind of thing, and recognizing these distinctions helps identify design problems early.

Simple Versus Composite Attributes

A Simple Attribute cannot be meaningfully broken down further, such as a page count. A Composite Attribute is made up of smaller, meaningful parts, such as an address, which naturally decomposes into street, city, and postal code.

Composite attribute example:
address → street, city, state, postal_code

Whether to store these as one combined field or
several separate columns depends on whether the
application needs to query or sort by the individual
parts — if city-based searches are needed, the
address should be split into separate columns

Single-Valued Versus Multi-Valued Attributes

A Single-Valued Attribute holds exactly one value per entity, such as a book's ISBN. A Multi-Valued Attribute can hold several values for a single entity, such as a book potentially having multiple authors. Multi-valued attributes cannot be stored directly as a single column in a relational table without violating good design principles, and instead require a separate related table — a technique explored in depth in the article on relationships later in this series.

Stored Versus Derived Attributes

A Stored Attribute holds a value that must be explicitly saved, such as a birth date. A Derived Attribute can be calculated from other stored attributes whenever needed, such as age, which can always be computed from a stored birth date.

Derived attribute example:
age = current_date - birth_date

Storing "age" directly as a column would create
redundant data that becomes stale the moment time
passes, since it must be manually updated to stay
correct — storing only birth_date and computing age
on demand avoids this inconsistency risk entirely

As a general design principle, derived attributes should usually not be stored directly, since doing so introduces a redundant value that can silently become inconsistent with the data it was derived from, unless performance requirements specifically justify the trade-off.

Choosing a Key: Uniquely Identifying Each Entity Instance

Every entity needs a way to uniquely distinguish one instance from another — this becomes the table's primary key, introduced earlier in this series. Choosing an appropriate key is one of the more consequential decisions in entity design.

Natural Keys Versus Surrogate Keys

A Natural Key is an attribute that already exists in the real-world data and happens to be unique, such as an ISBN for a book or a national ID number for a person. A Surrogate Key is an artificial identifier, typically an auto-incrementing number, created solely for the purpose of uniquely identifying rows, with no real-world meaning of its own.

Natural key example:
isbn as the primary key for a books table

Surrogate key example:
book_id (an auto-incrementing integer)
as the primary key, with isbn stored as
a separate, non-key attribute

Surrogate keys are generally preferred in practice, even when a natural key exists, because natural keys can occasionally change (a person's national ID might be reissued, or a product's code might be revised), and any change to a primary key value ripples through every foreign key referencing it elsewhere in the database — a surrogate key, having no external meaning, never needs to change for business reasons.

Why Careful Entity and Attribute Identification Matters

The entities and attributes identified during this early conceptual phase directly become the tables and columns of the eventual database schema. A mistake here — merging two distinct entities into one, missing an important attribute, or choosing a poor key — tends to cascade through every later design decision, making this careful, deliberate identification work one of the highest-leverage steps in the entire database design process discussed throughout this series.