Voice of the DBA

Tag: database design

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

  • Understanding a Database

    I ran across a post that asked a good question, one which I want to ask you today: how do you learn about a database?

    I’ve run into quite a few databases in my career. Some were third party systems, like Dynamics and JD Edwards World. Some were databases that custom designed and built by developers and database modelers of widely varying skills. Some were well built in order to normalize data and define referential integrity, and other databases were put together in a piecemeal fashion over time, lacking keys and consistent naming. I’ll leave it to you to guess if there were more of the former or the latter.

    When a developer or DBA comes across a database, what’s the way that they can decode what fields and columns mean? Certainly names help at times, especially when the purpose of the database is understood, but all too often the names don’t quite make sense. This is especially true in many vendor databases. The one common theme I’ve seen in many databases is that there is no data dictionary provided by anyone.

    Trying to understand a database has been a trial and error detective task for me in the past. Usually this starts when I need to do some work that is requested by users: write a report, change data, etc. In these cases, I often will ask users to access certain data related to the change from their application while I run Extended Events and note which entities are accessed. I can then start looking for data elements, and note which columns might be mapped to which fields in an application.

    Often I’ve built a data dictionary of sorts outside of the database using something like ErWin, ER/Studio, or another tool. That has been somewhat flawed, as it’s hard to share the information with others. These days I think I’d make extensive use of Extended Properties to document what I learned, so that all my knowledge is available for anyone else that needed to work on the system. They can just look at the properties for various entities.

    If you’ve got other methods, share them with us today. I’m sure there are plenty of DBAs and developers out there that would like some tips and tricks for decoding a database design.

    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.

  • Modeling and Design Are More Important in DevOps

    I’ve run across a few customers that are adopting DevOps processes for the database. This makes sense as I deal with Redgate Customers, many of whom are doing this. As they adopt a Compliant Database DevOps process, it seems often that there is a push for database developers to react to feature requests quickly, at the same rate that the application developers do, and push out new code regularly to support changes. This new code often includes schema changes to support new features and functions, which often means table changes because the data elements need to be stored somewhere. This, of course, means the data model changes.

    In many cases, trying to release a new feature today or tomorrow, or even next week, means making quick decisions. As an example, if we capture lots of cost and price information in an application and are enhancing this to add currency values, we’ll be adding fields to any table that stores financial data. How we do this could vary. It’s easy to add a currency lookup field to all tables, and that might be what many application developers want to do. If they’re using an ORM, they might just add this as a property to their object definition, which generate a series of ALTER TABLE statements to add the related fields. Certainly, a database developer could just generate those same scripts.

    Whether this is the best choice, or if some more normalized structures are needed, is unclear. That’s dependent on the problem domain, and if date stamps are needed to capture currency differentials at times, or even if we need additional FKs to ensure there is referential integrity, many junior developers and DBAs might not think about the implications to the data model. As time passes, this could mean additional technical debt to deal with, limiting future enhancements. This could also mean fundamental flaws in how financial data is calculated, potentially opening impacting revenue if the data model doesn’t support accurate calculations.

    Moving to DevOps doesn’t necessarily mean moving fast. It can, but it’s really about making focused, small changes at the rate that matters for your business. It also means that your data model and design of data store structures becomes more important than ever. While we can make decisions quickly, this takes experience and understanding of the business impacts, as well as the potential downfalls from different types of structures. DevOps asks us to give feedback about potential problems up and down the software development pipeline, which should include a data architect of data modeler. Someone with experience here can help to consider future implications and even provide some flexible designs that can adapt in situations where we have incomplete knowledge.

    Since we build our software on the data, we need to ensure we are properly capture the data in a way doesn’t create too much technical debt. There are many, many stories of organizations that struggle to grow their applications over time, often because of very poor data models. Many of these issues could have been avoided by consulting with senior developers, DBAs, and data architects/modelers for an hour before making a fundamental change. Even if this means keeping a consultant on retainer. The investment in reviewing and understanding the data model can pay off tremendously in the future, especially as the cost of data processing is often one of the larger costs of running an application. Whether this is an RDBMS like SQL Server or an alternative structure like CosmosDB. A little investment in modeling early can prevent the need to over-provision resources later.

    Steve Jones

    The Voice of the DBA Podcast

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