ERDs Should be Compulsory at School

It was 2000 when I first learned about ERD's. I was a late starter. Like a major historic event, I remember the exact class and how excited I was to learn a new way of thinking. The amazing thing about Entity Relationship Diagrams is how they describe a business in a visual way. Within seconds, you can look at an ERD and understand what the business is and how it works. You can read about ERD's on Wikipedia but it's easier to understand if shown an example.

Why Do I Need to Know This?

Why do you need to know this I hear you ask? For business analysts and software developers, ERDs are tools of the trade so if you need to communicate with them in their own language, this is the way to do it. ERDs help developers create code objects and database structures. They are also used to illustrate the design of systems after they have been built.

The Conceptual ERD

ERDs focus on the relationships between the working entities of any business. For example, a medical center might need a new patient management system. The first thing is to identify the key entities and the relationships between them. A conceptual ERD might visualize them like this:

The translation is, "A Doctor is scheduled for an Appointment. A Patient schedules an Appointment. The Appointment creates an Invoice which is paid by a Payment that the Patient makes." Each relationship, and its direction, is defined by the word(s) in the diamond. 

The Logical ERD

Once the Conceptual ERD has been completed, it's easy to add the attributes to the design and make it into a Logical ERD:

You can see that "crow's feet" are used to specify a "many" relationship and vertical bars represent a single instance of an object e.g. the Patient to Payment relationship reads, "One Patient makes zero to many Payments, or alternatively, a single Payment is made by one Patient. This is termed the cardinality of the relationship.

In the diagram, I've also snuck in the PK attribute (Primary Keys) that are unique IDs that database tables use to reference each other. These details are usually added to the third ERD iteration, called the Physical ERD, which resembles the database structure (schema) itself.

The Golden Rule

When planning a software project, ERDs are one of the first things to do - well before choosing a technology or architecture. As a software company, we are sometimes tempted to say, "Oh, that entity fits into a solution we know about" but by doing this we start trying to construct our ERD to fit a specific/predetermined data model and not reflect the true needs of the client.

Conclusion

With ERD's mastered you can speak to analysts and developers in their own language. If you are creating a new information system, this will save time and money. I'd expect any business analyst returning from a reconnaissance visit to have a simple ERD available as it represents the starting point of further investigation.

I used Microsoft Visio to draw these ERDs but the beauty of these diagrams is the ability to prototype systems on a scrap of paper or a whiteboard. If you want to learn more about ERDs I recommend this article by Ben Oliver.