Voice of the DBA

Tag: data modeling

  • Designing for Teams

    Some years ago, I had to register for my medical benefits with Redgate. My employer had contracted with Anthem for health insurance, and I went to register myself. I found that not only could I not register myself, but there was already an account there with my tax number, and it was under my wife’s account. Many years ago, she had health insurance with Anthem through another employer. Anthem kept her record, which includes my tax ID. So today, in 2026, I have to log into my Anthem account with her ID to see my plan.

    That’s likely an older, legacy system, so we might understand that they didn’t expect two people to be primary account holders over time, right? Well, we also have a more modern tax software system to deal with. Let’s call the software “Chewit” to protect the reputation of the company. When we contracted with an accountant for services, they set up an account for me. However, my wife manages most of the money, so I asked them to add an account for her. Apparently, this “Chewit” business tax service only allows for one person in a household to have an account. This person has to manage the account, so now when my wife logs in with my account, I get a code, and I send it to her. If you think these are isolated, I have 3 or 4 other services that work like this, from well-known, global brands.

    The quality of data modeling and software development is depressing some days.

    A lot of what I see from customers at Redgate, often from people who want to use Flyway to smooth our their database change management, is that they have built similar solutions that often depend on A person (or A server or AN account) to work. Someone hasn’t modeled things well for teams. Even when we try to smooth out a flow, often I see the users don’t account for the complexities of teams working together. I think some of this is because a demo or a model often shows a happy path, and we all tend to think in that mindset of a simple flow. Managing parallel actions is hard, just like parallel programming is a thing many people struggle to do well. Well, they struggle to make the code work without race conditions.

    I see the same thing in T-SQL. So many people think in terms of singletons, working with a row of data. They don’t think in sets, or as Jeff Moden put it recently, about how to work with the entire column at once. Thinking that way is harder and requires a new mindset, but that’s why we get paid well. Or should be paid well. We solve the hard problems.

    The world is more connected, more complex, and more likely to require teamwork. Whether that’s between partners/spouses in a household or different groups of people in an organization. We should be designing and modeling for teams, not individuals, in everything from data storage and security to user settings and customizations.

    Steve Jones

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

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

  • Poor Names

    It’s always interesting to me when I give product feedback to engineers at Redgate on their demos. Quite often they’ve built a feature that uses AdventureWorks or Pagila (PostgreSQL) or some other well known schema to evaluate how their particular thing works with a database. I try to remind them that many databases aren’t well modeled and designed with consistent naming.

    I ran across a Daily WTF article that isn’t showcasing databases, but it does show some poor naming in data being stored in a PDF. The developer who had to automate a process had to map these fields to database fields, which also might not be named very clearly. In fact, I think I’ve seen a few database models that used column names like the field names in the PDF.

    Most systems we work on evolve over time. They aren’t built by one programmer, or one team, across any period of time. Existing developers leave and new ones start. DBAs change, and we often don’t have any code analysis rules enabled or running in CI that might help us with consistency.  Often we don’t even have our rules documented.

    Humans are amazingly creative beings, but they also get very uncreative when they have to repeat that creativity over and over. It’s why we find a neighborhoods in Colorado with Pine St, Pine Ln, Pine Rd, Pine Circle, Pine Way, etc. You get the idea. Someone got bored and didn’t want to be creative, so they just took the easy way out.

    The same thing happens in databases. I’ve seen people have a Customer table and then a CustomerDetails table alongside it (singular and plural) with a custDefault (case) and a tblCustContact (prefix) table that leave me scratching my head. Did the next developer not look at the database at all? Certainly they didn’t use any modeling tool like Redgate Data Modeler.

    I don’t blame others, since I’ve found myself struggling to be creative as well as consistent when I build systems. Sometimes I’ve got some automation running that reminds me to do better, but often I’m depending on another human to catch these inconsistencies in a code review. However, I’m not sure code reviews include looking at the name of the object for most people. Well, maybe column names, but that might be it.

    I’d hope an AI system could recognize poor names and then suggest better ones that capture the intent of the data being stored and look like the other objects in the database. Unfortunately, that will likely lead to a section in the database that looks amazing while all the older objects drive you crazy with their random nature. Maybe the AI can at least update the comment or description fields in code to ensure there’s some place to look for information to help us do better in the future.

    Steve Jones

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

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

  • A Poor Data Model

    Recently there was some online complaints about social security numbers (SSNs) in the US being duplicated and re-used by individuals. This is really political gamesmanship, so ignore the political part. Just know that social security numbers appear to be one of the contenders used in many data models.

    I found a good piece about how SSNs aren’t unique, and have a mess of problems. Despite this, many people seem to want to use SSNs as a primary or alternate key in their database systems. They also aren’t well secured in many systems, even though we should consider this sensitive PII data.

    As we have moved many analog systems to digital ones, we often find that our assumptions about the rules governing data aren’t that well defined. I’ve worked in many systems where data elements were assumed to be unique in some way, but they actually weren’t. I’ve seen invoices, POs, and other “unique numbers” actually duplicated because of simple mistakes by humans. When we try to enforce uniqueness in a database, we end up with problems. Often we actually need to drop keys and make exceptions because the data must be entered.

    This has led many people to not create unique constraints or even foreign keys in their systems, and I understand why they don’t. There are real problems when we assume the real world has the same strict structures we implement in code. I’ve seen systems go online and then FKs removed because of poor data quality. It might be a mistake, but it’s also a reality when we find there is existing child data without a parent. We might create a pseudo-parent at times, but we might also decide not to do so if that creates other problems.

    I suspect over time the real world will migrate some of their problematic keys to something more robust. However, some that are used widely in older systems, like SSNs, are unlikely to change in my lifetime. There are far too many places where this is in use and I am not sure that there is any consensus to undertake the massive amount of work to implement something else. I suspect even trying to add digits to the value is a task we’ll put off indefinitely.

    Be careful of using natural keys in your data models unless you are sure they are really a natural key. To me, I’m better off with some surrogate key in the event that my “natural key” turns out to not have the uniqueness I expected.

    Steve Jones

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

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

  • Do You Still Use CHAR?

    I ran across an article, titled When to use CHAR, VARCHAR, or VARCHAR(MAX), which struck me as something I never do. I mean, I do use varchar (and nvarchar), but I can’t remember the last time I actually created a char column. The article is worth a read, and it sets the stage for you to think about your database design process and the choices you make.

    It’s Friday, and if you take a few minutes and think about the last few times you’ve added columns to a table have you used CHAR as a data type? Or do you default to varchar of any size as a general rule?

    I tend to do a lot of demo work, and I help customers with different situations. In many cases, we are storing text data, often not strongly typed data. As a result, I find most customers using varchar (or nvarchar), and I’ve built the habit of using the variable structures in proofs-of-concept and demos. I find it especially handy when someone asks me to enter some data they use and then show how it would be handled.

    Early in my career, I’d often tag a zipcode as a 5 character field, or a state as a 2 character abbreviation. However, these days a postal code can be a 5+4, which is 9 characters or even 10 with the plus. Many companies work overseas and may want to account for longer postal codes. States (or regions), can often be 3 characters, but sometimes more. Often we just leave 10 characters for region abbreviations (or longer) as the data might be spelled out or need to accommodate something unknown.

    For many business applications, it seems that there might be a definition for what the data should be, but since exceptions can abound, often using a variable-length data type just prevents issues in the future. Add that to the fact that often we are dealing with cheap storage, and it doesn’t seem worth the time to try and get the exact size correct. Even when knowing an invoice uses 10 characters, are you sure that you won’t exceed the ten-character width? What if you acquire a company that uses 12 character invoice numbers? Easier to set this to a variable 20 and move on.

    If you work in data warehousing, then you might know what your data sources contain and be more likely to choose fixed types, but is the space savings worth the work in the event that source systems change? I don’t know. I tend to plan for sources to change and allow a little padding in my schema. You might feel differently, but are the space savings worth the potential hassles in the future? I’d be curious what you think today.

    Steve Jones

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