ThatJeffSmith

Entity Relationship Diagrams (ERD)

I have a nasty habit of not thinking. I take something for granted or assume something for so long, that it corrupts my original learning or understanding of a concept. And then when someone calls me out, my first reaction is to defend my ‘shortcut’ thinking instead of re-evaluating my set of assumptions.

So I’ve been working with data modeling software for almost 15 years and on a regular basis for the last 8 or 9 years.

When talking to customers, ninety-percent of the time, when they say –

Can your tool generate an ERD?

– they are asking for a diagram which displays and describes the PHYSICAL properties of their data model for their application. Which in this case is generally a diagram of their tables and views and the relationships between them.

This is NOT an ERD though.

The ‘E’ part of ERD is important. It stands for ‘Entity.’ Entities are a much more abstract concept than a table. An Entity is a 1000 ft view of how you are going to handle a uniquely identifiable piece of data – kind of like how an atom used be seen as the smallest physical piece of matter. In a logical model, I might call an ‘entity’ a ‘customer.’ When I go to build the system physically in a databse like Oracle, that might actually be composed of one or more TABLEs.

So the Logical or Entity based diagram is completely separate from the Physical diagram.

I have a nasty habit of calling THIS an ERD

This is NOT an ERD

When I do an import of a data dictionary (which is a live, breathing instance of Oracle in this case), SQL Developer Data Modeler builds for me by default a Relational Model which is also composed of one or more Physical models.

And I get a diagram!

And that’s USUALLY enough for what people are looking for.

But when someone says they NEED an ERD, I should know better to clarify what their requirements actually are. And to make sure that their idea of an ERD matches up to mine, regardless of who is ‘right’ or not.

An Actual ERD

In SQL Developer Data Modeler I can reverse engineer a Relational Model to a Logical one (it’s on a right-click context menu.) Once that’s done, THEN I have a list of Entities and Attributes AND the associated Entity Relationship Diagram.

This is an ERD. Those blue thingies are Entities which will later live on as tables.

So what’s the big deal?

Don’t call something by name if it’s not the right name. Or if you do ‘cheat’, be sure to clarify and explain what you’re actually referring to. Or someone who doesn’t really get what you are saying will hear your words and start building a shaky understanding of the concepts.

And THAT is how poorly normalized systems with no foreign keys and tables with hundreds of columns are born.