Jul 29, 2020 7:36:00 AM by Tim Fritz
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.
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:
So, who within an organization would use ER diagrams? Certainly these people will find ERD’s useful:
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.
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:
Quest® provides three products to suit the needs of people in varying roles in your organization: Toad, Toad Data Modeler, and Toad Data Point.
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 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.
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:
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.
Need help managing data?
Quest Software® is here to help you simplify complexity, reduce cost and risk, and drive performance. Maybe that’s why we’re consistently voted #1 by DBTA readers and have 95%+ customer satisfaction rate.
Toad® database management tools are cross-platform solutions from Quest® that can help new and veteran DBAs, and even “accidental” DBAs manage data in Oracle, SQL Server, DB2, SAP, MySQL, and Postgres environments.
Learn more about how Toad database management tools from Quest can help with database development, performance monitoring and database DevOps.
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!
Written by Tim Fritz
Quest Software solution architect - database performance management and administration solutions. Former DB2 developer and DBA. IBM Certified Database Administrator – DB2 LUW; Microsoft MCP, Certified Technical Specialist – SQL Server.