If you have been using SQL and relational databases professionally for years, then it’s likely you have taken advantage of Enhanced Entity Relationship diagrams (EER diagrams, or EER models) dozens of times. However, if you are newer to the SQL game, you may not even realize how valuable creating EER diagrams can be. Luckily we’re here to show you what you’ve been missing!
When you are new to SQL, you are likely working with your first database. In this case, it is likely that someone showed you the ropes. That person probably told you which of the tables were the ones you would use the most, how they relate to each other with joins, and even the key nuances in the data you should look out for. If this describes your situation, you probably know one dataset really well. While deep knowledge of the database you are currently using is great, at some point in your career you may want to switch jobs or do a consulting project on the side, and you will need to learn about a new database.
Personally, I have been through learning a new database many times. Part of becoming a SQL expert is developing the ability to approach a new schema on your own, and quickly learn the ins and outs, without needing someone else to walk you through all the details. In my experience, the fastest way to self-serve and become an expert in a new database on your own, is to leverage EER diagrams.
Why do we love EER diagrams so much? Here are some of the key benefits:
Quickly see which tables are in your database, and which columns are in those tables
Learn the data types of all columns in one view
Understand how the key tables relate to one another. Easily identify primary and foreign keys, which helps a lot when you are writing joins in your queries
See which columns have constraints that you should be aware of (absolutely critical to a DBA, and also extremely helpful to a SQL Analysts)
Take the simple EER diagram below as an example. In one quick view, we can easily see which tables we’ll be working with, the columns within them, how they relate to each other through primary and foreign keys, and also which tables are allowed to contain NULL values and which have the NON-NULL constraint imposed.
Let’s say in the future you are lucky enough to start a new job where someone has already created an EER diagram for you. My advice to you is to ignore it. Don’t even look at it, at least not at first. I know what you’re thinking… has this guy lost his mind? He just finished talking about how valuable EER diagrams are, and now he’s saying I shouldn’t use them. What gives?
While using an EER diagram that someone else has already created can be helpful, in my opinion, the act of creating your own EER diagram is by far the most valuable thing you could spend time on to quickly build mastery of a new database. When you force yourself to start from scratch, and list out all of the tables, their columns, data types, key constraints, and primary key to foreign key relationships, you will become an expert in the dataset in no time.
If the database is huge, let’s say 100+ tables for example, you might not want to diagram out the entire thing, at least not right away. In this situation, there are probably 10-20 tables that you are likely to use most often or your job. You should aim to focus there first. Diagram out those 10-20 tables to get up to speed. You can always add the others later if you find yourself using more tables than you expected to. Focus on going really deep in the area where you will be contributing to the business.
As an Analyst who is new to the business, how can you figure out which tables you will need to be using most often? If you have the luxury of other Analysts working in the company, I recommend you ask one of them which are the three tables you are likely to use most. Start with those, and figure out which other tables they relate to directly. Expand from there.
If you find yourself walking into a situation where there is no current database expert (usually an Analyst or an Engineer), then you will need to get the ball rolling on your own. While this situation is unlikely, it could happen. Maybe the original database architect has left the company, and there are no remaining experts (this is possible at small startups, very unlikely at larger firms). If you do find yourself in a position where there is no expert, and you have to become the expert on your own, I recommend you start by learning about the tables which capture your revenue. These tables will have names like ‘orders’, ‘sales’, ‘purchases’,’charges’, etc. Look through the list of tables names in the schema, figure out which ones appear revenue-related, and start from there. Similarly, if you are going to be focused on a certain area of the business like manufacturing operations, or call center analytics, look for tables that relate to those things. Whatever the case is, start with the tables that are closely related to revenue and to your specific job function, and then expand from there.
As you go through the process of creating your own EER diagrams on the key tables you’ll be working with, you will not be able to avoid becoming an expert. You are going to need to figure out the key joins, learn which columns have constraints, which tables you need to access to pull specific data points, etc. This is why I always recommend new Analysts spend their first couple of days creating their own EER diagrams as a shortcut to developing expertise in their new schema.
Hopefully I have sold you on how valuable creating EER diagrams can be when you are trying to get up to speed in a new database. If you need any more convincing, I will throw in the fact that they are pretty easy to create, and most SQL tools like MySQL Workbench have EER diagram functionality built in, so you can build your EER models right in the editor you will already be using.
Where can you learn more about creating EER diagrams?
For more on EER diagrams, including a step-by-step walkthrough on how to create them in MySQL Workbench, check out our new MySQL for Database Administration course.
Keep learning! John
