Getting Started with ER Diagrams in Redgate Data Modeler

Learn how to use Redgate Data Modeler’s user-friendly data modeling platform to create your first Entity-Relationship (ER) diagram!

If you’ve never used an online ER diagram tool before, you might be a bit lost about where to start. Don’t worry; I’ll give you a step-by-step guide to getting started with Redgate Data Modeler and creating some basic ERDs (Entity Relationship Diagrams).

Redgate Data Modeler is an online ER diagramming tool that helps design and visualize databases. It provides a modern, intuitive database design UI right in your web browser. But it gets better: it supports the most popular database management systems (DBMSs) and can even generate the SQL DDL scripts and migration scripts you’ll need for your database.

If you’re confused about what an ER diagram is, this article will give you all the background you need.

An Example of an ER Diagram

Here’s an ER diagram that I created for an online forum that I’m building. I based this ERD on the popular forum Reddit, so those that have already used Reddit will be familiar with the functionalities. If you don’t know Reddit, check it out so you’ll understand the concepts I’m describing.

There are different entities and relationships in this online forum database model. We’ll need to represent:

• Users – These are people who can log into the forum and perform various actions.
• Subforums – These are subsections within the forum, which users create themselves for broad groupings of topics.
• Threads – Within each subforum, users create discussion threads about a particular topic.
• Replies – Users can reply to threads and other replies.

Also, I need a private message system that allows users to send messages directly to other users and to reply to messages. I’ll also have a voting system so that users can upvote and downvote threads and replies. Each post and each reply will have a “score” based on these votes.

Lastly, I want an awards system, which is different from upvotes and downvotes. Awards will be available for purchase in a store, so they need a price tag. Users will be able to “award” (assign) them to a post or a thread that they like.

Here is the finished ER diagram for our forum; this is where we are headed:

 

Next, I’ll explain what you need to do to create and share your own data models.

Signing Up with Redgate Data Modeler

First, go to the Redgate Data Modeler website and click on the sign up button at the top right of the page.

 

If you’re using Redgate Data Modeler for educational purposes (i.e. if you’re a student or a teacher), you can go to a page that allows you to use the tool for free after registering. Otherwise, create your account by entering your details. Then confirm your email address by clicking on the link that you’ll receive in your inbox.

You’ll then be brought back to Redgate Data Modeler’s website and be good to go for the next steps.

Creating Your First ER Diagram

Go to the startup page of the ER diagram tool. You will be welcomed by a tutorial explaining how to proceed. But you’re even more lucky, as you have this article to help you!

First, click on the Create document icon in the top left corner, right under the Redgate Data Modeler logo. You will be asked if you want to create a logical data model, physical data model, or SQL script.

 

Which Data Model Do You Need?

To help answer this question, I’ll first explain what conceptual, logical, and physical data models are:

• The conceptual data model illustrates entities, attributes, and relationships. It doesn’t go into detail on how the data is actually structured.
• The logical data model adds more information to the conceptual data model. It describes more about how the data is structured and how the entities are related to each other. For example, it includes information on data types.
• The last model is the physical data model. This model takes into account the restrictions and capabilities of the specific database. It also adds important details like foreign keys, constraints, indexes, etc.

All three models are useful and give information for a specific plane of thought. The physical model will usually be more useful for developers, as it will have the actual implementation details of the database. The conceptual data model will be used most often by data architects or business stakeholders to visualize data.

Generally speaking, the ERD cycle of creation goes from conceptual to logical to physical. We move from a more abstract point of view to a more practical one, while avoiding mistakes on the way. However, starting with the physical diagram works for small projects and for mature projects. In such cases, all three phases are done in the same diagram: the model goes from a rough draft ( the conceptual model) to a precise data model (the physical data model).

But be careful: a bad conceptual data model can lead to huge mistakes in the actual database. For more information on the different data model types, see our article on implementing conceptual, logical, and physical ERDs with Redgate Data Modeler. (Spoiler alert: Redgate Data Modeler actually allows you to create a physical ER diagram from an existing logical ERD. I’ll show you how to do it later in this article.)

Let’s go back to Redgate Data Modeler. You’re asked to choose what document to create; for now, choose Physical data model.

You will then be asked to choose which DBMS you want to use for this model. I used PostgreSQL because it has all the functionalities I need for my forum’s database. Plus, it’s the one I am most comfortable using. You’re free to use whichever you prefer or require for your project.

You can always change which DBMS you want to use for an existing data model, but again, be careful: you might have to tweak some things to make it work. I would recommend you choose wisely and stick to your decision.

Prepare Your ERD

It’s best to think about what you want to model and how you want to do it before starting a model. Take a moment now and think about it, then come back when you’ve made up your mind.

If you want some advice on preparing a database design, read this article.

Create a Table

Let’s move on to the actual building of our model!

Click on the Add new table button, then click anywhere on the screen. This will create your first table.

 

You can see all its properties in the window on the right. This will allow you to add, modify, and delete columns, set a primary key, add alternate keys, etc.

When you add a column, double-check its data type. It’s best to set to the correct one right away, whether it is an integer (int in PostgreSQL), boolean (bool), character (varchar), etc.

So, let’s build a model like the forum one I explained earlier. We’ll start by creating a table called user with the properties shown below. Note that we’re using id for the primary key (PK). Here’s mine:

Of course, you can customize this table however you’d like. Don’t save any useless data, but if you can think of something useful to add for a functionality you would like to implement, don’t hesitate to do so.

