SQL University – Conceptual Modeling

Welcome to SQL University’s Fall 2011 Semester week of Data Modeling.  I’m thrilled to be a part of this great endeavor again, and a giant thank you to the man behind the project, Jorge Segarra (Blog|Twitter).  I hope you enjoyed Julie Smith’s (Blog|Twitter) SSIS Deployment posts last week, and are looking forward to Stacia Misner’s (Blog|Twitter) upcoming week of MDX.  I can’t even tell you how cool it is to be scheduled between these two amazing women.

So… Data Modeling!  Well, not quite yet. I want to take a step back and talk Conceptual  Modeling. Let me explain why. Every good IT person I know is a born problem-solver. It’s one of the reasons we love what we do. We are presented with a problem and get our thrills from finding a solution. But here’s the thing… We’re so enthusiastic about finding a solution that we occasionally jump right past understanding the problem at hand and just start doing stuff. In our world, the results of solutions to undefined problems can vary from the happy accident of an appropriate fix to a snowballing A Simple Plan-esque disaster.

I can assure you, they’re contemplating where to bury the server they just destroyed. It all started with dropping that index.

Conceptual Modeling sounds fancy. Kind of like metadata, paradigm, sommelier, or obfuscation.  You’ll sound impressive when you say it, but conceptual modeling really is far simpler than it sounds. It’s just a picture of what’s going on or what you want to see going on. For example, in trying to think of a non-technical example of conceptual modeling, it turned out that my 9-year old self became Exhibit A. When I was a kid, my room was usually a mess. So much so, that it was a little overwhelming when my mom would tell me to clean it up. My solution was to draw a map of my room, complete with furniture placement, break the room into “zones”, and assign each zone a number. Then, I’d clean one zone at a time.

Yes, this is a true story. And I'll admit: I was a pretentious little brat.

What was I doing? I was modeling my room. It allowed me to see what the problem was (the messy room), the shape of it, and where all of the parts were located. Given this, I was able to break the problem into manageable pieces and begin tackling them one at a time. That’s all conceptual modeling is:

1) What is the environment?

2) What are all of the parts (or subjects, components, etc.)

3) How do they relate to each other?

That’s it. That’s one example of a conceptual model. Is there a formal methodology? I suppose so. (http://en.wikipedia.org/wiki/Conceptual_model) But I want to get you thinking less about notation and vocabulary and more about the purpose of conceptual modeling. Bottom line, do what works for you and how your brain functions. Your primary objective is to draw a simple picture of the environment you’re in and the challenge in front of you. Also, step away from the keyboard. I mean it. I hear they still sell paper at office supply stores. While you’re there, grab a pencil or two. Or better yet, get a whiteboard and a couple of markers.

There are some real advantages to starting a development process with a conceptual model. First, you lay out the problem before you start mucking around with things. Second, you can use the model as a way to organize your thoughts and clear the clutter of available, but possibly useless, information. Finally, (and this is a big one) you can use the model as a way to communicate what you think the design path is. It isn’t hardcore design; think of it as problem definition. You’re not modeling to solve a problem – you’ll do that when you design the solution – you’re modeling to define the problem. The more well-defined your requirements are, the smoother design is going to go.

I usually end up with one of two kinds of models: Workflow (Point A to Point B) or Contents (what’s included in X?).

Conceptual Model #1: Workflow

Just last week, I had a work item that said to add all of the ISO 3166-1 Standard Country Codes to a collection of databases.  Sounds simple enough.  Except there were a few hitches.  Here are the requirements I had to meet:

1) Each database should be standardized – all ISO Codes need to be added to the Country table in each database

2) Not all databases have a Country table already, if there is one, use it.  If not, create it

3) For the databases that have a Country table already, you can’t remove anything that’s already there – existing columns and rows have to go untouched

4) For countries that are already populated in Country tables, you can’t modify any data, you can only add additional data (in the form of columns)

5) There should be one DDL script and one DML script that can run in any environment we have

Not insurmountable, definitely, but it took a little planning.  This is where a workflow-focused conceptual model came in really handy.  I start with Point A and Point B, then start filling in what it takes to get me from one to the other.

Let’s walk through the process…

  • What is the end state I want? I know what I want the Country table to look like – I write it down. I know that I need to keep my original Country and CountryName columns, and that I’m going to add CountryThreeLetterCode and CountryNumericCode.

    I promise, we'll fill in this whitespace
  • What is the current state? I have 3 possible scenarios

Note my clever visualization of "nothing"
  • What do I need to do to get from Current to End State? The biggest generalization I can think of is “some DDL and some DML” (I need to modify the structure of the database and then work with the data)
I know, call me Captain Obvious, but I promise, it'll make sense if you keep reading.
  • What kinds of things am I going to do in these DDL and DML scripts? This is where I officially turn my back on formal methodology – I make notes, put in whatever detail makes sense to me, and basically jump all over the place
See? Told you. 🙂
  • Which comes first, DDL or DML? I can’t add any data until the table’s in place, so I’m going to run DDL first, DML second
