loading...
Cover image for Entity Relationship Diagrams explained by Sonic the Hedgehog

Entity Relationship Diagrams explained by Sonic the Hedgehog

helenanders26 profile image Helen Anderson Updated on ・3 min read

Data Analysis (14 Part Series)

1) Speed up your queries with indexes 2) Resources for beginner data analysts 3 ... 12 3) SQL concepts from A to Z 4) 6 ways to search with System Tables 5) Pearl Jam, Nirvana and Beautiful Data Visualisations 6) Entity Relationship Diagrams explained by Sonic the Hedgehog 7) How to use the VLOOKUP Excel function 8) SQL Joins without the Venn diagrams 9) SQL aggregate functions and Mariah Carey 10) Why I use SQL CTEs 11) How to customise SQL Server Management Studio (SSMS) 12) Have you VACUUMed your tables lately? 13) Why you need SQL window functions (part 1) 14) Why you need SQL window functions (part 2)

An Entity-Relationship Diagram (ERD) is a way to visualise relationships between tables and the common keys between them. On first glance, they may look like a spaghetti mess. This post hopes to demystify the language and how they fit together with the help of Sonic the Hedgehog.


Conceptual Data Model
Logical Data Model
Physical Data Model
What do the circles and arrows mean?
Who uses these diagrams?
Which systems can we model?
How do we start?
Any gotchas?



In this example, I'll be using data from my favourite Sega Master System game, Sonic The Hedgehog 1 and illustrating how the zones, acts and boss levels fit together.


Conceptual Data Model

Level one!

The Conceptual Data Model. This is a high-level overview of the different entities in the model and their relationships.

conceptual-data-model

In our example, we show that in Sonic the Hedgehog 1, zones can have many acts, these levels may have special stages, may have bosses, but there is only one boss per zone.

This model features entities, the rectangles. This is a physical thing, a fact or an event. The relationships between these entities are shown with diamonds.


Logical Data Model

Next up ... The Logical Data Model which adds more details to our model.

logical-data-model

In this diagram, we add the relationships between the tables using crows foot notation (more on that in a little bit) and add primary and foreign keys to show exactly how they are related.


Physical Data Model

And finally ... The Physical Data Model, that shows how the model will actually be implemented.

physical-data-model

This features the data types and names of the tables in our database. In this case, all the tables record attributes so are dimensions.


What do the circles and arrows mean?

This is called crows foot notation and shows the relationships by using a 'crows foot', line or circle on either side of the line connecting the entities.

ERD-crows-foot-notation


Which systems can we model?

Structured data in relational systems. We need to be able to see relationships in the form of tables, columns and keys.


How do we start?

Get out a pen and a big piece of paper. Make this your first step before you open the tools. Plus it will get you away from your screen for a little bit.

Lucidchart does a great job of making it easy to map out your diagram when you are ready to publish, but there are many tools out there to help you get started.


Any gotchas?

  • Don't join relationships to relationships
  • Use lots of colour ... wisely
  • Make entity and attribute names easy to understand
  • Know your audience - are you doing this for a user in management or to implement a new data model?
  • Don't overthink it if you don't have to. :)

Thank you Darren for the peer review!

Read more


This post first appeared on helenanderson.co.nz

Data Analysis (14 Part Series)

1) Speed up your queries with indexes 2) Resources for beginner data analysts 3 ... 12 3) SQL concepts from A to Z 4) 6 ways to search with System Tables 5) Pearl Jam, Nirvana and Beautiful Data Visualisations 6) Entity Relationship Diagrams explained by Sonic the Hedgehog 7) How to use the VLOOKUP Excel function 8) SQL Joins without the Venn diagrams 9) SQL aggregate functions and Mariah Carey 10) Why I use SQL CTEs 11) How to customise SQL Server Management Studio (SSMS) 12) Have you VACUUMed your tables lately? 13) Why you need SQL window functions (part 1) 14) Why you need SQL window functions (part 2)

Posted on by:

