Tag: Data Modeler

This happened to me recently after being busy with non-data modeling tasks for a few weeks. I went to add a relationship and was confused about the behavior. Read on to see what happened and what I did.

This is part of a series on Redgate Data Modeler.

Adding a Relationship

I opened one of my models and saw something like this. Note that the Article and ArticleTag entities aren’t related.

I decided to add a relationship. This is a Many to Many, so I clicked on that icon in the toolbar.

I then clicked on the Article entity and saw this. It’s a self-referencing relationship.

What??? I assumed I’d click one entity, then the other. I tried grabbing various elements of the relationship to move them, but nothing worked. I could move them around within the relationship, but not to another entity.

I deleted and added this a few times before I decided to check the docs. On this page for relationships, I found my mistake. There is this quote: “If you’ve already selected the tool, simply drag from one entity to another.”

Aha.

If I click the toolbar relationship icon, and then drag from one to the other, it works.

I suspect this is an artifact of the web controls, but it was weird for me to not be able to alter the relationship targets. Even in the right properties pane, I can’t change this.

Summary

A simple thing, but not quite as intuitive as I’d like. However, one needs to learn to use tools, which means reading docs or using Claude/ChatGPT and

This is a reminder to drag relationships in your Redgate Data Modeler models and don’t get away from modeling for weeks, like I did.

Give Redgate Data Modeler a try and see if it helps you and your team get a handle on your database.

Video Walkthrough

Here’s a short video of my working with Redgate Data Modeler and changing cardinality.

One of the things a customer asked recently about Redgate Data Modeler was how to adjust a FK constraint between two tables. The tool seemed to makes this  easy, and this post will show you how.

This is part of a series on Redgate Data Modeler.

Altering a Constraint

In a previous post, I looked at adding a FK. Let’s do things a little differently here. Let’s change a constraint to better reflect the relationships between entities. In this case, let’s look at a part of my model. I have the User and Author entities linked as shown below. Note that we have a User with a 1:many relationship to Author.

This isn’t correct. A User cannot be a part of multiple authors. This is, logically, a 1:1 relationship. Each Author is related to one and only one User. Each User can be an author, but only one Author. So let’s fix this.

If I click on the relationship, it turns blue and highlights.

On the right side, the properties pane displays the relationship properties. Note that there is a relationship type below the comment.

If I click the  drop down, I can change this to whatever cardinality makes sense.

I’ll choose one to one. When I do that, I can see the diagram changes.

I can also change this for other entities. Here I have a One to Many, but this is really a many to many relationship. So I need to change it.

I can adjust the properties in the drop down and you can see both the diagram and the right properties pane match.

Summary

We may model our entities incorrectly, either because of incomplete information or simple mistakes. In any case, if you need to change cardinality in a relationship, it’s easy to do in the relationship properties.

Give Redgate Data Modeler a try and see if it helps you and your team get a handle on your database.

Video Walkthrough

Here’s a short video of my working with Redgate Data Modeler and changing cardinality.

One of the things a customer asked recently about Redgate Data Modeler was how to set a FK constraint between two tables. The tool seemed to make it easy, but they encountered a few errors. Here is how this worked for me.

This is part of a series on Redgate Data Modeler.

Adding a Constraint

This might make more sense in the video walkthrough, but here’s the text version. I want to add a constraint to my model that links the Organization and User tables shown below. This is a 1 (Organization) to many (User) FK.

I don’t have a good FK yet in the child table (User), but that’s OK. I’ll click the Add new reference icon in the upper left of the design surface.

Once I do this, I can click on the Organization table and drag to the User table. That will give me this view. Note that this defaults as a 1:n relationship, so you want to start with the parent. There also is a new “Organization_OrganizationID” column added as a FK.

That’s not a bad pattern, especially with modern Intellisense, where I don’t need to type everything out. This lets me know where the join should be. However, for many of us, we prefer having something simpler, like OrganizationID as the column in the child.

If I want to change this, I can look to the right for the Reference Properties. Note the default name below is User_Organization, which I definitely don’t like.

I can adjust the name to meet my standard, which I’ll do. I can also adjust the FK column, but I’ll need to go to the child table, User, to do this. If I rename that column there, I see this.

When I click back on the reference, I see this. My change for the FK table is there, but the Primary has defaulted to OrganizationName. Fortunately, there’s a drop down where I can change this.

Below this, I have other properties. There’s a color (if you care), but also I can set cascading actions. See the drop down below and the options. These can be set for update or delete. There is also the additional property to set this as not for replication.

Once I do this, the changes are saved. If I generate the SQL script, I can see my FK exists inside the script. You can see the relevant portion below.

Summary

Setting accurate FK constraints is an important part of data modeling. I certainly see the reasons why some people don’t like FKs, but if you set them, you want them to be accurate. Redgate Data Modeler supports this, but it’s not as straightforward as I like. Hopefully that changes over time.

I don’t know that the names matter that much, but in case you are concerned about naming, you can customize this.

Give Redgate Data Modeler a try and see if it helps you and your team get a handle on your database.