Discovering Entities and Entity Types

Learning outcomes

Chapter 1 taught you to begin with requirements instead of tables. The next step is to discover the things the system must remember. These things are represented as entities. Entity discovery sounds simple—look for nouns—but reliable modeling requires more judgment. A noun may be an entity, an attribute, a role, an event, a value, or merely wording in a user interface.

Entity, entity type, and entity instance

An entity is a distinguishable thing about which the system needs to store facts. An entity type describes a category of similar entities. An entity instance is one member of that type.

The model describes the type; the database stores many instances. This distinction matters because design rules are usually stated at the type level: “Every WorkOrder belongs to exactly one Asset.”

A table is not automatically an entity type

Relational implementation often gives one table per entity type, but the correspondence is not guaranteed. A table can represent an associative entity, a history structure, a subtype, an aggregate, a queue, an imported staging dataset, or a database-specific implementation choice. Conversely, one conceptual entity type can sometimes be mapped across several physical tables.

Four tests for an entity candidate

When a noun appears in requirements, ask four questions. No single test is absolute, but together they provide a strong decision framework.

Consider Technician. We distinguish technicians, store their names and perhaps skills, assign them to work orders, and track their status over time. It is an obvious entity candidate. Consider repair priority. If priority is only one controlled value such as low/normal/high, it may simply be an attribute. If priorities have organization-specific names, response targets, escalation rules, colors, and effective dates, Priority may become a reference entity.

Noun extraction is a starting technique

Take this requirement:

Candidate nouns include Customer, Asset, Dispatcher, WorkOrder, Technician, ProblemDescription, Status, Priority, and Part. But after evaluation:

• Customer, Asset, WorkOrder, Technician, Part are strong entity candidates.
• Dispatcher might be an Employee playing a role rather than a separate entity type.
• ProblemDescription is probably an attribute of WorkOrder unless descriptions become independently versioned or structured.
• Status may be a controlled attribute or reference entity depending on its business complexity.
• Priority may be an attribute initially, but requirements may justify a reference entity.

Entity or attribute?

The entity-versus-attribute decision is contextual. An Address illustrates the problem. In a small application, a customer may have columns such as street, city, and postal_code. In a logistics system, addresses may have identity, geocoding status, delivery zones, validation metadata, effective dates, and shared usage. Then Address behaves more like an entity.

Entity or role?

A role describes how an entity participates in a relationship or process. “Dispatcher,” “requester,” “approver,” and “primary technician” may all be roles played by a Person or Employee.

If you create one entity type per role, you may accidentally duplicate the same person across multiple tables. Ask whether the business believes these are fundamentally different kinds of things or one kind of thing with several responsibilities.

Employee  employee_id  full_nameWorkOrder  opened_by_employee_id  -> EmployeeWorkOrderAssignment  technician_id          -> Employee  assignment_role        -> 'primary' | 'assistant' | 'specialist'

This design does not say every system should use a single Employee entity. It demonstrates the role test: the same employee may open orders and also perform repairs.

Entity or event?

Events describe something that happened: an assignment started, a status changed, a payment was authorized, an asset was transferred. Some events deserve entity-like representation because the event itself has identity, time, participants, and audit importance.

For WorkshopHub, WorkOrderAssignment is best understood as a relationship/event with its own facts: technician, work order, start time, end time, and role. Later in this chapter we will call this an associative entity.

Entity boundaries should match meaning

A common mistake is to create one giant entity for a business process. Another is to fragment every tiny concept into its own table. Good boundaries follow independent facts and rules.

Suppose WorkshopHub stores these values in a single WorkOrder record:

work_order_idcustomer_namecustomer_emailasset_serial_numberasset_manufacturertechnician_namepart_skupart_quantitystatus

This structure mixes facts about at least five independently identifiable things. Customer email can change without changing the work order. Parts exist before and after a particular repair. An asset can have many work orders. These independent lifecycles are evidence of separate entity types.

Strong first entity set for WorkshopHub

Based on the current requirements, a defensible conceptual set is:

• Customer — current owner/responsible party for assets.
• Asset — physical item being serviced.
• WorkOrder — repair/service case for one asset.
• Technician — worker who can be assigned to orders.
• Part — reusable catalog item that can be consumed.
• WorkOrderAssignment — time-bounded participation of a technician in an order.
• PartUsage — quantity and price facts connecting parts to a particular work order.

This list will change as requirements change. Entity discovery is not a one-time naming exercise; it is an evolving hypothesis about the domain.

Anti-patterns in entity discovery

Every noun becomes a table

This produces tiny tables for values such as color, phone number, and problem description even when no independent identity or lifecycle exists.

Every screen becomes an entity

“Customer Search,” “Work Order Dashboard,” and “Technician Queue” are user-interface concepts, not necessarily domain entities.

Every report column becomes an entity attribute

Reports often include derived values such as age, duration, current balance, or total cost. Those may be computed rather than stored.

Database implementation names replace domain language

Names such as tbl_hdr, master, or detail communicate storage mechanics but often hide what the data actually means.

Practice: classify the candidates

Summary and next lesson

Entities are independently meaningful things the system must distinguish and remember. Entity discovery starts with language but depends on identity, lifecycle, relationships, and business rules. The next lesson moves inside each entity and asks what attributes mean, which values are valid, which are optional, and which should be computed instead of stored.

References

• Peter P. Chen, “The Entity-Relationship Model—Toward a Unified View of Data,” 1976.
• Ramez Elmasri and Shamkant B. Navathe, Fundamentals of Database Systems.
• Thomas Connolly and Carolyn Begg, Database Systems.
• Martin Fowler, Analysis Patterns.