helenanders26 profile

Helen Anderson

@helenanders26

Making applications go faster at Raygun, AWS Data Hero, and tag moderator on Dev.to. Database concept you don’t understand? Let me know, I’ll write a post!

Discussion

markdown guide
 

Hi Helen, that's a nice post! I've clicked through this post because of Sonic but left with some newfound knowledge. 😁


I just want to check if I got what I'm reading right...

In short, some uses of Entity Relationship Diagrams(ERD) are:

  1. visualization of relationships between data
  2. serve as plans for implementing the database
  3. communicating these plans
  4. determine requirements
  5. debug system

And those uses translate naturally into "Why we use ERD".

While I can clearly see how ERD is used to serve the first 3 purposes, I have no more than a vague idea on using them for 4 and 5. If possible, do you mind elaborating on using ERD to determine requirements (and what sort of requirements) as well as using them to debug systems.


Thank you so much for this wonderful post! I will probably be reading through the rest of the BI series as well 😄

P.S. I have another question that's not really related to the content of the article. The part where you add the 5 circles to show the article being part of the "Business Intelligence Series" is so cool! I just wanted to know what is it called so I can also add it to my own posts.

 

Hi Leslie,

Thanks for the nice comments, I really enjoyed writing it.


Regarding requirements gathering, what I was trying to get across was that its part of the process we go through with stakeholders.

When building a new model there's lots of back and forth and clarification of what's required now, later and what can be delivered after go live. Ideally all the requirements are locked down early but in reality, this doesn't always happen.

At the first step, with the Conceptual Data Model, we can go back to the stakeholders to double check the requirements and do our best to avoid scope creep. Hopefully ;)


As far as debugging goes the ERD gives us a point of reference for how things 'should' work. If multiple team members are releasing changes to the model we can visually see how it fits together and where the dependencies are.

It's also helpful when doing any kind of database migration.


Excellent questions!

I really like stringing my posts together in series and use the instructions here to do so:

I'm also a fan of the Table of Contents at the top of my posts:

 

Thanks for replying Helen, your explanations are really clear!😄
Also, thank you for pointing me to these articles.

You're welcome, looking forward to reading more of your posts :D

 

I love what you describe and your words. The following is a code demo by js:

// Conceptual Data Model
const EntityRelationshipDiagrams = {
  Zones: class {
    // Logical Data Model
    constructor() {
      this.ZoneId = EntityRelationshipDiagrams.PhysicalDataModel.getId(105);
      this.voice = "truths";
    }
    // Physical Data Model
    speak() {
      return `${this.ZoneId} ${this.voice} as work`;
    }
  },
  Boss: class {
    // Logical Data Model
    constructor() {
      this.BossId = EntityRelationshipDiagrams.PhysicalDataModel.getId(0);
      this.voice = "barks";
    }
    // Physical Data Model
    speak() {
      return `${this.BossId} ${this.voice} in the meeting`;
    }
  },
  // external Physical Data Model
  PhysicalDataModel: {
    getId(num) {
      return num < 100
        ? this.getRandomInt(0, 100)
        : this.getRandomInt(num, 1000);
    },
    getRandomInt(min, max) {
      min = Math.ceil(min);
      max = Math.floor(max);
      return Math.floor(Math.random() * (max - min + 1)) + min;
    }
  }
};
 

Nice article!

Just noticed that the entity 'Zones' has an attribute 'ActID', which shouldn't be there if I am correct. A one-to-many relationship should only have a foreign key on the 'many'-side, as having one on the other side does not make sense. Having 'BossID' there would make more sense ;-).

 
 

Nice post!!!
I think you can try dbdesigner.id to create Entity Relationship Diagram 😎😎😎.
This can help you to collaborate with your team on web and cut off your step in generate SQL Create database.
I write this article to explain more about this devtool dev.to/didin1453fatih/database-des...

Alt Text

 

Gotta go fast! Great explanation!

 

Love it! I should have worked that into the post somewhere