Mickey's T-SQL Ponderings

This month marks the 72nd T-SQL Tuesday.  Adam Machanic’s (b|t) started the T-SQL Tuesday blog party in December of 2009. Each month an invitation is sent out on the first Tuesday of the month, inviting bloggers to participate in a common topic. On the second Tuesday of the month all the bloggers post their contribution to the event for everyone to read. The host sums up all the participant’s entries at the end of the week. This month I’m the host and the topic is …

Data Modeling Gone Wrong

The purpose of SQL Server, is to make sure that the databases are kept safe and run as optimally as possible. The problem is, if the data model is flawed, or not maintained, then no matter how optimally the SQL Server is configured, the database won’t be able to function efficiently.

Below you will find the summary of the 19 posts from this blog party.

Summary

Note: This is a heap. The summary is based on the order received… Well, except Ed’s I inserted him at the top. You’ll see why.

Ed Leighton-Dick
SQL New Blogger Challenge: Week 2 ideas
I found several posts in my comments that simply reiterated my invitation. I didn’t list them below. I am, however listing this one. Ed is challenging new bloggers. I love this idea. He not only broke down my invitation for new bloggers, but gave some ideas on how to find a topic that will fit into the prompt. Thanks go out to Ed for encouraging the “next generation” of SQL Bloggers.

Thomas Rushton – The Lone DBA
T-SQL Tuesday #72 – Data Modeling Gone Wrong
He wrote about generically named Fields and overloaded Fields. This is a very important topic and I’m glad it’s at the top.

Robert Pearl
T-SQL Tuesday No.72 – Is Your Data Model, Normal?
Robert started off his post with, “Well, I’m not a data modeler, nor do I play one in the corporate world.” He then goes on to tell us how important data modeling is. Finally, he goes over the basics of the 3 forms of normalization, which he feels everyone should know. (And I wholeheartedly agree with him.)

Chris Yates

T-SQL Tuesday #72 Invitation – Data Modeling Gone
Wrong
Chris, one of my dearest friends, wrote a fabulous topic on what you should think about and do prior to creating that first table. He then went on to talk about some good general practices to keep in mind while creating the data model.

Tamera Clark
#TSQL2SDAY – Data Modeling Gone Wrong

I really liked Tamera’s approach to the topic. She goes into the realities of teams not admitting there are problems due to

• “Reasons”
• Applications
• Egos

I’ve been there, seen that and have the t-shirt.

Andy Galbraith
T-SQL Tuesday #72 – Implicit Conversion Problems

This is one of those topics that people, who don’t write SQL, often forget about. Implicit conversations and how they impact queries. This is a great read.

Tim Peters

That time a beer broke my database – T-SQL Tuesday
#72

Tim has a great example of one of his data modeling experiences about finding out (after the data model has been deployed) that another table to hold multiple breweries was needed.

Side note: He has a great website, Beer Intelligence Project, where he has documented and visualized new beers. I think he probably enjoyed the research tremendously too.

Rob Volk
T-SQL Tuesday #72: Data Modeling
As usual, Rob tells a great story. This one is a fictitious story about how a small ice cream store grew over time. It’s told from the point of view of the database. The database started as a small database and grew until paralysis hit, I mean performance issues hit.

Steve Jones
Poor Data Modeling – T-SQL Tuesday #72
Steve also has a great topic about data modeling morphing into a difficult model. I’ve worked with the same data models that he describes here. That of a hierarchal data model that can get out of hand if it is not planned properly. The moral of the story is, when a data model is being designed, be forward thinking.

Mike Fal
#TSQL2SDAY: You’re Gonna Cluster that GUID
Mike attacked a very controversial data type: The GUID <sinister music inserted here>. This is a great topic to add to this collection of posts. Mike makes a great argument on why GUIDs should be indexed. He also specifies a specific use case for it.

Aaron Bertrand

T-SQL Tuesday #72 : Models Gone Wild!

I think Aaron and I were separated at birth. I preach the same list of “database sins”. In Aaron’s post he goes over two categories of “sins”, bad names and incorrectly used data types. I think the only thing I would add to his list would be to not use the default SQL Server constraint names. Give them meaningful names!

Kenneth Fisher
Why are you still using datetime?
Kenneth brings a great question to the table. Why are you still using datetime? He goes into the different date data types that are available and why we should be taking advantage of them.

Anders Pedersen
T-SQL Tuesday #72: Data modeling gone extremely wrong
Anders gives a good example of how an over-normalized database can slow down your application.

Terry McCann
Tally/Number tables in SQL Server #TSQL2sday
Terry took a different approach. He looked at the prompt from the point of implementing some best practices instead of identifying bad ones. He wrote about how Tally/Number tables can help with queries to find missing data.

Rob Farley
What’s driving your data model?
Like Chris Yates, Rob wrote about considering the data models purpose BEFORE creating it. The difference between the two posts, is Rob took it from the data warehouse data model point of view.

Malathi Mahadevan
TSQL Tuesday #72 – Data Modeling gone wrong
My good friend Mala, had a great topic for her post. She talked about the “one table to rule them all” pattern that she encountered at one of her jobs. I really liked one of her sentences in her summary, “The big lesson that came out of it was that the size of the schema or fewer tables do not really mean a simpler/easier to manage design, in fact it can be the exact opposite.”

Sander Stad
Data Modeling Gone Wrong
Sander painted a story of XML woe. I want you to be prepared when you read his excellent post. He has an image of an execution plan that will make your hair stand on end. Make sure you are sitting down.

Jens Vestergaard
#TSQL2SDAY – Data Modeling Gone Wrong
Jens has a horrifying tale of bad field name combined with the lack of foreign keys. I may have bad dreams about this one tonight. He does have a great reaction GIF that summarizes how any of us would feel about being faced with the database he describes.

(Update…I forgot to add my own post. Oopse.)

Mickey Stuewe
T-SQL Tuesday #72 – Bad Decisions Made With Surrogate Keys
In my post, I go into when you should and should not use Surrogate Keys as primary keys. I then give a case on how surrogates can cause duplicate data in a many to many table.

Thanks for all the fish!

First, I would like to point out how cool this blog party is. Even though Adam lives in the US, this is not just a US blog party. It’s international. In my list I have posts from The Netherlands, the UK, Denmark, and Australia. I think that is really cool. The one thing that I would like to see is, more women joining the blog party. Including myself, there were only three women who participated. While I’m very happy that Tamera and Mala joined the party, I would like to see more in the future.