Voice of the DBA

Category: Editorial

  • A Database Design Test

    I was watching a presentation recently on database design and the speaker talked about how he hires developers. These are full stack developers, for whom the database is a portion of their responsibility. One of the tasks he gives them is a short database design test, designed to get a rough idea of their knowledge of databases.

    The test is a short story, with a classroom/course/scheduling scenario. There are descriptions in some business terms, and the instructions ask the reader to to decide how to put entities together and link them. There is a sample unlabeled diagram with only a couple boxes. The diagram is meant to clue them in to the way to indicate relationships, and there are names of different structures in the test in bold. For someone experienced in databases, this would seem trivial as the entities are listed in bold, and the test is designed to be completed in 5 minutes. Extra points for not crossing any relationship lines.

    I found this to be a nice, short test to gauge a developer’s knowledge. The speaker noted that they didn’t worry too much about time taken, or the exact notation used in the digram. This is mostly a way to measure if an individual thinks in terms of entities and connections. This is part of a few tests used for a basic evaluation of how a developer solves practical problems, and avoids the trivia based examination used by many interviewers.

    I was intrigued since I’ve never been really asked to design anything and I’ve had quite a few jobs where that would be a portion of my job duties. No one has given me a scenario and asked me to produce an ER diagram. The most I’ve gotten is some theoretical questions on normalization, or what keys are. I wonder if I’m alone. For those of you reading this, have you ever had a design test of any kind in an interview? Can you disclose the types of questions or scenarios? When were you tested? It would be interesting to see if this is used by much of anyone.

    There seem to be so few ER diagrams in the real world, especially from vendors, who should always produce one for clients. I suspect that few people understand them, or even write them, even though they can be invaluable when trying to write reports and understand the relationships between different entities. Many ask, but could those people produce one? Or read it beyond realizing which field in table a connects to which field in table b? Let me know this week if you’ve been tested on your database design skills.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Handling Data Corruption

    I wrote about dealing with data loss recently, ansking what your plans are for a situation. Some of you might rely on digital systems, some rely on humans, and some just accept some data loss. All of those are valid responses, depending on your environment. Today I wanted to take another step on this journey.

    Imagine that one of your systems has data corruption. Any system you have, but pick one that might cause you some anxiety. Maybe you discover it from a failed query, maybe from an entry in the error log, maybe from a DBCC execution (I hope you use these). You have no idea how long the corruption has been there. Therefore, you don’t know how many backups are valid.

    What do you do? What are your plans? Certainly there could be data loss potential here. There will likely be some questions about why this wasn’t known immediately, and maybe you’ll experience some embarrassment in the moment. This will be a stressful moment in your career, and one with which you will want to be able to cope.

    The question today is to get you to prepare a bit and game the possibilities. Some of you might never experience corruption, but you never know, so it does make some sense to think in advance and anticipate the reaction you want to have in the moment.

    We always want to be prepared, but we won’t have actual preparation plans (scripts, documents, etc) for all situations. It’s much easier to think about the possibilities, rather that actually build plans for every possibility. Maybe you want to even discuss and debate them among your peers. Today’s question is good practice for the real situation, and great mental preparation for a real event. Plus it can be a bit fun to brainstorm, have someone shoot holes in your ideas, you do the same for them, and then come up with another solution.

    Steve Jones

    The Voice of the DBA Podcast

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

  • SQL Server is Lagging a Bit

    I don’t expect this to last, or I hope it doesn’t, but SQL Server is lagging behind. I ran across a short piece on the SQL:2016 standards, and I know these standards aren’t necessarily an important consideration for most of us when choosing a database. The standards are a compromise from many industry professionals working together. However, they do influence the vendors a bit. The way the list is written makes me wonder if Oracle provides more input and resources, or exercises more influence than other platforms. I hope not.

    In any case, I would expect that Microsoft to be able to make change and catch up quickly with their rapid engineering process. At least, they will if they want to, which means, do these things matter do you as a customer? Or maybe, do these things matter to the really large customers that use, or might switch to, SQL Server?

    I’m not sure I understand all of the changes to the standards, but row pattern recognition looks like some sort of LIKE-across-a-row feature. I know there are people that might need this, but I worry about the performance implications of doing this. As we move to larger data sets, perhaps those that come from sensors and other large data producers, this might be an important feature. Hopefully the implementation perform well.

    The one thing I expect is more JSON support. As much as I saw the value of XML and hated working with it, I see JSON as a better format, and one that developers use extensively. I do hope that SQL Server gets more extensive JSON support, though I expect plenty of people with a native JSON type to just start stuffing blobs in a column and spending lots of CPU cycles trying to de-serialize the values for queries. If this a native JSON type is an intermediate store before moving the data into a better format, I think this makes sense. However, we can’t prevent poor choices from database designers, and I would expect consultants to love this, as I’m sure more JSON creates more issues for them to fix.

    I am glad that date/time work gets some attention in the standards, but since I can never remember the actual syntax and values, I don’t really care. I’ll depend on SQL Prompt to get me through changes here, and then try to get all data in yyy-mm-dd format so I am not confused. LISTAGG is interesting, but I expect JSON to be used more. There are other changes, and I’m not sure how valuable they are for most data professionals.

    Adding small functions and features, even those that are used rarely, are ways that we can dramatically improve developer productivity. Looking back over a career, I’m glad that I don’t need to write sorting routines anymore. Using an ORDER BY or x.Sort() is a great time saver. There are certainly some poor implementations of some features, but we can often override and write our own implementations if needed. Or find workarounds that will meet our needs.

    I do hope to see the T-SQL language grow and expand over time, with regular enhancements that might help us work with data in easier and more efficient ways in future versions.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Classifying Sensitive Data

    Our databases store all kinds of data in them, depending on the purpose of your database. Most of us create tables and store data in response to some requirement or necessity in our organizations. Our decisions should be driven by good design principles, and I’d hope they are, but we do need to find a place to put all the data that our system will receive.

    Depending on the sensitivity or personal nature of data, we may have to alter the way we store data (perhaps encrypt it) or alter the security for parts of the system. This is almost an ad hoc, deal with it at that time task. The exceptions might be when I’ve had to conform to a regulatory statute, such as SOX, PCI, HIPAA, etc. In those cases, I’ve often had to ensure the entire database is protected in some way that ensures it meets the requirements of the statute.

    The time when I have had to think about individual columns of data is usually when building a development database where potentially sensitive information can’t be transferred to development machines. In that case, because of the effort of changing data, I’ll try to build scripts that change out individual columns and ensure that sensitive data doesn’t get copied. However, the data that may be deemed sensitive for one company, isn’t always classified that way for another.

    At least that’s been my experience. I shared some of this with the Redgate Foundry, who is running research into data classification. They’re looking for people to share opinions, but the project has me curious. I’ve always felt intuitively we could classify data in tables, but perhaps that’s too simplistic a way of looking at the problem. I know that legal groups struggle with some this problem with email and file server documents. Classifying the content in different ways is a challenge.

    Is it the same in databases? I’m not sure. I don’t know if there complex rules needed or if this is a simple problem that we easily solve and rarely deal with. I’m curious from those of you that deal with highly regulated industries. Is data classification something that you work with often? How do you decide the data classes and does this impact your administration of the database? If you don’t classify the data, do you worry about the sensitivity of the bits in your database? Let us know today.

    Steve Jones

    The Voice of the DBA Podcast

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