I know a lot of us have more time on our hands lately, being stuck inside at the home office, but you probably want to see some juicy details right up front, rather than reading the whole article and savoring the slow build-up. Here’s an animation of my new entity and attribute shapes in action:
Ok now for a bit of history, some design goals, and a rundown of the features of the new ERD shapes!
Visio and Database/ERD Diagrams Background
Several years ago, Microsoft added a simpler, easier-to-use set of Entity Relationship Diagram (ERD) shapes to Visio. You can find them in the Crow’s Foot Database Notation stencil, buried deep under Shapes > My Shapes > Software and Database > Database. You can also start a new diagram using the associated template, which you’ll find under: File > New > Templates > Software and Database > Crow’s Foot Database Notation.
I also found a sample diagram called Books ER Diagram by searching for “ER Diagram” in the Search for online templates field (on the File > New screen.)
These shapes are great for quickly illustrating and designing a database. You directly manipulate shapes, typing in entity names, attribute names, and attribute types. You can quickly re-order attributes because the shapes use the fancy List Container behavior, that eliminate the need for grouping and awkward shuffling of rows and columns of shapes. There’s a nice, quick video on how to use these shapes here: How To Use Visio 2016 For Conceptual Data Modeling And Er Diagram.
Visio has a more capable and complicated database reverse-engineering tool, accessible via the Database Model Diagram template. But it isn’t so good for quickly sketching out database relationships. You can manually create entities, but you have to enter a lot of information via a grid interface. The tool is more geared for pointing at an existing database, and dragging out existing entities and relationships. If you’re interested, I found this (no-sound) video for Visio 2010, I’m not sure if the tool is the same in Visio 2013 or 2016: Visio 2010 Crows Foot ERD.
New Salesforce-style Shapes
I like the Crow’s Foot shapes. They’re simple, clean and easy to use. But they’re a bit blocky, and you have to do some mildly cumbersome moves to fully use them. I’ve had some ideas on how to improve them bouncing around in my head for awhile now. This week I distilled them down to these four goals:
- Create a snazzier style that was a bit more compact
- Make it easier to type the attribute name and attribute type onto shapes.
- Eliminate cascading menus in the right-click context menu.
- Expand and collapse attributes according to various criteria.
When I started out, I wasn’t sure if #4 would even be possible. Thankfully it I was able to get it to work!
As for “snazzier style”, the Salesforce Schema Builder ERD shapes caught my eye. They are a bit more “User-interface” looking than your typical diagram, but I thought; “why not see if I can make the Visio shapes look like those?” I’ve pinched this image from Salesforce’s Schema Builder site, I hope they don’t mind:
Now I’m no Salesforce expert, nor do I know much about Schema Builder. If I missed some obvious functionality, or interpreted something completely wrong, please let me know in the comments below!
I think they turned out pretty well. I’ve built-in three colors that you can quickly choose from via the context menu. I’m not sure if these have special meaning in Schema Builder or not, but I thought they could be used for emphasis in a diagram. I think the blue just means that the entity is selected in Schema Builder. Oh well, you can use the colors for whatever you want:
To change the entity name, simply select the shape and type, just as with the original shapes.
The entity shape has right-click menu items for showing/hiding particular attributes, displaying or hiding attribute types, and setting some built-in colors:
As usual, I try to build as many context menu items in the Shape Data fields as well. The reason I do this is that you can select multiple shapes, and set properties using the Shape Data fields. With right-clicking, you can only operate on one shape at a time.
One note: the Add Attributes item at the top of the menu is reserved for future use. For the download at the end of this article, it won’t do anything. I have an idea to make it easy to enter lots of attribute names and types at once in a pop-up dialog, but that is not functioning yet.
Here we see the entity completely collapsed, hiding all of its attributes:
I didn’t want the attributes to completely disappear, so I’ve left a teensy bit of height for each one when they are hidden. The Name-Type Separator is inherited by child attribute shapes to make it easier to enter text. More on that in a second!
You add attribute shapes to the entity shape. Since the entity is a List Container, the attributes neatly snap into place, and can be re-ordered by simply dragging them up or down.
To edit the name and type of an attribute, you just select a shape and start typing. Or double-click to edit existing text. The one thing to remember is that you enter the name and type at the same time, separated by a semicolon:
The shape will parse the expression and send the characters to the left of the separator to the name box. The rest of the text will be the attribute type. Yes, it’s a bit nerdy, but it is much faster than subselecting the type box, then separately typing in the text, as with the original shapes.
If it turns out that you have semicolons in your names or types (unlikely?), you can select the entity and change the Name-type Separator field to anything you want. You could pick something super unlikely, such as double-pipe ||. Then you type attribute-name||attribute-type into the member shapes!
Here I’ve set the separator to the ridiculous “visguy”, which means I have to type that between the name and the type. Not very practical, but it works:
Notice that the Shape Data for the attribute shape has three fields with an asterisk*. I use this to denote fields that are read-only: you can’t edit them. Separator is inherited from the entity shape, as I mentioned earlier; Attribute Name and Attribute Type are calculated from the text that you manually type in.
You can specify whether an attribute is a primary key, a foreign key, and if it is a required field. The red bar indicates the field is required. It appears to be a look specific to Schema Builder. I think it looks cool:
The Relationship connector is pretty much the same as the original shape that Microsoft built. I’ve flattened out the menus, because I found it tiring to always have to dive into a cascading menu. Yes, the menu is quite tall, but it’s still faster to have everything at the top level. I’ve also added the same built-in colors that the other shapes have, in case you want to highlight some relationships:
I did, however, streamline the entering of the Multiplicity text. Instead of subselecting each text block, you simply type four values into the Multiplicity List Shape Data field, and separate them with a semicolon:
Full disclosure: I don’t even know what multiplicity is for!
As with the Entity and Attribute shapes, you can change the separator, should you need to display semicolons in the text. You can also do everything the right-click menu can do via the Shape Data panel.