Missing the Big Picture?... Learn the Entity Relationship Diagram (ERD)

In today’s fast paced development where speed of the delivery matters the most, the product owners, solution architects or application developers are engulfed in turning big problems into small problems and providing solutions to them. In this process, they are missing the big picture, that is, they are missing the conceptual view of the proposed system / database design. Entity relationship diagram (ERD) helps us address this need aptly.

An Entity-Relation model is based on the notion of real-world entities and the relationship between them. It is a visual representation of different entities within a system and how they relate to each other.

The components of ERD are (1) Entities (2) Attributes (3) Relationship

 1. Entity An entity can be place, person, object, event or a concept, which stores data in the database. The characteristics of entities are must have an attribute, and a unique key. Every entity is made up of some 'attributes' which represent that entity. 

2. Attributes: These are the properties of an entity (Example: Color of car entity, Name of the student entity) 

3. Relationship: It explains different types of relationships that exists among entities. That is, it explains how different entities are related in the proposed system. These can be: 
  1. One-to-One Relationships
  2. One-to-Many Relationships
  3. Many to One Relationships
  4. Many-to-Many Relationships
Example :: Order Management System - In this post, we shall take order management system as an example and learn how to infer the conceptual view of the proposed system. In our example, our ERD has 8 entities. They are “Supplier”, “Products”, “Customers”, “Shippers”, “Category”, “Payment”, “Orders” and “OrderDetails”. 


 The entities “Supplier” and “Products” are tied with one to many relationship. That is, one supplier can supply multiple products. The primary key for the entity “Suppliers” is Supplier_ID. That is, we will have distinctly unique value for this attribute for each of the suppliers in the entity set "Supplier". The other attributes of the entity Supplier are listed in the entity Supplier. Similarly the entity set “Products” has Product_ID as the primary key. Thus, we will have distinctly unique value for this attribute for each of the products in the entity set "Products".

Going with this analogy, the entities “Customers” and “Orders” are tied with one to many relationship. That is, one "Customer" can place multiple "Orders". Similarly, one "Shipper" can ship multiple "Orders". One "Category" can be linked to multiple "Orders". One “Payment” instrument type can be linked to multiple “Orders”.

The entities “Products” and “Orders” are linked to the entity “OrderDetail” with one to many relationship. That is, Order Detail is a derived entity where multiple entries of “Order Detail” can be linked to one “Products” entity and one “Orders” entity.

Look at the beauty of the Entity Relationship Diagram (ERD). It is enabling us understand different entities in the system, their attributes and the way they connect each other.  Thus, it is giving conceptual view. This way, product owners, business analysts, designers and developers can get holistic view of the proposed solution. This way, we can enhance speed torqued with quality and connect.

 Entity Relationship Diagram (ERD) is a simple tool. But if we understand how to apply it, it certainly makes solution architect, product owner, and application developer distinctly distinct in their professions...

Subscribe to: Posts (Atom)