Voice of the DBA

Tag: database design

The Data Model Matters

I ran across a statement that seems exciting to me as someone that has written a lot of code in their career. It said: “Many of the “modern” software practices of the last decade were early adaptations to this shift, even if we didn’t articulate them that way. Immutable infrastructure. Stateless services. Containers. Blue-green deployments. Infrastructure as code. These ideas all share a common premise: never fix a running thing. Replace it.”

These are a few sentences in this piece on the death and rebirth of programming. That’s how a lot of software developers have viewed the world during the last decade and we’ve seen a lot of software advances in that time. The very successful developers and teams, who often speak at conferences and publish papers have adopted many of these practices. Serverless, containers, lots of tests allowing continuous deployment of new objects into complex environments that scale to levels many of us never thought possible. These are the very high performances talked about in the State of DevOps report every year.

At the same time, many people reading about these successes and trying to emulate them struggle. So many customers I know want to use containers, but struggle. Many teams lose control over serverless functions and stateless systems, having issues with immutable infrastructure. They revert, or often combine, older ways of building and deploying software with some of the techniques they read about.

If they struggle with stateless systems, it’s no wonder they struggle with the really, really important stateful ones: the databases.

Databases are state machines. We evolve and grow them. NoSQL systems were developed to try and deal with some of the scale issues with relational systems, but they often push the immediate problems of concurrency and efficiency to the side, invoking eventual consistency and redundant data models that keep multiple copies of data around for quick access. They also defer one of the strengths of relational systems, aggregating lots data, to another system, usually a data warehouse, data lake, or some other architecture.

That works great, though it comes at the cost of more compute, more latency to develop and produce those aggregations, and more cost to store all that data in yet another place. That’s not to disparage those designs. They work well and handle workloads most relational systems couldn’t manage.

However that brings to mind two things. One, perhaps that easy and instant aggregation isn’t as important as we think. After all, often companies at that size never have a view of all their data. It’s changing too often, yet they are successful. Secondly, if you don’t have the funding to manage that complexity (both in machine and human resources), perhaps you ought to focus on what is important in this age of cheap code changing often.

Build a strong data model and write efficient SQL Code.

Steve Jones

Listen to the podcast at Libsyn, Spotify, or iTunes.

Note, podcasts are only available for a limited time online.

June 5, 2026
Reverse Engineering a Physical Model Diagram

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.

December 1, 2025
Is Data Modeling Common?

Recently, I had a few questions on database modeling. One was posted in the SQL Server Central forums, and a customer asked about ERD tooling on the same day. This came shortly after Redgate acquired Vertabelo (now Redgate Data Modeler). This stood out to me as very rarely in the last few years have I found people consulting and updating a diagram while performing database development.

When I started as a developer and needed to update a database, I had to first update a diagram that was stored in ErWin. We had a dedicated computer (back when we went to an office every day) where the software was run and any developer could us this to update the diagram with proposed changes. Back then, we had to get another peer to sign off on changes before making them, and the peer was supposed to go check the diagram for the change before approving it. That’s only if they thought your change made sense and conformed to our standards (naming, design, etc.).

Over the last decade or so, it seems there haven’t been a lot of common tools for building diagrams in the Microsoft space. The SSMS Database Diagrams haven’t been the easiest to use and I rarely see people consult it. I loved ER/Studio, but it and ErWin were very expensive and outside the budget for most groups. There have been a lot of smaller online tools, but I don’t see a lot of consensus for which ones people like. There are DbSchema and SqlDBM, among others. I see various blogs, like this one, that recommend different tools, but the uptake seems to be low in many organizations. I hope Redgate can make data modeling more common with Redgate Data Modeler (coming soon to the Toolbelt).

My question for you is do you still maintain and use database diagrams for development? Or is this a somewhat lost art? Is it not necessary or useful for you? Do you wish your development teams did this to ensure a more cohesive and consistent model?

I used to like having a diagram because it a) forced me to slow down and rethink things a bit as I made updates and b) let me see where there were dependencies. One of the reasons SQL Dependency Tracker exists is that I asked Redgate to extract the dependency tree from SQL Compare to allow me to see it separately. This would allow me to know what other objects might need to be changed if I refactored ObjectA. I found that to be a great “picklist” of things to check during database development.

I don’t know how many people these days perform data modeling, but I do know a lot who don’t spend much time thinking about the changes they make. I fear data modeling has become a bit of a lost art, which saddens me.

Steve Jones

Listen to the podcast at Libsyn, Spotify, or iTunes.

Note, podcasts are only available for a limited time online.

November 24, 2025
Being Mindful of Design Time

Over the last few years, I’ve worked a lot with various customers on finding better ways to build database software, often using the principles of DevOps to drive the change. A lot of managers and leads want to see a smoother process to help their teams become more efficient. DBAs often want less overhead and friction in the process, while developers just want to deliver code.

In many cases, however, what lots of management wants is speed, and they’re looking for ways to increase their current speed and deliver more software. Their current rate of development might be quick enough if you can reduce your bottlenecks. Making communication easier, limiting the slowdowns from handoffs, and reducing the risk of mistakes are everyone’s goals.

However.

What I see too often is that both their current process and the new one are often lacking fundamental database design and modeling. Developers aren’t well-trained and make design decisions based on an (often) incomplete spec. They alter schemas to fit the immediate challenge, without thinking about the future. Good database modeling considers the often unasked questions and unspoken rules of the problem space.

Moving to a smoother process that allows code to be merged, tested, and released in a quicker fashion is great if you are writing good code. It’s less great if you aren’t. Most of us have a lot of poorly architected schemas and don’t need more challenges from more bad designs or bad code.

I wonder how much time most of you spend on database modeling? Do you try to out more than one design? In this podcast, John Ousterhout talks about designing a system twice. He tries to get his students to come up with a second idea for a problem, which often brings out a better design. I wonder if this might not be a good idea for database modeling as well.

I’m sure most people do their best to build a good data model, but experience often teaches us that the way we decide on a table structure, data types, keys, indexes, and more changes as we learn more. Our experience can help us make choices that perform well over time and don’t limit flexibility.

Is design an important part of your development process? Do you have guidelines for your organization? Do you consult more experienced database people? Or to inexperienced people ask you to review their choices? Building a more efficient software development process should help you to move code to production more smoothly, and therefore quicker, but it shouldn’t be used to deploy more poorly written solutions. Take the time needed to implement well-designed and tested code, including your database code.

Steve Jones

Listen to the podcast at Libsyn, Spotify, or iTunes.

Note, podcasts are only available for a limited time online.

November 14, 2025