In database design, an entity is something you want to store information about. It’s a person, place, thing, event, or concept that matters to your application and has data associated with it that you need to track.
Entities are the building blocks of database design. Before you create tables, write queries, or think about indexes, you need to identify what entities exist in your domain and what information you need to store about them.
Entities vs Tables
In most relational databases, entities become tables. If “Customer” is an entity in your system, you’ll probably have a customers table. If “Product” is an entity, you’ll have a products table, and so on.
However, the relationship isn’t always one-to-one. During the design process, you work with entities at a conceptual level – what exists in the real world or your business domain. When you implement the database, those entities translate into physical tables, but the mapping can be more complex depending on your design decisions.
Sometimes one entity becomes multiple tables (like splitting customer data across customers and customer_addresses tables). Sometimes multiple related entities get combined into one table for practical reasons.
Types of Entities
Entities are commonly categorized based on their independence and role in the database structure:
- Strong entities can exist independently. A customer is a strong entity. Customers exist regardless of whether they’ve placed orders or not. Products, employees, and departments are typically strong entities.
- Weak entities depend on another entity for their existence. An order line item can’t exist without an order. A room can’t exist without a building. These entities are meaningfully identified through their relationship to a parent entity.
- Associative entities represent relationships between other entities. An enrollment entity connects students and courses. An assignment entity links employees to projects. These often become junction tables in your database design.
These three types are the most important distinctions when you’re actually designing and implementing databases, as they directly affect how you structure tables and relationships.
Other categorizations exist and can be useful for thinking about your domain:
- Tangible vs intangible entities. Tangible entities have physical existence (products, buildings, equipment), while intangible entities are conceptual (accounts, policies, services, events). This distinction can help during requirements gathering but doesn’t typically change how you implement them.
- Concrete vs abstract entities. Similar to tangible/intangible, concrete entities are specific things you can point to (an employee, a vehicle), while abstract entities are concepts (a department, a transaction type).
- Aggregate entities. These represent summaries or collections derived from other entities, like monthly sales summaries or inventory totals. In data warehousing, these often become materialized views or summary tables.
The specific categorization system you use matters less than clearly understanding what each entity represents and how it relates to others in your system.
Identifying Entities
When you’re designing a database, identifying entities is one of your first tasks. Look at your domain and ask yourself what things you need to store information about.
For an e-commerce system, obvious entities include customers, products, orders, and payments. Less obvious ones might include shopping carts, product reviews, wish lists, or promotional campaigns.
For a hospital system, you’d have patients, doctors, appointments, prescriptions, and medical records. You might also need entities for rooms, departments, insurance providers, and medications.
The main thing is to focus on things that have multiple attributes you need to track and that play a meaningful role in your application. Not every noun in your requirements document becomes an entity. Some are just attributes of other entities.
Entity Attributes
Once you’ve identified an entity, you need to determine its attributes. These are the specific pieces of information you want to store about it.
A customer entity might have attributes like customer_id, first_name, last_name, email, phone, and registration_date. A product entity might include product_id, name, description, price, category, and stock_quantity.
Attributes should be atomic. Each attribute should store one piece of information, not multiple values concatenated together. A phone number is one attribute. Multiple phone numbers should be handled with a separate entity or table, not by cramming them all into one field.
Some attributes are identifiers that uniquely distinguish one instance of the entity from another. These become primary keys in your tables. Other attributes are descriptive, providing information about the entity.
Entity Instances
An entity is the general concept, whereas an instance is a specific occurrence of that entity. “Customer” is an entity. John Smith with customer_id 12345 is an instance. “Product” is an entity. A specific iPhone 15 Pro with product_id 67890 is an instance.
When you create a table for an entity, each row represents one instance. The customers table contains all customer instances. The products table contains all product instances.
Understanding this distinction helps clarify database design discussions. When you talk about entities, you’re talking about the structure and concept. When you talk about instances, you’re talking about the actual data.
Relationships Between Entities
Entities don’t exist in isolation. They relate to each other in meaningful ways. Customers place orders. Orders contain products. Employees work in departments.
These relationships are just as important as the entities themselves. They determine how you structure your database, what foreign keys you need, and whether you need junction tables for many-to-many relationships.
When identifying entities, you should also identify how they relate. Does each customer have one shipping address or many? Can a product belong to multiple categories? Can an order be placed by multiple customers? These relationship cardinality questions shape your database design.
Entity-Relationship Diagrams
Database designers use entity-relationship diagrams (ERDs) to visualize entities and their relationships. These diagrams show entities as boxes or rectangles, with lines connecting them to show relationships. Attributes are often listed inside the entity boxes or shown as ovals connected to entities.
Here’s an example of an ERD:
ERDs help communicate database designs to stakeholders, document existing systems, and think through design decisions before writing any SQL. They’re a standard tool in database design, though the specific notation varies (Chen notation, crow’s foot notation, UML, and others).
Creating an ERD forces you to think clearly about what entities exist, how they relate, and what attributes matter. It’s easier to catch design problems in a diagram than after you’ve built tables and loaded data.
Entities in Different Database Models
The concept of entities exists across different database paradigms, though the implementation varies.
In relational databases, entities become tables with rows and columns. In document databases like MongoDB, entities might be represented as document collections with flexible schemas. In graph databases, entities become nodes with properties and edges representing relationships.
The fundamental idea remains the same, which is to structure the things you need to store information about and structuring your data around them. The implementation details change based on the database technology you’re using.
From Entities to Implementation
The process of going from entities to a working database involves several steps:
- First, identify entities through domain analysis and requirements gathering. What exists in your business domain that needs to be tracked?
- Second, determine attributes for each entity. What information do you need to store about each one?
- Third, identify relationships between entities and their cardinality. How do entities connect to each other?
- Fourth, normalize your design to reduce redundancy and maintain data integrity, or deliberately denormalize for performance when appropriate.
- Finally, translate entities and relationships into physical database tables with appropriate columns, data types, constraints, and indexes.
Entity Design Considerations
When defining entities, think about granularity. Are you tracking individual items or batches? Individual employees or teams? The level of detail you need affects how you model entities.
Consider lifecycle and state. Do entities go through stages or statuses? An order might progress from pending to paid to shipped to delivered. How you track state affects whether you need additional entities or just attributes.
Think about historical tracking. Do you need to know what an entity looked like in the past, or just its current state? This affects whether you need audit tables or slowly changing dimension approaches.
Understanding entities deeply is the foundation of good database design. Get this right, and the rest of your database structure follows logically. Get it wrong, and you’ll face constant workarounds and eventual redesigns.