I recently wrote about a logical diagram with Redgate Data Modeler. That was interesting, but creating all the objects is a pain. I decided to try creating a physical diagram from an existing database. This post looks at the experience.
This is part of a series of posts on Redgate Data Modeler.
Getting Started
As with the logical model, I right click and choose New document.
Then I get a list of options. I’ll choose the middle one, Physical Model. This is what I mostly need to work with an existing system. Since I don’t often move models from one platform to the other, a physical diagram can work well for me.
Once I click next, I get a list of platforms, and I need to enter a name. I’ve done that. Notice that when I select SQL Server, I can alter the drop down for different versions of SQL Server.
Below this, I see a source box, and a file picker.
I need a file.
I’ll go to SSMS and right click my database. I’ll find the Generate Scripts task, as shown below.
I go through the wizard and mostly pick defaults.
I do script to one file, not separate ones. I choose all objects.
Back to Data Modeler with the file. I select this and upload it and see…an error.
When I open the script, I see lots of non-database stuff.
I’ll delete this stuff and also a bit at the bottom. I rename this to Westwind.sql, which is what I am importing. Then I was able to import the file and when I clicked “Start modeling”, I ended up with this:
Redgate Data Modeler (RDM) has detected quite a few relationships. You can see them in the diagram for the explicit ones defined. If I click one, such as the Order to Order Details item, I can see on the right that this is for OrderID in both tables..
One thing that threw me slightly was two Employees tables. I was wondering what was going on here, but when I clicked the lower one and looked through properties, I can see that this is for a different schema. I wish this were more visible, but it did get detected.
Cleaning up the Design
One of the things I like is that I can set areas in the model. This lets me organize things and even convey information to developers and others that work with the database.
At the top left, I have a series of icons. The last one on the right is the New Area icon. I’ll click this.
I can now draw an “area” somewhere. Notice the right when I do this. I have properties for this area.
I’ll add a name and change the color, which gives me way to easily see this area in my diagram. I’ll also drag in the Auditing.Employees table, as this is what I want people to know.
I can select this and move it (look at the video) and then I see this as a part of my diagram, but clearly separate from other parts. Developers can learn the light red is the auditing schema, which is separate from the rest of the diagram.
I can add other areas, not just for schemas, but for separating out parts of my database. Often I have a series of entities that I care about, or want to cluster together, and having areas with colors lets me separate these out.
I might run out of colors in a large database, but I could use lots of pale blues or grays to separate out areas, each of which has a name. That can be helpful to reduce the complexity of the model.
Adding a New Table
I can also add a new table if I want. One of the icons at the top is for new tables.
When I click in the model, a new table appears. The right opens up the properties with a default name (table14). I can change this and add the columns I need.
Once I’ve finished, I can add a relationship. There is an icon for this.
I’ll click this and then click and drag from Products to Discount. This gives me a new column in Discount by default, called Products_ProductID. Not a bad name, but in general I want a cleaner name. I’ll edit the relationship to use ProductID in both tables and delete the other column from the model.
Modeling with an image is a good way to start to visualize how things are setup and where you might be normalizing, or denormalizing data. I also want to know just how many things are in here and what is related to what.
Moving Forward
There are more things that can be done with tools like that to help ensure our databases are well designed and perform well. I’ve submitted feedback to the team and asked for some enhancements.
One of the things I might want to do from here is update my dev db from the model. I’ll show that in a future post.
For now, I like the idea of getting a model started from my SQL script, though clearly I need a clean script. I’ll do more testing with other scripts for both forward and reverse engineering.
Give Redgate Data Modeler a try and see if it helps you and your team get a handle on your database.