You’ll notice that, when specifying a field for characters (first_name), you need to specify the length of the varchar. Obviously, we can’t know what the longest first name among our users will be, but we might safely assume that 100 characters is plenty.

Similarly, you will notice the small “N” next to the definitions for first_name and last_name. This indicates that these values are “nullable”: they can be left empty in the table. In other words, when a user is created, they must have a username but we will not require them to provide their first and last name.

And there you go – you’ve created the first table in your ER diagram.

Define Relationships

Next, you will need to create another table. I created a table called thread. Follow the same process as we used for the user table and give thread the attributes you think it will need.

It’s common for there to be a link between some of the tables in a relational database. In this case, we want to link threads to users, as threads will be the topics of discussion to which users can reply.

To create a relationship in your ERD, click on the Relationship button. (It’s next to the Table button.) Then click and hold the user table and drag it to the thread table. You will notice that Redgate Data Modeler’s online ER diagramming tool opens the properties of the relationship on the right side of the interface. You can set the cardinality that you want for the relationship: one-to-one, one-to-many, zero-to-many, etc.

Your primary and foreign keys for this relationship are already set, but feel free to add more if needed (and to set these keys as non-mandatory). In my example, my foreign key (FK) for the thread table needs to be mandatory to ensure that we identify the user that posted a thread; a thread must always be linked to one user, but there might be users who haven’t posted any threads.

Now that you can create tables and relationships; it gets better: with these tools, you can create the ER diagram that I showed you in the introduction. I’ll demonstrate how to add a few more (optional) things in the next steps – namely, subject areas and views.

Add a Subject Area

In my data model, you can see that I have a blue area engulfing the message and message_receiver tables; this is called a subject area. It doesn’t have any impact on the database structure – it’s a visual help that allows us to see at a glance what tables are involved in the messaging system.  You can also use it to highlight a particular subdomain within your data model.

You add a subject area by simply clicking Add new area. Here’s one that I created for the messaging system of our forum:

I created additional subject areas for the Awards and Forum parts of my ER diagram.

Create a View

This is a bit more advanced, so let’s start by explaining what a view is and why you’d need it.

A view retrieves data from one or more tables and stores the relevant information. If you have a table that stores usernames, passwords, etc., you might not want everyone to have access to the passwords. So you can create a view that shows only non-sensitive data.

Let’s say that you want to have a table showing all the subforums and the users who created them. You can create a view that takes data from both the user and subforum tables.

To create a view, start by clicking the Create view button. Then you need to enter some SQL code, which I’ll explain in a second.

First, we create our view. I’ll call mine user_subforum. Now for the SQL query code:

We select the variables to be included in the view: first comes the table name, then a dot, and then the variable name. Then we list the tables we’re selecting from, user and subforum (user is quoted here because it’s a reserved keyword in PostgreSQL). Lastly, we say that "user".id=subforum.user_id; this puts all the subforums created by the same user in one row. This is the most logical way to organize the information in our case.

This view will always have the information linking subforums created by each user with the username, subforum name, and creation date:

And there you have it! See, it wasn’t that bad. And you’ve just created your first ER diagram in Redgate Data Modeler!

Logical ER Diagrams in Redgate Data Modeler

Remember when I told you that you could create a physical entity-relationship diagram from an existing logical ERD? In this part, I’ll show how you can create the physical data model of a forum by first starting to build a logical data model, then switching it to a physical one.

Create a new model from the Documents tab in Redgate Data Modeler. Next, select Logical data model; from there, create your tables as you would in a physical data model. Then, add relationships between the entities; I used one-to-many and one-to-one, but what you choose will depend on the type of data you have to model.

Creating a logical model is as straightforward as a physical model. Your logical data model might include associations and inheritance that can be useful for object-oriented programming. For more information about inheritance modelling, I suggest you read this article.

Once you have your logical data model, you can use it to create the physical data model automatically. Simply go back to your list of documents, right click on your logical data model, and click on Generate physical data model:.

Next, choose the DBMS you are going to use and… that’s it! The outcome is exactly what I wanted.

Below is the logical data model for the forum and the physical data model generated from it. You can see that this physical model is very similar to the one I created earlier. However, the data types have changed and we can now see the foreign keys present in the correct tables. It is much more adapted to the implementation of the database. It’s also in this model that we can use views and some other tools that I haven’t spoken about, such as indexes.

1. Logical Data Model
   
2. Physical Data Model
   

For a more detailed look at this process, read our article How to Generate a Physical ER Diagram from a Logical Diagram.

Sharing Your Data Models

I’ve been showing you my data models, and you can do the same. Furthermore, you can share your models in view-only mode, but you can also invite friends to work with you on Redgate Data Modeler. Simply right-click on your model when you’re in Documents and click Share. A window will pop up where you can copy the link to your document or add email addresses. If you’re the owner of the document, you’ll also be able to manage access to it.

Sharing your models over the web lets you collaboratively work with a team on creating an ERD. For example, some of the team might work on one subject area, while others work on another area (just like collaborative editing with Google Docs). It’s a great way to work with a distributed team.

What ER Diagram Will You Build Next?

Learning how to create an ER diagram with Redgate Data Modeler can be a lot to take in, but don’t worry. We have several tools to help you get started with online ER diagramming:

• Documentation
• Support platform
• Our blog, which is full of guides, examples, how-to’s, etc.

Above all, be sure to keep practicing your ER diagramming skills. Online tools like Redgate Data Modeler are especially helpful, as they let you work with more experienced data professionals. Keep learning, keep building your skills, and mastery will follow.

Tools in this post

Redgate Data Modeler

Design, update, and manage database schemas

Find out more