For those of you who may not come from a super-technical background, the term “ER Diagrammer” may sound strange or unfamiliar. Well, for the record, the term “ER Diagrammer” stands for “Entity Relationship Diagrammer” – a fancy name for a tool that displays the relationships between objects (like tables) in the database. You may ask, “Ok, but why is such a technical tool in a product like Toad for Data Analysis that’s supposed to make things easier?” Such a tool is actually indispensible for anyone that wants to retrieve accurate data in an efficient manner. In this article, I’ll briefly describe the benefits of Toad for Data Analysis’ ER Diagrammer and cover its use. (And don’t worry… its super simple to use!)
First, why should I use the ER Diagrammer?
Well, the very first reason is that the ER Diagrammer – let’s call it ERD for now – is going to show you a sort of “map” of your database or schema. If you just made a connection to the database and wanted to write a query, how would you know if there was more data that you need in some other table(s)? Obviously, the whole point of a relational database is that you can store like records together in their own tables and then define a relationship between that table and some other table. If I have a church database with member records, I may want to put all of the adult members together in a table and then create a separate – though related – table for the records concerning the members’ children. In each table I need to store different kinds of information: most adults won’t be in grade school (I hope) and the children won’t have email addresses or work telephone numbers… If I want to count the number of people in the church, I probably need to include both the MEMBER and CHILDREN tables in my query. The ERD visually maps out the relationship so I can see that these two tables are related.
Another reason to use the ERD is that, in Toad for Data Analysis, you can use it as the basis for your query. If you use the ERD to gather all of the tables you want to query from, then when you send the tables to the Query Builder or the SQL Editor, the tables and the necessary JOINs are already pre-populated – all you have to do now is select the columns you want to get data from. Even if you’re a pro at writing SQL, you can still save time and prevent headaches associated with getting all of your JOINs set up properly by using the ERD.
Perhaps one of the less obvious benefits of the ERD is the fact that you can use it to document schemas or databases. Imagine that church database from the previous example: if the relationship between the CHILDREN and MEMBER tables didn’t actually exist in the database, you can use the ERD in Toad for Data Analysis to manually define the relationship and then save the diagram as a .ter file. Now you have the schema or database documented for future reference or use without actually making changes on the server – very important if you don’t have the permissions to make such a big change! You can even use these saved ERDs for new people or people that have less experience writing queries; just hand the ERD to them and let them use the tables it contains to begin building their query.
Using the ER Diagrammer
So, enough talk; let’s take a look at the ERD.
Screenshot of the ER Diagrammer in Toad for Data Analysis
The ERD begins as a blank document with a graphic in the center describing how to use the tool. To get started, either double-click on a table in the Object Explorer on the left or just drag-and-drop it onto the ERD document. If the table has a relationship defined in the database itself, all of the tables that are directly related to the one you selected will also appear on the document.
Tables and their relationships
In the above example, I double-clicked on ORDERS and along with it came the CONTACT and ORDER_ITEM tables, because of their database-defined relationships. I can add more tables and I can remove tables; I can even add my manual relationships now by selecting one column in a table and dragging it over to a column in another table. But regardless of what I do from this point, I’m no longer guessing about where my data is and whether or not there are other tables in the database that I need to select from; I’m beginning to understand the schema or database before I begin writing my query.
I added the WAREHOUSE table and manually defined the WAREHOUSE_ID relationship
Once I have the tables I want in the ERD document window, I can add notes to further document the table, I can hide columns in the table if there are just too many to work with effectively, and I can even associate a lookup table to an ID column. And with that, I can then save the ERD as a .ter file or I can move on to my query… but more on these in another article!