T-SQL Tuesday is here again. I’ve had good intentions the past few times this event has come around and even have drafts still waiting to be queued up which I will eventually turn into regular blog posts, but I decided to just make time this month and jump back into the monthly party.

This month Mickey Stuewe (b|t) is hosting and has asked for some data modeling practices that should be avoided, and how to fix them if they occur.

What is Data Modeling?

Data Modeling itself is referred to as the first step of database design as you move from conceptual, to logical, to actual physical schema.

While that definition sounds simplistic, we can expound upon it to arrive to the conclusion that data modeling is a very important aspect from database design on all levels.

What to Avoid?

As a data professional and in senior management I’ve seen pit falls wide-spread in various business units when it comes to design architecture. The listing you are about to read are some of the methods and items I’ve discovered on my journey and conducting gap analysis type of events that carry a chain reaction. They consist of doomed failure from the get go.

1. Audience – the audience and/or participants should be defined up front. I differ with many and that’s okay. To me the ability to identify business stakeholders, subject matter experts, technical groups, BA’s is an integral piece to the puzzle. Too many times I have seen the engine pull out of the gates with a design to only find out that the design and documentation to not even meet the criteria and standards of the shop.
2. Detailed Project – how many times have you received documentation only to find out there was not enough meat to get the project off the ground? As a data professional we do think out of the box; however it is imperative to be clear and concise up front. When my team is given projects to complete that involve Database Design and creation, I implore business units to provide as much detail up front that is agreed upon. This helps streamline and makes for better efficiency.
3. Understandability – With details comes the ability to articulate understandably. All to often items get lost in translation which causes additional work on the back-end of the database. This could mean unfortunate schema changes, large amounts of affected data, and so on.
4. Business Continuity – ask yourself a question in design phase. Is what you are building that will be presented to the business efficient? Will business be able to decipher what is being presented back to them; if not why?
5. Downstream Analytics – How does the business want to see this data in the form of analytics or reporting?  Most modern systems are going to either be queried by, or push data to, ETL processes that populate warehouses or other semantic structures.  Avoid complex table relationships that can only be interpreted by the code that stores the data.  Make sure you define all your data domains so that the BI professionals are not scratching their heads trying to interpret what a status of ‘8’ means. (In speaking with a colleague, Tom Taylor, at my shop – he brought up this valid point).

Items To Look For

Some key and general practices to look at and decide on are:

1. Primary Keys – yes they are your friend – add them.
2. Look at all audit data and what needs to be audited
3. Clustered/Non Clustered indexes – have you read through your execution plan?
4. Has the scope of the data model been met?
5. Are tables normalized properly?
6. One Data Modeling Tool – it’s easier if the team is looking at one utility together; if you have many varieties spread across many team members it could leave views skewed.

Conclusion

Data modeling, in and of itself, is a key component for any business. What often falls by the wayside is the poor leg work done up front. You have to lay a proper foundation in order to be successful with any design; taking into consideration all personnel in order to make the best strategic decisions to move forward.

Hopefully the next time you go down this path you have some questions to ask yourself along with some solutions to those problems.

What is 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. If you are interested in hosting and are an active blogger than reach out to Adam and let him know of your interest.