2025-04-22
Introduction to SQL - Entity Relationships
We’re so close to finalize the Introduction to SQL articles, but before that we have another critical topic to learn: Entity Relationships
So far, we learned that Entity is synonyms of Tables. In database administration, only those things about which data will be captured or stored are considered entities. So if you aren't going to capture data about something, there's no point in creating an entity in a database.
Once you decide that it is worth it to create an entity or table, you can end up with multiple tables as we saw here. And then you will need to join those tables to have a big picture about your data, your queries and your business logic.
When to draw ER Diagrams?
So, when do we draw ERDs? While ER models are mostly developed for designing relational databases in terms of concept visualization and in terms of physical database design, there are still other situations when ER diagrams can help. Here are some typical use cases.
- - Database design - Depending on the scale of change, it can be risky to alter a database structure directly in a DBMS. To avoid ruining the data in a production database, it is important to plan out the changes carefully. ERD is a tool that helps. By drawing ER diagrams to visualize database design ideas, you have a chance to identify the mistakes and design flaws, and to make corrections before executing the changes in the database.
- - Database debugging - To debug database issues can be challenging, especially when the database contains many tables, which require writing complex SQL in getting the information you need. By visualizing a database schema with an ERD, you have a full picture of the entire database schema. You can easily locate entities, view their attributes and identify the relationships they have with others. All these allow you to analyze an existing database and to reveal database problems easier.
- - Database creation and patching - Visual Paradigm, an ERD tool, supports a database generation tool that can automate the database creation and patching process by means of ER diagrams. So, with this ER Diagram tool, your ER design is no longer just a static diagram but a mirror that reflects truly the physical database structure.
- - Aid in requirements gathering - Determine the requirements of an information system by drawing a conceptual ERD that depicts the high-level business objects of the system. Such an initial model can also be evolved into a physical database model that aids the creation of a relational database, or aids in the creation of process maps and data flow modes.
In the previous join tables blog post we learned about Inner Join, Left and Right Joins. But we need another piece on our cake: Entity Relationships
Entity Relationships
An Entity Relationship is a high-level conceptual data model diagram. Entity Relationship helps to systematically analyze data requirements to produce a well-designed database. Entity Relationships represent real-world entities and the relationships between them. Creating an Entity Relationship is considered as a best practice before implementing your database.
Let's re-use the Database Design used in this blog post
As you can see we have a Customer who can have many Orders. So, as you can imagine, each order belongs to a Customer. At the same time, the Subscription (to a given magazine, e.g. Time Magazine) has many Orders, and each Order belongs to a Subscription. The technical name for this in Database management is: Cardinality
Cardinality
Defines the numerical attributes of the relationship between two entities or entity sets.
Different types of cardinal relationships are:
- One-to-One Relationships
- One-to-Many Relationships
- May to One Relationships
- Many-to-Many Relationships
1.One-to-one:
One entity from entity set X can be associated with at most one entity of entity set Y and vice versa.
Example: One student can register for numerous courses. However, all those courses have a single line back to that one student.
2.One-to-many:
One entity from entity set X can be associated with multiple entities of entity set Y, but an entity from entity set Y can be associated with at least one entity.
For example, one class consists of multiple students.
3. Many to One
More than one entity from entity set X can be associated with at most one entity of entity set Y. However, an entity from entity set Y may or may not be associated with more than one entity from entity set X.
For example, many students belong to the same class.
4. Many to Many:
One entity from X can be associated with more than one entity from Y and vice versa.
For example, Students as a group are associated with multiple faculty members, and faculty members can be associated with multiple students.
How to draw an ER diagram?
If you find it difficult to get started with drawing an ER diagram, don't worry. In this section, we will give you some ERD tips. Try to follow the steps below to understand how to draw an ER diagram effectively.
- - Make sure you are clear about the purpose of drawing the ERD. Are you trying to present an overall system architecture that involves the definition of business objects? Or are you developing an ER model ready for database creation? You must be clear about the purpose to develop an ER diagram at the right level of detail (Read the section Conceptual, Logical and Physical Data Models for more details)
- - Make sure you are clear about the scope to model. Knowing the modeling scope prevents you from including redundant entities and relationships in your design.
- - Draw the major entities involved in the scope.
- - Define the properties of entities by adding columns.
- - Review the ERD carefully and check if the entities and columns are enough to store the data of the system. If not, consider adding additional entities and columns. Usually, you can identify some transactional, operational and event entities in this step.
- - Consider the relationships between all entities and relate them with proper cardinality (e.g A one-to-many between entity Customer and Order). Don't worry if there are orphan entities. Although it's not common, it's legit.
- - Apply the technique of database normalization to restructure the entities in a way that can reduce data redundancy and improve data integrity. For example, the details of the manufacturer might be stored under the Product entity initially. During the process of normalization, you may find that the detail keeps repeating records over records, then you can split it as a separate entity Manufacturer, and with a foreign key that links between Product and Manufacturer.
The purpose of this blog post was to give you an introduction about Entity Relationship and how to diagram it. With all of the cumulative knowledge we have about SQL we’ll finish this introduction series with database structures. So, I’ll see you in the next blog post
I hope you learned a lot.
Thanks for reading
Daniel Morales