This month, the wonderful Pat Wright has provided the invitation for T-SQL Tuesday and he entitled it Risk vs Reward. His question was “Risk vs Reward” and covered a lot of things like designing indexes or choosing where to store a column, or even whether you should put new data in an existing database or create another one.

But I latched on to the bigger question:

In the database world, we have to make some very tough choices. This T-SQL Tuesday asks the question. “How do you weigh the risk vs the reward of your database design decisions”

I have been a fan of database design since I wrote my first book back in the very early 2000s. (Don’t buy it, look for the newest version…the first one was just ok). And the more I learn and live with designs, the more I realize that it is a very complex yet simple task to create a database.

There are a long list of factors that go into implementing a database design. Performance, cost, security, skills, etc. But there is only one answer to this question for me when it comes to what is the most important answer.

That most important thing

In the movie City Slickers, there are two memorable lines that have stuck with me for many years. The first is in regards to setting up a VCR, This has nothing to do with the point of this blog, but it is funny. And if you ever tried to record something and watch something else on a VCR, you know what I mean.

The second thing is the character named Curly’s One Thing speech. The secret to life is one thing. (This is a G rated blog, but look it up if you want.) But it is basically the type of thing I would say to anyone designing a new database, a new column, a new stored procedure, an application, or even baking cookies.

But my biggest decision is to make sure the data will be as correct as possible at all costs (that I am allowed to spend, time, money, whatever).

Without correctness, you are in trouble. If your database is incorrect enough, securing probably isn’t even a big deal. In fact you might just confuse the hackers more if it is as bad as some databases I have seen. (Kidding, mostly).

My thought process

One of Pat’s questions was:

I would love to hear about your thought process, experience, or a lesson learned from a decision that has impacted you for a long time. 

When you work at the same company, in the same general area, every mistake you make comes back to you. This is especially true when you are a database programmer because it is the foundation of everything that is done. Changing your data structures is very hard. A design you implement one day is a design you may live with for the rest of your life. You feel it pretty much every time you write a query and you have to take even one extra step.

An example

As example from long ago, we used to store images for ads at the job I worked pre-Y2K. It was a pain at times making sure that the picture that went along with the ad were right, because it was necessary to ensue that the URL that was used matched the location of the files. (There wasn’t a really easy way to store images in the database at the time.) Any time there was a failure in the images, it was always attributed to the database, but it was always the soft link between bits of data.

But this made me think, should we have stored them in the db even then? Maybe. But if I was still doing that job once filestream and then filetables came around in SQL Server, definitely. And if I was a creating database system to hold security photos today, I probably wouldn’t even use filestream. Making sure that the file was not tampered with is far simpler in a physical database, probably with some form of checksum/hash to make sure the image being served was the exact same bytes as it started out as.

Conclusion

In conclusion, ensuring data is as correct as possible is how I make any choices. Do I get my way all the time? No, because other forces do come into play, like time, money and all. Little things you know.

If I am on support and there is an issue, making sure the data will be correct when everything is back up and running is the most important thing… even if it means a bit less sleep.

Oh yeah, and just because correct is most important, it doesn’t mean all the other things aren’t important, too, In fact, security is one that you can argue is so very close to importance as to require close scrutiny to see the difference. But so is performance, and usability.