Tag: database design

One of the ways in which developers become better at their craft is by building software. They tackle a personal project or maybe modify someone else’s code. They may work through exercises, solving problems like the Advent of Code. As with many tasks, practice makes perfect.

Database developers need to do the same thing. To grow their skills, they certainly need to find ways to improve their skills. That means tackling some sort of database project, which is often inherently more complex than a software project. Beginning code projects might ask you to reverse a string. Certainly a T-SQL project might do that, but what about storing a set of data for strings? What about setting up a server and database? There can be a lot more to database development than the programming code.

I do think that learning to model data, and store it in an efficient, useful fashion, is a skill. Whether in a relational database or a NoSQL store of some flavor, you need to find a way to not only solve a problem, but get feedback.

Today I wonder if you have a thought on a good beginner project? I found a list, but are there any that you think will help a junior database developer learn how to built entities and relationships?

Maybe more importantly, how do you evaluate a good design? How do you get feedback, or test and evaluate your work? Often many of us make choices in the real world, and we may not learn how well they actually work until lots of code is written and an application stresses our design. Then it might be too late, or very difficult, to change things.

If you’ve got ideas, exercises, or even ways to share designs, let me know today.

Steve Jones

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

I ran across a neat post from Michael Swart recently. In doing some spring cleaning, he was looking to remove unused, or maybe unnecessary, tables from his database. He published a script that looks through the plan cache to determine what connections exist between tables. He joked that if he doesn’t find any joy when examining a table, he drops it. At least I hope he was joking. If not, I suspect we’ll see a note on LinkedIn soon.

Actually, I’m sure he’s joking, and he makes a good point at the end of the his post. His script isn’t used to make decisions, but rather it provides a place to begin to investigate more about what the table might be used for in an application. It provides a starting point for more questions, such as is there value in removing this table. His company is embracing AWS, and they are becoming more cost conscious. This means keeping less data, and possibly moving cheaper data stores where possible to reduce license and/or hardware costs.

I suspect that other organizations will start to embrace similar attitudes as more move to the pay per month model of the cloud. Many of us rent hardware, and when we do, the recurring costs become an issue. Suddenly we might rethink the amount of data we keep and archive or remove older data, either to reduce costs, or risks. The GDPR brings with it a push to not keep all data in perpetuity.

The goal of better understanding our databases, and specifically tables, makes a lot of sense to me. Far too often I’ve inherited some system and only understood portions of the database. I’ve performed hours of investigation to try and better comprehend how data is stored, retrieved, and manipulated. I’ve found my share of unused tables, often renaming them for months and eventually deleting them.

I do think that we poorly document databases, almost treating them like a file share where we drop a new item when we need it, without thinking through the usage, ensuring others know about the entity, and often forgetting it exists if we don’t regularly use it. A RDBMS isn’t a file share, or at least it’s not an inexpensive one, so we ought to be cognizant of the data we keep and trim unnecessary waste over time.

I empathize with Michael and would relish the challenge to review and trim old tables where I could. However, I also know that often there are tables that won’t “make a difference”, either in cost or any other savings and aren’t necessarily worth the time to investigate and remove. Spending even an hour to decide if I should remove a 10 row table whose purpose is unclear doesn’t seem like a good use of time. Unfortunately, I’ve run into plenty of those, which nag me, but really shouldn’t be something I spend time on, and I have to leave them be.

Steve Jones

You can hear the podcast here: http://traffic.libsyn.com/voiceofthedba/understanddb_50_v2068.mp3

I read a post recently that captured my attention for more time than I expected. The piece fascinated me, and I sent it to my son and a couple friends. In fact, reading it caused me to stop working for a bit as I thought through some of the concepts and ideas. It’s about divergence and convergence, and how you can use these ideas when trying to be creative. I recommend you take a few minutes and go through it.

Is this something we ought to do a bit in database design? Should we spend first some time on divergent, creative thinking for how to implement some new entity or design some schema? Do you ever do this in your job, especially in a team? Or do you just push forward with your first thoughts, relying on experience and instinct? That’s what I’ve often done.

We certainly need convergence thinking. We have to get things done and deliver software. Most of us are probably good at the convergent side of thinking and writing code or queries or configuring systems. Most of us probably do some amount of divergent and convergent thinking at the same time.

However, I’m not sure that’s the best way to tackle creative work, especially when building complex systems or working to solve new types of problems. There is some value in separating out the brainstorming portion from the actual, let’s write some code and see if it works. Getting a good list of potential ideas and spending a few minutes thinking about them before writing code helps us to avoid getting trapped pursuing into the first thing that works and possibly see other approaches.

In today’s world of DevOps, trying to move faster and get small chunks of work to the customer quicker, we are under pressure to do more convergent work, quicker. If we want to ensure we do model our entities well and follow good database design practices, we need to spend some time with divergent thinking without doing work, perhaps speculating on potential future design challenges in advance. If we periodically spend this time, then we can rely on our previous brainstorming when it is time to converge and get things done.

Steve Jones

The Voice of the DBA Podcast

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

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.