What an ER diagram is and tools that help build and alter them

For database design, application development, and access to data from database objects, an ER diagram (Entity Relationship Diagram, or ERD) is an essential resource.  In this post, we will examine what an ER diagram is and, if asked to create or utilize one, the various types of tools that can help one do so cost-effectively.

ER diagrams visualize data models: Are there cost-effective tools to glue them together?  (Yes!)

People responsible for designing data, and anyone who builds a physical model and database objects based on a logical model, will definitely make use of ER diagrams.  Why?  Because they depict visually the relationships between logical “things” that have been modeled during the design of a database based on business requirements (rules) of the data.  Modeling, and the visual aid, is important, especially to:

• Verify that a design accurately depicts business requirements (rules)
• Make complex business rules understandable.
• Build understanding of what data of value reside in particular database entities for reports and business decisions, making clearer what is possible regarding the extraction and use of meaningful data.
• Check data element names on the diagram for adherence to naming standards – before the database is actually built and columns/elements are actually physically created.
• Help auditors and other compliance and data governance professionals in locating sensitive data elements and other data that, by policy or regulation, require special care or scrutiny.
• Provide a “roadmap” to data elements stored within the database so accurate queries can be written to obtain exactly the data that is desired.

So, who within an organization would use ER diagrams?   Certainly these people will find ERD’s useful:

• Data modelers/architects
• Data analysts
• Application developers
• DBAs
• Others in your organization?

Here is a Toad World blog post that provides a concise and helpful overview of data models and ER Diagrams, specifically targeting data analysts, by my Quest colleague, John Pocknell: Data modeling for the business analyst.

What is an ER diagram?

Maybe the best way to define an ER diagram is that it is a visual representation of a model.  So, what is a data model?   Essentially, as I hinted at in the previous section, a data model defines how data is organized and stored.  It includes the sizes of data fields and their data types.

 An ER diagram is also a communication tool, helping in those situations in which a team of people need to work on designing data, designing a physical database, or write queries for an application or for ad hoc reporting.  Data architect team members need to know what state a new data model.   DBA team members need to know exactly how a data model is being translated into a physical model, and the status of that process.   ER diagrams help with all of this in ways that nothing else can (they are visual, and as detailed as team members make them).

Tools, of course, can help with building, altering, and using ER diagrams and their underlying models.  And tools can facilitate communication among team members by documenting modeling efforts to date.  For example:

• See data locations and relationships. A basic ER diagramming tool can provide a quick logical depiction of how a database is structured – great for those who need to write queries against that database.
• Compare models of structures cross-platform. A data modeling tool can help in mapping data between two different database systems for data integration projects, for example comparing structures (through models) on Oracle with structures on SQL Server, and see ER diagrams of each.
• Reengineer.  Some data modeling tools enable data structures from one type of database to be “re-engineered” into a different database system, such as from Oracle to MySQL, and see the ER diagram for each.

Quest® solutions for building and using ER diagrams

Quest® provides three products to suit the needs of people in varying roles in your organization: Toad, Toad Data Modeler, and Toad Data Point.

Toad® for Oracle: For developers and DBAs are common users of Toad.

The Toad ER Diagram component allows one to drag objects onto a diagram and relationships between entities can be drawn automatically (if referential integrity is enforced by the database management system) or manually.  Other Toad functions can then be performed from the diagram, including table and column operations and creating SQL from the diagram (for example, multi-table joins).

This tool is designed into the Toad interface, providing the benefits of an integrated method of seeing the data design and the details of data elements and primary and foreign keys – all without leaving Toad.

Here is a short video illustrating how to place objects of a database onto an ER diagram for documentation, investigation, understanding, or query-building: Video: 'Dan's Dozen' Quick Toad Tips – Tip #10 – ER Diagrammer.

Toad Data Modeler: For data architects, who build models as they design the data for a new or existing database.

Toad Data Modeler deserves more description than this post has space for, but think of this tool as a more robust tool for professional data modelers.  Think of the two use cases I listed in the last section:  comparisons of models and reengineering of databases into models.  A full-function modeling platform with a wide variety of database platforms supported for connections and reengineering, for example. 

Here is a Toad World video post about reengineering – creating a model from physical database structures – and managing data models: How to reverse engineer and manage models in Toad Data Modeler.

Toad Data Point: For data analysts, and in fact anyone who is asked to build somewhat complex reports pulling data from multiple data sources. 

Users of Toad Data Point can take advantage of the “see data location and relationships” use case for a diagramming tool, just as Toad for Oracle does.  Toad Data Point has a built-in diagramming component, just as Toad for Oracle does, and they are very similar (which is great if you use both Toad and Toad Data Point – only one diagram interface to learn!). 

Here are some resources that allow you see Toad Data Point’s ER diagramming in action: 

Video: How to diagram data in Toad Data Point

Video: Toad Data Point – The ER Diagrammer

Summary

We defined an ER diagram, spotlighted several personas in an organization that might use ER diagrams, and described solutions from Quest that can help in building ER diagrams. 

However you create ER diagrams, they will be useful tools.  If you’re already a Toad user, you definitely have access to the built-in ER Diagram tool.  Many Toad users make use of that feature to view entity properties and relationships easily and quickly. 

The same holds true for the data preparation, data analyst tool, Toad Data Point.  Use its built-in diagramming component to visualize how data relates to other data as you build queries or prepare the data for deeper analysis.

If you use a Toad edition that includes Toad Data Modeler (for example, Toad for Oracle DBA Edition), of if you own Toad Data Modeler standalone, you have a more robust modeling solution that provides the ability to reverse engineer a database schema into a model that can be depicted, and altered, in a diagram.

Share on social media

If you think your colleagues would benefit from this article, share it now on social media with the buttons located at the top of this blog post. Thanks!

Read more blogs from Tim.