| Lesson 2 | Entities and their Attributes |
| Objective | Describe how entities map to relational tables. |
Entities and Attributes: From Conceptual Model to Relational Tables
In a simple relational mapping, an entity type becomes a table, its attributes become columns, and each entity instance is represented by a row. An identifier distinguishes one instance from another and provides a candidate for the table's primary key. This mapping turns a description of the business into a structure that can store its data consistently.
For example, PERSON describes the kind of thing being modeled. Alex Morgan is one person, not a separate table. Name, BirthDate, and City describe people; Alex Morgan, 1992-04-18, and Boston are particular values for those attributes. Keeping these levels distinct is the starting point for a useful conceptual model.
The objective of this lesson is to explain that mapping, not to select a database product. Start with the information the business needs, establish what one occurrence means, and then translate the model into tables. SQL comes after the meaning of the data is clear.
Entity Type, Instance, Attribute, and Value
The word entity is sometimes used for an entity type and sometimes for an individual occurrence. In design discussions, state which meaning you intend. An entity type defines a category with common properties; an entity instance is one distinguishable occurrence in that category. The instances currently represented form an entity set.
| Term | Meaning | Example |
|---|---|---|
| Entity type | A category of things the business records | PERSON |
| Entity instance | One occurrence of that category | The person assigned PersonID 101 |
| Attribute | A named property of the entity type | BirthDate |
| Data value | The recorded value of a property for one instance | 1992-04-18 |
| Identifier | Attribute or attributes that distinguish instances | PersonID |
An identifier is itself an attribute, or a combination of attributes. It is not a separate category of information that sits outside the entity. PersonID identifies a person within this model, while Name describes that person. Both appear as columns when the entity is mapped to a table.
A data value [1] belongs to a particular row-and-column intersection in the table. Boston is the City value for PersonID 101; it is neither an entity type nor the name of an attribute. Many people can have the same City value without being the same person.
Read the Diagram from Business Meaning to Stored Data
The left panel describes Alex Morgan using familiar business facts: a name, a birth date, and a city. The middle panel abstracts those particular values into properties shared by the entity type. Instead of listing Alex's date, it lists the attribute Birth date. The identifier is shown conceptually without committing to an SQL data type or generation mechanism.
The right panel shows the relational representation. PersonID 101 identifies Alex Morgan, born 1992-04-18, with City Boston. PersonID 102 identifies Sam Rivera, born 1988-09-03, with City Denver. PersonID 103 identifies Jordan Lee, born 2000-01-25, with City Seattle. The highlighted row connects Alex's real-world example to its stored representation.
These are fictional teaching records. The table contains three rows because three people are recorded, not because PERSON has three attributes. Adding another person adds a row. Adding a genuinely new property, such as a preferred contact method, may require a model change. Confusing those two operations leads to tables named after individual people or columns named Person1, Person2, and Person3.
Separate Conceptual, Logical, and Physical Design
Conceptual design establishes business meaning. Identify the entities, important attributes, relationships, and rules that stakeholders recognize. Ask what PERSON means in this application: every person, registered customers, employees, or participants in a specific program? An entity name without a definition can hide disagreements about scope.
Logical relational design translates those concepts into relations, attributes, keys, and dependencies. Decide how the selected entity types and relationships map to tables, how repeating facts are separated, and how identifiers connect related rows. Normalization helps check that the resulting structure does not unnecessarily repeat facts or introduce avoidable update anomalies.
Physical design and implementation select DBMS-specific types, SQL declarations, indexes, and storage choices. For example, the domain of a birth date can be described conceptually, while the precise DATE declaration and validation mechanism are implementation choices. Indexes support access paths; they do not define what a person is.
These activities inform each other rather than forming a one-way handoff. A difficult implementation can reveal an unclear business rule, and a new requirement can change the model. The distinction is useful because it prevents a convenient spreadsheet layout or a database tool's default settings from silently becoming the business definition.
Choose Entities from Requirements
An entity type represents something about which the application needs to remember facts. It can describe a tangible object, such as a product, or an intangible occurrence, such as a reservation or inspection. A useful candidate has distinguishable instances and information that the business needs to retain.
Consider the requirement, “Record each person who registers for a training course.” Person and Course are likely candidates. Registration also deserves attention: it connects a person to a course and may have its own registration date, status, and completion result. Simply turning every noun into a table would miss that the association itself carries information.
Conversely, a report title does not automatically identify an entity. “Medical history” may summarize encounters, diagnoses, and procedures. “Inventory” may summarize products and stock at locations. But an inventory count can be an identifiable event with a date and responsible employee. Decide from the facts and rules, not from a blanket rule that collection names can never represent entities.
Write a short definition for each candidate and test it with stakeholders. For PERSON, specify whether two registrations by the same individual refer to one person or two accounts. A Person entity and a UserAccount entity can have different lifecycles and identifiers. That decision affects the schema more than the choice between singular and plural table names.
Select Attributes at the Right Level of Detail
Attributes should describe the entity or association to which they belong. BirthDate describes a person; RegistrationDate describes a registration. If one person registers for several courses, a single RegistrationDate column in PERSON would not say which registration it describes. This is a modeling problem, not a problem that a larger text column can solve.
A simple attribute is treated as one value for the model's purposes. The diagram uses Name as one displayed name. A different application may require structured name components. Do not split names merely because spaces appear, or assume that every person's name follows the same first-name/last-name convention.
A composite attribute groups meaningful components, such as an address containing street, locality, postal code, and country. Separate components when they support real requirements, such as postal validation or locality-based searching. Define whether the address is current, historical, residential, or a contact address; otherwise different users may populate the same columns with different meanings.
A multivalued attribute allows several values for one instance. A person may have several telephone numbers. Numbered columns such as Phone1 and Phone2 impose a fixed limit, while a comma-separated string hides individual occurrences. A related PersonPhone table can store one number per row and its purpose. See how to resolve multivalued attributes for a worked example.
A derived attribute can be computed from other facts. Current age depends on BirthDate and the date on which age is evaluated. Storing BirthDate avoids maintaining a changing Age value. If the requirement is “age declared at enrollment,” however, that is a historical fact with different meaning and may need recording separately.
Also define what a missing value means. An unknown birth date is different from a fabricated default date. NULL may represent missing information in an SQL implementation, but it does not by itself distinguish unknown, not supplied, and not applicable. Record additional status only when the business needs to tell those conditions apart.
Map the Model into Tables and Keys
- Establish the row meaning. In PERSON, each row represents one recorded person. Write that rule before choosing a primary key. Historical versions, accounts, and memberships are different possible row meanings.
- Map the attributes. PersonID, Name, BirthDate, and City become columns in this example. All rows follow the same column structure, even though their values differ.
- Select a primary key. Choose an identifier that distinguishes every row. PersonID serves that role here. Names and cities do not, because their values can repeat.
- Map relationships. Use foreign keys or associative tables to represent connections to other entities. Put facts about an association on that association, not arbitrarily on one participant.
- Check dependencies and constraints. Confirm that attributes describe the intended row and that uniqueness, required values, and permitted relationships reflect agreed rules.
A candidate key is a minimal set of attributes that uniquely identifies an instance. Minimal means that removing any component loses that identifying property, not necessarily that the identifier has the fewest characters. One candidate is selected as the primary key; other genuine candidate keys can be enforced as alternate keys.
A surrogate PersonID is assigned for identification within the system. It avoids using a changeable descriptive value as the reference in other tables. It does not automatically prove that two rows represent different human beings. Duplicate-person detection and record merging require additional business rules and processes.
The entity-to-table rule is a useful starting pattern, not a guarantee of exactly one table for every box in every model. Multivalued attributes, associative entities, dependent entities, and subtype designs can require additional mapping decisions. Similarly, one relationship can require its own table. Judge the mapping by whether it preserves the intended facts and rules.
Understand How Relationships Affect the Mapping
For a one-to-many relationship, the foreign key normally belongs on the many side. If each registration belongs to one person, Registration.PersonID references PERSON.PersonID. Several registrations may contain the same PersonID, while each PERSON row has a unique PersonID.
Required participation needs careful wording. A NOT NULL foreign key can require every registration to identify a person. It cannot require every person to have a registration. Enforcing a minimum number of related rows for every parent is a separate requirement, often involving application workflow or additional database logic.
For a many-to-many relationship, introduce an associative table. People can register for multiple courses, and a course can have multiple people. Registration links the two. Whether PersonID plus CourseID uniquely identifies a registration depends on whether repeat enrollments or separate course offerings are allowed. Use the actual business occurrence, not a memorized key formula.
For a one-to-one relationship, a foreign key with a uniqueness constraint can enforce at most one referencing row for each referenced key. It does not, by itself, require both sides to exist. Decide whether the concepts should be separate, which side can be optional, and how creation and deletion are coordinated.
A Small SQL Representation of PERSON
The following illustrative PostgreSQL 18 example represents the table in the diagram. It supplies identifiers explicitly to preserve the displayed values. A production application may use generated identifiers, but generation is a separate implementation choice from deciding that PersonID is the primary key.
CREATE TABLE Person (
PersonID INTEGER PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
BirthDate DATE,
City VARCHAR(100)
);
INSERT INTO Person (PersonID, Name, BirthDate, City) VALUES
(101, 'Alex Morgan', DATE '1992-04-18', 'Boston'),
(102, 'Sam Rivera', DATE '1988-09-03', 'Denver'),
(103, 'Jordan Lee', DATE '2000-01-25', 'Seattle');
SELECT PersonID, Name, BirthDate, City
FROM Person
ORDER BY PersonID;
The primary key identifies the rows, and the DATE column records dates rather than formatted date strings. The example allows missing BirthDate and City values; it requires Name to be non-NULL. These are stated example policies, not universal rules for every person database. NOT NULL alone does not reject an empty or whitespace-only name.
The length limit of 100 characters is illustrative and should be justified by requirements. City is a simple descriptive value here, not a complete address or a globally unique place identifier. An application that manages places independently may need a Location entity and an appropriate foreign key instead.
The SELECT is expected to display the three supplied records in PersonID order. A table has no guaranteed display order without an ORDER BY clause in the query. These SQL statements are examples for a practice environment, not a captured execution result.
Document the Model so Others Can Review It
An entity-relationship diagram communicates structure, while a data dictionary records details that would clutter the diagram. For each entity, document its definition and identifier. For each attribute, record its meaning, domain, whether it is required, whether it can repeat, and whether it is stored or derived.
For BirthDate, the domain is a calendar date and the meaning is the person's date of birth, not the date the record was entered. For City, define which city is intended. For PersonID, specify its scope of uniqueness and who assigns it. These descriptions help analysts detect inconsistent assumptions before they become inconsistent data.
Use one diagramming notation consistently and explain unfamiliar symbols. Crow's Foot notation is useful for relationships and cardinalities; other notations are also valid. The supplied illustration is a teaching map between modeling concepts and table elements, rather than a complete ERD of a multi-entity system.
Choose consistent names, but do not mistake a naming convention for a relational rule. Singular PERSON and plural Persons can both work. Distinguish similar concepts clearly and avoid names that mean different things to different departments. A stakeholder should be able to read each relationship as a business sentence.
Validate the Design with Example Questions
Test the model with realistic cases. Can two people share a name and city? They should remain separate instances with different identifiers. Can a person move? Decide whether City records only the current value or whether address history is required. Can a birth date be unknown? The model and entry workflow should agree on that case.
Next, test changes in quantity. Adding a fourth person should add a row without changing the table definition. Adding a second telephone number should not require a new numbered column. Registering a person for another course should add an association without duplicating the person's descriptive data.
Finally, ask which facts the schema can enforce and which still require a process. A primary key prevents duplicate key values; it does not establish that an entered name is accurate. A foreign key prevents an invalid reference; it does not confirm that the association is correct in the real world. Both structural integrity and reliable data-entry practices matter.
Continue to Identifiers and Data Values
The central mapping is entity type to table, simple attribute to column, and instance to row, with an identifier selected to distinguish rows. Relationships and more complex attributes add further structures. Keep the business meaning explicit as you move from the conceptual model to its relational implementation.
The next lesson explains how identifiers help store entity data values. You can also review attribute domains and entity and attribute constraints as you refine the model.
References and Further Reading
- Microsoft Learn: fundamental relational data concepts
- PostgreSQL 18: primary keys, foreign keys, and other constraints
- PostgreSQL 18: CREATE TABLE
Entities and Attributes: Exercise
Apply these distinctions in the Entities and Attributes Exercise. Identify the business objects and the attributes that belong to each before proposing tables.