Voice of the DBA

Category: Editorial

  • The Lost Time

    Last year when Daylight Savings Time moved clocks back, I was wondering how the time change might affect different devices and situations in the world. One area that I wondered about was medicate and health care devices. While I’m sure most (hopefully all) medical device software developers have thought about this, it was something that struck me this year as I was fighting a cold.

    I was taking a medicine designed to help reduce the length of illness and was supposed to take this every four hours. That meant if I took this at 10pm, the next dose should be 2am. However, if I woke up at 2:10am, it would actually be 1:10am since the clocks would have moved back an hour. If my clock adjusted itself, then it would be 3:10am. Not a big deal for my dosage, but for some people, this might really matter.

    Certainly software can adjust for this, but can we humans? If I’m dosed every four or six hours, common time frames, both me and my caregiver might get used to a schedule. We might assume that 7am, 11am, 3pm, 7pm is the schedule. We know that 7, 11, 3 repeat throughout the day. Daylight savings time changes that. Once the clock resets, I need to move to 2, 6, 10 the next day. Or to 1, 5, 9 depending on the time of year. It would be easy for someone to think they’d missed a dose and double dose, or assume that medicine was given last out and skip a dose.

    What about sales transactions? I’m sure this is a slow time of the day and year, but still, I’ll get a skewed view of that hour. Either zero sales or double sales. What about auditing? Is this the perfect time to break into a system or perhaps game the tracking of events? If I came and went from a facility during the hour in question, could I leave before I arrived? How could we actually capture a true audit record here?

    Daylight Savings Time might have been a good idea at some point, but these days I think it’s silly and causes more potential issues than it solves problems. I’d like to see it go away and have us stick to some consistent schedule throughout the year. Standard time, savings time, split the difference. I’m fine with anything, just don’t arbitrarily move my clock during the year.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.2MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • The Justification Database

    I saw an article on the security at various Department of Defense facilities that control the ballistic missile systems in the US. It’s interesting reading, especially if you want to shake your head at bad practices. I think this article summarizes just how quickly the digital world has grown faster than the ability of humans to keep up and adopt new practices, especially where constant staff turnover is the norm.

    This is a good read for anyone in an organization that must plan for security and account for new staff on a regular basis. It certainly made me think a bit about how information is presented, disseminated, and the view from the other side. It’s easy for me to consider security and focus on that from my position, but for others that are tasked with other jobs, their view is completely different.

    There was one item in here that made me stop and think. There is a section, titled “No database with written justifications”, that notes that there wasn’t a database of reasons why someone was granted access. Through random sampling, the found that lots of employees didn’t have forms filled in completely with justifications and approvals. In some cases there weren’t even forms. I assume these are digital forms, but they could be paper.

    That made me think about the ways in which I’ve granted access to individuals in databases in the past. Often in small companies the request is verbal or in email, with approvals made the same way. Certainly there’s no tracking. In larger companies there might be tickets in some helpdesk system, but how do I track those back to clicking in SSMS to add a user to a role?

    This week I’m wondering how well managed your system is for managing and approving security changes? If someone was concerned that a hacker gained access through social engineering, could you track down where, when, and why someone was granted access? Are you regulated or audited? I know some people have great processes that limit the potential for abuse, but I also know that far too many people use exceptions to get work done. I wonder how many of you allow for exceptions to your process.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.4MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • Ensuring Designs have Flexibility

    One of the biggest complaints from developers about RDBMSs is that they are inflexible and hard to change. This has led to many developers in the past using existing fields for a variety of purposes, sometimes putting data from multiple places into one field. In more recent times, this has led to adding or migrating data to new data stores, with the idea that the system can adapt easily to changing requirements.

    I ran across a post on system flexibility that somewhat illustrates the issue. The writing is a little hard to understand, but the author is thinking about the process of ordering food and how a system might need to handle some of the exceptions or additional changes. While the employees in a store might deal with this in an ad hoc manner, those of us building software need to think through the possible issues and account for (at least) some of them in code.

    Software typically has much more flexibility than we do at the database level, since anyone building software is usually building on top of the database system. However, those of us that design schemas need to ensure that we take care to consider the different ways that our data store will serve the needs of the end user and design in the proper structures needed to support the data that will be stored.

    In my experience, it’s important for the database developer to question the requirements of the customer. Just as a software developer might try and tease out additional features and functions that are needed, the database developer needs to carefully query the customer to ensure that there won’t be additional data that the customer assumes is being captured. We need to be aware of relationships and cardinality between the data elements, in order to better design a system that meets the current, and potential future, needs.

    Building and designing a database isn’t that hard, but it does take some attention to detail and a meticulous nature to think through how some real world situation is modeled. While I think small, frequent DevOps releases are a way to move forward, I’d also like to ensure that we capture as much detail as early as possible to minimize disruptions. After all, refactoring and changing the schema is harder in a database system than in an application software class.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.3MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • Fun with Dates and Times

    The other day I got a Github issue that asked if we here at SQLServerCentral should set our dates as YYYYMMDD in the new SQLServerCentral site. The issue noted that there were some inconsistent dates. Article dates are in a similar format, YYYY/MM/DD, but we do have some DD MMM YYYY and a few places where I think the American MMBBYYYY has lived on.

    In any case, I made a joke on Twitter of asking if people were alright moving to YYYYMMDD. Most people were in favor, though someone asked about MMMM DD, YYYY, which is easily read. I have to say that I somewhat like that for reading, but as data, as something I’d work with, I prefer having the year month day format. I do want separators, and while I grew up using slashes ( as in YYYY/MM/DD), I know the ISO format is dashes (YYYY-MM-DD). ISO 8601 has dashes, so I decided to add that to the GitHub issue as a way of formatting.

    What about using month names? As I look through various sites, I find inconsistencies. Some use dates, like Jan 11, 2019. Others might use 11 Jan 2019 or 2019-01-11. I find that MSDN uses regional settings, so the en-us version of a page will have 01/11/2019, but if I change to the en-Gb version, I see 11/01/2019. That seems find as a UI enhancement, but I hope that the actual value is stored as 2019-01-11. We don’t need them time

    Or do we? There are times involved. While most of us might not care about whether something was published at 8am or 9am, we might care about where it was published and the time. Certainly 8am in Christchurch if far different from 8am in Honolulu. Do we store time zones? Or keep all dates in one? The logical thing is to keep dates in UTC and then adjust based on the client settings to display something that matches their time zone. We’ve been storing data in UTC, though the server time has been the Mountain time zone in the US for historical reasons. I don’t like that dependency, so I’m sure we’ll change that.

    Dates and times are both simple and not simple. There are decisions to be made, and far too many of us take them lightly. While the display is of secondary importance, it’s likely that we’ll often use this data in other systems, as a way of marking a change, or even as a way of ordering our data. Having consistent and known date time formats is important as a data professional. I hope you pay attention to how you gather and store this data.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 4.3MB) podcast or subscribe to the feed at iTunes and Libsyn.