Mickey's T-SQL Ponderings

Bradley Balls  (b|t) is hosting this month’s T-SQL Tuesday blog party. The party was started by Adam Machanic (b|t) in December of 2009.  The topic this month is Second Chances. Bradley has asked us to write about something we would like to have changed if we were given a second chance. I’m going to write about something that I actually was given a second chance to do differently.
Setting the stage

[Neo sees a black cat walk by them, and then a similar black cat walk by them just like the first one]
Neo: Whoa. Déjà vu.
[Everyone freezes right in their tracks]
Trinity: What did you just say?
Neo: Nothing. Just had a little déjà vu.
Trinity: What did you see?
Cypher: What happened?
Neo: A black cat went past us, and then another that looked just like it.
Trinity: How much like it? Was it the same cat?
Neo: It might have been. I’m not sure.
Morpheus: Switch! Apoc!
Neo: What is it?
Trinity: A déjà vu is usually a glitch in the Matrix. It happens when they change something.

The Matrix (1999)

First past

This is an ironic topic. Today is the last day at my current job and the topic I’ve chosen to write about has to do with one my first assignments four and half years ago. I first arrived at my company in January. Do you know what happens in January? The Sales Team has a new structure that needs to be applied to all of their reports as of yesterday. That particular year, they added a new layer to their hierarchy. The database model couldn’t handle it and neither could the reports. I proposed a new model using recursion, both in the database model and in the reports and it was approved. It proved to provide flexibility in the years to come. It had one flaw remaining though. It had maintained the current practice of assigning Clients to Sales People. That doesn’t sound too bad, until you know that when a Sales Person leaves, all of their Client records have to be updated… one by one by someone in sales. It also caused problems when there wasn’t a Sales Person available to assign to the clients right away.

Changing direction

This past January I had an opportunity to improve upon my original design. I simply changed directions. In the past, each Client had a Sales Person and each Sales Person had a Territory. Now, each Client has a Territory and each Territory has a Sales Person. If someone leaves, only ONE Territory record needs to be updated with a new Sales Person. If a new Sales Person is not available, then the Territory still shows up in the reports. This change was completely transparent to the report users.

I created a sample database model to show the relationships.

Data Model Created in Erwin

The cherry on top

The best part came a month after the new model was implemented. The Sales Team needed to have a single Sales Person represent different Territories in different Parent Territories. That was not possible with the old model. A Sales Person could only have one Territory, but with the new model it was possible… and it was already in place.

Thanks for all the fish

Thanks go out to Bradley Balls for hosting this months T-SQL Tuesday blog party. Please visit his website at http://www.sqlballs.com.