#TSQL2SDAY – Data Modeling Gone Wrong

Here we are again, time for TSQL2sDay. This is actually the 72nd one, that is long enough to finance and pay off a car these days. This month our gracious host is none other than Mickey Stuewe (  BLOG  |  TWITTER  ) and she’s asked us to tell  a story about Data Modeling Gone Wrong. I have to say that I really no nothing about a data model going wrong. Now hold on I am not done yet. You are wondering WTH?! is this girl still sick? Has she lost her marbles? I’ve never had these issues because:

• Reasons
• Applications
• Egos

Reasons

First of all, you have to admit that something has gone wrong for a problem to exist. Now I know that my other two reasons can still fall into this category but we’ll save them for later. Reasons can be that you’ve grown or aged yourself into a black hole of data model hell. Here is an example.   When I worked in product insurance (read cell phones and everything else your carrier tries to sell you) the systems were designed way back in the 80’s. When building a system like that I am sure no one knew that by the end of 2014 more than 7 billion phones would be used around the world. So like any good developer/architect/designer, the primary key was….wait for it………cell phone number. When I first started with ABC, phone numbers were not getting recycled for about 3 years. That means if you disconnected service your old number collected dust for 3 years. When I left on average that was about 3 weeks. The other side of this coin is all of the carriers did the same thing and every device they sell you with connectivity has a phone number. This creates issues on 2 levels. First your PK getting recycled is unthinkable and second how do you tie things together for single accounts, how do you create a family plan? Wasn’t a bad model when it started but sure ended up that way.

Applications

I am 100% certain some of the worst data models I have ever seen were provided by an application or written by an application developer. Trying to keep all your relationships inside the application so I can’t possibly know what is going on in your database is just crazy talk. So why do application developers design databases? Because we let them. In the course of my career I have only met 1 application (java) developer who had a clue about databases. Currently my husband works with .net devs who design databases and do cute things in customer tables like only including FULL NAME as a column. I assume, that with all things this boils down to time, money and clearly a management lack of understanding. I give people a lot of slack for making mistakes, being new at something and having not fully embraced the engine.

Egos

Remember when I said you have to admit something has gone wrong for a problem to exist? Well, sometimes egos get in the way. You know there is that one guy who nods his head “yes” to every manager request; they love him, they promote him and they let him be on all the biggest projects. I have worked with a DBA Dev that was trying to prove how great they were so they over-normalized a database to death. Great news: when deploying stored procedures you were not allowed more than 10 joins; it took 6 just to get a customer name and address. Then there is the “it’s not my problem” problem. This happens when all anyone cares about is fast writes to the database so everything is designed to accept dumps of XML. When providing new services there will be a need to report at a minimum it’s growth rate and no one has mentioned to the business that getting data out of they fancy new system will suck, and no one wants to pay for a data mart. Generally I heard “well they can pay for a reporting database later” or “guess they should have thought of that.” For me, it is always a sad day when an EGO is running the team, creating the new system or, hell, just breathing in my air space. The stakeholders don’t know what you don’t tell them.   Really I’ve never seen anything out right die due to poor design, but I have seen a few things that make me shake my head in wonder.