Entity-Relationship Diagrams (ERD) are a way to visualise relationships between tables and the common keys between them. On first glance, they may look like a spaghetti mess.<\/p>\n\n\n\n
This post hopes to demystify the language and how they fit together with the help of Sonic the Hedgehog.<\/p>\n\n\n\n
Conceptual Data Model<\/a>
Logical Data Model<\/a>
Physical Data Model<\/a>
What do the circles and arrows mean?<\/a>
Who uses these diagrams?<\/a>
Which systems can we model?<\/a>
How do we start?<\/a>
Any gotchas?<\/a><\/strong><\/p><\/blockquote>\n\n\n\n
\n\n\n\nIn 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.<\/p>\n\n\n\n
\n\n\n\nConceptual Data Model<\/h2>\n\n\n\n
Level one!<\/p>\n\n\n\n
The Conceptual Data Model, this is a high-level overview of the different entities in the model and their relationships.<\/p>\n\n\n\n
<\/figure><\/div>\n\n\n\n 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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
\n\n\n\nLogical Data Model<\/h2>\n\n\n\n
Next up … The Logical Data Model which adds more details to our model.<\/p>\n\n\n\n
<\/figure><\/div>\n\n\n\n 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<\/a> to show exactly how they are related.<\/p>\n\n\n\n
\n\n\n\nPhysical Data Model<\/h2>\n\n\n\n
And finally … The Physical Data Model, that shows how the model will actually be implemented.<\/p>\n\n\n\n
<\/figure><\/div>\n\n\n\n This features the Data Types and names of the tables in our database. In this case, all the tables record Attributes so are Dimensions.<\/p>\n\n\n\n
\n\n\n\nWhat do the circles and arrows mean?<\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
<\/figure><\/div>\n\n\n\n
\n\n\n\nWho uses these diagrams?<\/h2>\n\n\n\n
The Business Intelligence (BI) team uses them when modelling data to determine requirements, show users how the tables fit together, and to debug systems.<\/p>\n\n\n\n
\n\n\n\nWhich systems can we model?<\/h2>\n\n\n\n
Structured data in relational systems. We need to be able to see relationships in the form of tables, columns and keys.<\/p>\n\n\n\n
\n\n\n\nHow do we start?<\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
Lucid Chart<\/a> 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.<\/p>\n\n\n\n
\n\n\n\nAny gotchas?<\/h2>\n\n\n\n
- Don’t join relationships to relationships<\/li>
- Use lots of colour … wisely<\/li>
- Make entity and attribute names easy to understand<\/li>
- Know your audience – are you doing this for a user in management or to implement a new data model?<\/li>
- Don’t overthink it if you don’t have to.<\/li><\/ul>\n\n\n\n
\n\n\n\nPhoto by Scott Webb<\/a><\/strong> from Pexels<\/a><\/strong> <\/p>\n","protected":false},"excerpt":{"rendered":"