Tag: database design

Tables are a problem for anyone trying to grow and modify software. Views, stored procedures, functions, all of these objects are easy to modify, but when we start to deal with actual data, where we need to maintain state, we start to have issues with concurrency, performance, and more. These challenges can really stress the developers and administrators that work on large tables.

As an evangelist for Redgate Software, I talk with a lot of customers and potential customers about their database work. Almost all of the problems and difficulties they experience revolve around tables, especially as we seek to deploy changes with little to no downtime. In fact, one of the hardest problems Redgate has been trying to solve is how to make these changes easier for DBAs and developers. It’s hard, especially because the impact becomes greater as the table gets larger.

I ran across a post from Michael J Swart that looked at ways to alter a large table while keeping the data available for querying. It’s the start of a nice series that examines a technique, looking at the pros and cons. This seems complex, but there isn’t any magic to altering tables and ensuring the system continues to run. There really are a limited number of ways to perform alterations to tables.

These are hard changes, and need to take place across time, meaning you won’t get this all done in an hour, sitting at your desktop. Some of these changes might take hours, or even take place across days. Really complex changes that must synchronize with an application (or many), might actually sit in some limbo state where triggers keep data in sync for months.

There are different ways to make these changes, but how do you test which one works best? How do you ensure the correct steps run in the right order? You want an automated process that can be repeated. If I need to restore and retry my idea, I won’t want to depend on humans to execute the correct steps in the correct order. That’s a recipe for disaster. Especially when repeat the deployment to production.

As the changes you make to your systems become more complex, with multiple steps, and the repercussions for problems grow, I think it’s important that you have an automated process. A human might need to kick off parts, but really an entire batch of items should run automatically, in a reliable, repeatable fashion, without requiring a human to execute each step. This doesn’t mean some things are too difficult to completely automate, or that human’s aren’t involved (maybe for smoke test) but people should be involved as little as possible.

Steve Jones

Quite a few of us will work with application databases where we need to identify a particular user for working with the various parts of the application. I’m not talking about the authentication and verification of identity, but just having a token to identify a user once they’ve been authenticated. I don’t want to talk security here since most of you aren’t great at building these systems and should just implement an authentication scheme that’s been vetted (OAuth, Live, etc).

However, once you get a way of authenticating a user, how do you identify them in the database? Many people store a username, but there are issues with these. They aren’t consistent across systems, a user might find theirs taken in one application, and then needs a second one for the next application. This leads to the complexity of people needing a way to retrieve their username, which can be a whole separate set of application programming pain. Often the default is to move back to an email associated with the username.

However what does someone do when they don’t have access to that email? Therein lies the issue with using email as an authentication mechanism. I use my sqlservercentral.com email for many things, most of them related to this site. However, if I were to ever leave this job, then I wouldn’t be able to access some sites if I couldn’t remember the login information. Perhaps I shouldn’t be using this email, but there are some services that are related to both my career as a DBA/developer as well as my position here at SQLServerCentral.

Plenty of people have a separate email for personal use, but not necessarily many users of a generic application. There are plenty of people using their work email for registration at sites, assuming they’ll always have access to the application through a username of some sort. You might even change your email, deciding at some point that coolteenager@hotmail.com isn’t the one you want to give out for your kid’s school registration. In that case, if you’ve used that email at a variety of places, then what can you do?

PASS recently changed to using email instead of username, which is fine. I think more sites use email, but in our case, as DBAs and developers, are emails less likely to change? Should we be sure that our PASS email is always a non-corporate email so that we can keep membership as we change employers? I’d think username is more stable for people in technology, and it can be fun. I love my way0utwest moniker, and try to ensure I reserve it on many applications I may want to use.

There’s no good answer here, and there are issues with using either one as an identifier, but what do you think? Are there compelling reasons to choose one over the other? No matter which one you use, I think the need to have multiple ways for users to gain access to their account is important.

Steve Jones

The Voice of the DBA Podcast

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