Voice of the DBA

Tag: software development

  • 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.

  • When Work Isn’t Done

    Software development can be a challenge for each of us with lots of demands and the need to ensure your code solves a problem correctly, efficiently, and completely. Juggling the workload by yourself is one thing, but add in a team of developers, and the complexity quickly grows.

    The real world is chaotic and despite the best efforts of project managers and scrum masters, our software development life cycle doesn’t always proceed smoothly. I wonder how many of you run into this situation and how you deal with it.

    Developer 1 gets a piece of work, let’s call this A. They complete this and send it to the QA team. Somewhere during this process, Developer 2 get’s a different piece of work (B) and writes code. They send this to QA before A is completely tested.

    Now, Developer 1 finds a mistake. Something doesn’t work or they realize their solution is incomplete. QA is A + B, but A doesn’t work and needs revision. B passes testing and needs to be deployed. If your codebase and QA have both A and B in them, how do you strip out A or B and ensure B is deployed to production but A isn’t?

    If this is C# or Java, you might have one solution, even if both changes are in the same class. If this is database code, you might have a different set of issues to deal with.

    Really, the question is can you reorder work in your deployment process? I find many customers don’t consider this when they are evaluating their software pipeline. They somehow assume if code gets to QA that it’s good, which is nicely optimistic but not realistic. At some point, we’ll deploy code to QA that doesn’t work. The more developers we have, the more likely this is, and the more demands on our time, the more likely we need to reorder work and release one thing but not another.

    As a DB developer and DBA in a company 20 years ago, I built a process and forced us to reset the QA environment and redeploy B only (using a branch of code that stripped out A) for re-testing. This ensured that we tested what was going to be deployed. However, I find a lot of organizations can’t do this or don’t want to. They want to hope that a human and either extract all of B or strip out all of A and release partially tested code without issues.

    I find that to be a poor idea. In this era of regular staff changes, staff of varying quality, and the high complexity of software, this is asking for mistakes. With cheap hardware, virtualization, and the ability to provision copies of environments, we ought to do better.

    How do you handle this today? Depend on humans to not make mistakes? Hope for the best? Or follow a repeatable, reliable process that accounts for inexperience and human error?

    Steve Jones

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

  • Bad Culture Bad Security

    I ran across a blog noting that Cisco has a vulnerability in a new product. The blog also lists two (one, two) articles showing that Cisco has had hard-coded credentials in the past. I understand that many times a known process is repeated, essentially copy-pasted between people, and we have similar issues as we have had in the past. However, in 2022 or 2023, it’s unacceptable to hard-code credentials in digital systems that will be used in today’s world.

    What’s worse than having this issue is stating that the fix is “an upgrade”. Their verbiage for those without a service contract is: “Customers should have the product serial number available and be prepared to provide the URL of this advisory as evidence of entitlement to a free upgrade.” Which, to me, is not only bad for the world, but it’s equivalent to the stuff that bulls leave behind in the fields.

    I suspect that this product and software were based on something that already exists, and it was rushed out without a good security evaluation. Or perhaps there are developers and managers who don’t think that hard-coding credentials will compromise security.

    That’s a cultural problem. Either you’re going too fast, or you don’t take this seriously, either way, you don’t have a good culture that values quality and protection. Certainly, their disclaimer about needing valid contracts or proving you have a device to get a security patch for a flaw they built is also a sign of a poor culture that doesn’t really understand the problems they are creating, nor are they taking responsibility for the issues.

    At the very least, fixing their poor security ought to be free and easy. I get that there are likely some software upgrades included in this patch, given the nature of software development and limited branches under support. However, there are other ways to ensure those features aren’t enabled for customers who shouldn’t have them. Making the entire world of computer network infrastructure less secure because you want customers to pay for your mistakes isn’t a model I’d want to adapt.

    I know many people don’t want more regulation or guidelines from governments (or even from insurance), but if I were going to accept some universal restriction, this would be the place. If you use hard-coded passwords, your product can’t be sold and no insurance claims apply if you are sued.

    Maybe that would change the way Cisco and others build software.

    Steve Jones

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

  • You Always Have a Software Pipeline

    I was working with a customer recently and they said they didn’t want to set up a pipeline. Somehow they wanted magically to see changes that developers made to their database appear in their production database without making any effort to build or configure a pipeline in a tool like Octopus Deploy.

    As I was thinking about how to find them a solution they would accept, I realized that I’ve always had a pipeline for software. Early on, this was a copy of all the files in a folder sent to the client for Clipper and dBase apps. Later, my pipeline was copying around all the VB6 files from a .zip file and executing a few SQL scripts that were sent to me in emails.

    At one point, I had developers just tell me the software pipeline was them copying files to a server while I had to move all the changes from the QA server to production. I didn’t like that one for two reasons. One was that the developers would sometimes forget to move a change from dev to QA, despite claiming they’d tested the application. Two was that the developers sometimes moved too much to QA, and had changes to the schema that they hadn’t finished in their app. SQL Compare couldn’t help me in either of those cases.

    Over the years, I learned that a slightly thought-out pipeline is better than nothing. I wrote my scripts to simulate what Flyway does today, executing the scripts I’d placed in a folder. I added logging that would capture output results and save them for later review. I learned how tools such as Team City or Jenkins could execute the same thing I could but in a more repeatable, reliable manner. After all, my reliability at 4:55 p.m. on Friday isn’t as good as it is at 2 pm on a Tuesday.

    DevOps is about learning and taking advantage of tooling. Look at what works in your pipeline and find repeatable, reliable ways of accomplishing those tasks without humans doing more than clicking a button to approve something. It’s about using your creativity to improve the process and codifying it in a way. It’s about being better than we were last week while ensuring the process isn’t dependent on any particular person. After all, I might leave for vacation.

    Or find another job.

    Steve Jones

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