Color-coding - one of the loves of my life
  • Within DDL and DML, what order should things happen in?  DDL has a great example of how the illustration helps us think things through – if I create the table when it doesn’t exist and then look for the existence of the table to alter it, I’m going to try to alter the table I just created, probably generating an error – instead, I’ll address existing tables before I add missing tables
This is starting to look a lot like a to-do list.

At this point, I have a pretty good picture of what I want to do and what order I want to do it in.  Now, I’ll just go write the code for each of the items in my flow.  Easy-peasy, mac and cheesy. Keep in mind, I’ve made this PowerPoint-pretty for the purposes of the blog, but in real life, these were scribbles on a whiteboard. Informal and quick, it just helped me create a picture of what I needed to do and how to get there.

Conceptual Model #2 – Contents

Next up is a conceptual model that focuses on contents. This could refer to anything from “What does our server infrastructure look like?” to “What functionality is going into this system?” to “What are we going to store in this database?” to “What exactly is in a McRib sandwich?”.  There are all kinds of questions you can answer with a model like this. Knock yourself out coming up with ways to use a content-based conceptual model.

Let’s say that you added those country codes to your database, and your manager comes to you and says, “We were thinking… maybe we should add more information about the countries.  You know, country-type stuff”.  This is a perfect opportunity for a model that’s all about the information you want to store; you could stretch a little and call it the country’s contents.  There are a few things you can accomplish with a model like this:

1) Brainstorming
2) Organization
3) Communication
4) Foundation

Since you didn’t get a lot of information from your manager, you’ve got a blank slate.  Let’s brainstorm all of the things we could store about a country.  Don’t be shy; just get down everything you can think of.

There’s some overlap here.  That’s okay, because now we can organize our thoughts a bit.

Now that you’ve got a starting point, you can use this as a communication tool.  Take it back to your manager and say, “Is this what you were thinking?”  If you’ve got a non-technical audience, a conceptual model is great because it’s not intimidating.  Your manager takes a look and says, “Well, I was thinking along the lines of geography, demographics, and government.  I don’t care so much about culture or history or military”.  Cool!  You’ve just improved your design requirements

You have a better idea of what you’re trying to accomplish, you’re on the same page as your boss, and you have a foundation for the next design steps.  You can go a couple of different directions from here.  You can walk away from the conceptual model, or you can begin to drill down.  If you drill down far enough, you’re going to end up with the start of an Entity-Relationship Diagram (ERD).  What I would probably do in this case is take one of the subject areas I want to include and start drilling into it.  Maybe it becomes its own conceptual model.

Now you can cycle through the whole thing again.  Maybe you take this geography model back to your boss and he says, “Oh, by geography, I guess I meant ‘geo-political’.  Thanks for clearing that up. You’re such a genius. Let me give you a raise. And a bonus. Can I get you some coffee?”  You’ve continued to proactively clarify and refine your requirements. People are going to appreciate that. A little up-front conceptual modeling can save you a lot of time in the long run.

However, there are potential downsides.  You can go down the rabbit-hole with conceptual modeling. Resist the urge to use it as a procrastination tool.  It should be a quick process, and you should only model as far as it adds value for you. Documentation for the sake of documenting is a waste of time.  Use the model as a tool to get to the next step in development.

I hope that this has explained what Conceptual Modeling is all about, and has motivated you to try it out for yourself.  I use conceptual models all the time, and I’ve found that they’re a great tool to have at my disposal when I need to organize my thoughts or clear up what it is I’m trying to accomplish.


Find an opportunity to model something. Find some task you have, technical or non-technical that could benefit from a little time invested in laying out a conceptual model. Or, take this country concept and see where it takes you. You could go in a hundred different directions with a topic this broad.  Thanks everyone for reading today, and I’ll be back on Wednesday with more modeling goodness!

Model on, my friends…


6 thoughts on “SQL University – Conceptual Modeling

  1. Thanks, I’m a big fan of conceptual and logical modeling. When I was a data analyst and before I was a BI developer, it was very common for me to put a model together but since I’ve been a BI developer, I have not one architect put together a conceptual model, which is sad.

      • I think you already answered it: “We’re so enthusiastic about finding a solution that we occasionally jump right past understanding the problem at hand and just start doing stuff.” 😉 We have to learn how to make time for modeling. It’s the key to understanding the client’s data…and often, helping the client understand their own data.

  2. Well Done!
    I think that by not modeling (even if it’s a reverse of the physical) you miss out on communicating the system intent. I’m a visual learner and I can very quickly learn much more about a system from a data model than I can from a comprehensive architectural document. I beileve many people are like that. Wittness all the DMV posters and system catalog DM posters, object models for applications, etc. that come from Microsoft and other vendors.

    • Thanks, Paul! And you make a great point that I didn’t even think to cover. Reversing an existing system into a conceptual model is a great idea. It can help you get a handle on an unwieldy system or process.

Leave a Reply