Voice of the DBA

Tag: database design

  • A Domain for Data

    A domain is a set of possible values (among other definitions). I use this word a lot in my work, often with a problem domain (the thing you’re trying to solve) or the domain of possible values (like the US States and Territories list). That last one is interesting, as this is often the set of data we stick in some reference or lookup table to use in a form on a screen.

    There is a domain as part of the SQL specification, which I never knew about. I was reading an article from Joe Celko on the CREATE DOMAIN statement. This doesn’t exist in SQL Server and T-SQL, but it has been a part of PostgreSQL for quite a few versions. The article talks about the definition of a domain from a few very experienced database design people.

    What’s interesting is that this could be a list of values, but it could be an expression against which values are checked. For example, we might choose the domain of positive integers, which might require that the value passes some x>=0 where x is the value. That seems OK, though this looks like a CHECK constraint to me, which we already have.

    The article is a little light and doesn’t give many examples of how or why a domain might be needed or why it might be better than a constraint. Perhaps there are good reasons, but I struggle to think of any. Certainly I am cautious of tightly binding which data is allowed, especially when I find many businesses have rules that often have exceptions.

    Would you use a domain for any data in your system? Do you think you have business rules that are firm and set enough to add domains? Perhaps you do, and if you do, that’s great. I suspect many of you are like me in that you are careful of where and how many check constraints you use. Those can be very hard to change and remove when an exception occurs, so the fewer the better.

    Steve Jones

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

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

  • Your Biggest Data Model Complaints

    I’ve been working with databases for a long time and there are no shortage of things I’ve seen other people do that I don’t like. Sometimes I shake my head a little. Sometimes I might groan inwardly (hopefully not aloud), and sometimes I might make an effort to convince someone else to do something differently.

    Sometimes I’m really annoyed (or angry) and don’t even know what to do.

    I know that most people are trying to just get work done. They might rush through something and not do a good job, perhaps because of oversight, or perhaps they are naïve about the effects of their work. Maybe they have ingrained habits and are unwilling to change. Maybe there’s another reason (let me know if there is one).

    However, no matter the reason, it can be very frustrating to work on poor database designs. There might be other things that bother you, but today I’m focused on the data model. Do you see poor naming of objects? Are there problems with the way they structure their entities? A lack of indexes?

    What are your biggest complaints about the structures in your databases?

    While I am looking forward to your stories, I want you to be professional. We’ve all made mistakes, and there is likely some (most?) code we’ve written that we wish we could redo. Don’t embarrass anyone or any organization, but let us know which types of problems or anti-patterns are your biggest complaints. Bonus points if you can do it in a humorous story.

    Steve Jones

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

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

  • Poor Database Design Realities

    One of the interesting things that I see at Redgate Software is how idealistic our developers and engineers can be. They often build our database DevOps products with the idea that customers will use well-designed databases. The systems will have primary keys, foreign keys, defaults, constraints, indexes, and more. Developers will use coding standards, and naming conventions, and will understand what data is stored in tables. Not in every case, but often.

    After all, that’s how we build software at Redgate, as teams, sharing information, publishing documentation for others, and following best practices.

    It’s cute and endearing, and unfortunately, not often true. In most cases, I find databases built by developers, accidental DBAs, or even experienced DBAs to be full of inconsistencies, lacking constraints and keys, and even duplicating some indexes and forgetting others. I often joke during one of my presentations that the main thing people should learn is to add primary keys to their tables. However, I’m not really joking.

    During a recent design session on our masking technologies, there was a discussion on masking data in tables without PKs, which is a challenge. We’re working on it, and also on being able to mask PKs themselves, as some people use the PII data as a PK. This could be a tax ID of some sort, but could also be an email address.

    When one of our account executives (Rob Boswell) heard that we were enhancing our capabilities with regards to PKs, he joked that we will soon be “primary key agnostic.”  It was a great line, and in one sense it’s true. In another, it’s sad that we need to design tooling around such poor practices.

    The reality of the world is there is a lot of bad design, bad architecture, and bad code out there. I applaud those who work to improve things in their environment, am saddened by those who don’t (either improve code or their skills), and frustrated by management not supporting efforts to be better. At the very least they should support efforts to teach your staff to code things right the first time, which helps improve future code. The next best thing is to refactor and improve older code, which can help you spend less in the cloud, or run longer with the resources you have on-premises.

    The reality is the reality we are in, but that doesn’t always need to be our future reality. We can change the future, each of us, by learning to write better code and improve how we approach our work tomorrow.

    Steve Jones

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

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

  • Having Data Modeling Standards

    While working with a customer recently, they mentioned that they have certain standards for their objects. They require a PK, and it’s the name of the table with _PK added. They also have some standards, like CustomerName vs. CustomerNames for various data items. In fact, they have enough that they built a tool to scan their database code to ensure that changes to the QA and UAT environments adhere to these modeling standards.

    I wonder how many organizations have formal standards. While I’ve often tried to set some naming guidelines, I often haven’t seen anything (or created anything) formal enough to build a tool around. I would like to, and I think it’s a good idea, but it’s often something that isn’t handled in advance.

    One thing I do think is important is to use singular naming. It’s strange to me, as I’ve often thought of a table as a collection of Customers or Orders, but I get why singular makes more sense. I really started to embrace this more after a great design talk at one of the Denver Dev Days.

    In the talk, the speaker talked about naming a table as an entity, but then including the entity name in columns. For something like a Customer table, we might have CustomerID, CustomerFirstName, CustomerLastName, CustomerStatus, CustomerPreferredContact, etc. There could be FKs in here, such as ContactID or AddressID, that linked to the entries in those tables, but in general, using a name like this was fairly unique for each column, and it helped anyone know where the data resided.

    It was a lot of typing, and if you didn’t have a tool like SQL Prompt, that might be really annoying, but I found myself seeing this as a very logical and easy-to-understand structure that even inexperienced developers might be able to follow and mimic. That doesn’t mean there isn’t a need to understand how we want to model structures like names, addresses, and more, but it did seem better than having FirstName in some fields and fname in others. CustomerFirstName or EmployeeFirstName makes a lot of sense. It also prevents ambiguous columns in queries.

    I do think that good data modeling standards are helpful in organizations, especially as we allow more and more people to make database changes and our teams grow larger. I would like to know how many of you reading this feel. Do you think strong data modeling standards are a good idea?

    Steve Jones

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