On Blogs – Think like a Journalist

Today, I was reading a blog post.  The article was published by a group that I would consider reliable and reputable.  It was on a topic that I have passing familiarity with, and would like to be better at.  I’m not going to name the post nor the topic, because that’s not the point.  Here’s what went down…

1130 Hours: Read post.  Surprised at the absolutes declared in the article.

1135 Hours: Read it again.  Think that either I’ve missed some really basic lessons on this topic, or that maybe the article has provided some less than ideal guidance.

1209 Hours: E-mail a friend who I know is an expert on the subject.  Ask him to read it and let me know if he thinks it is right.

1300 Hours: Get response from generous and patient friend.  His e-mail (which was longer than the blog post) explains in very clear terms that there are problems with not only the guidance that the article provides, but also how some of the fundamental concepts were represented.

1420 Hours: Go find another post on the same topic by another trusted expert.  Read it and confirm for the second time that my original suspicions about the article were correct.

1430 Hours: Pat myself on the back for knowing just enough about the topic to realize that it seemed off in the first place.

1445 Hours: Go back to blog post to write comment that maybe the article could use a second look.  See that someone has already done that.  Decide not to pile on.

Now, I’ve been watching a lot of The Newsroom and House of Cards lately, both of which have characters who are journalists.  I think that reading blog posts, articles, and books is a lot like being a journalist.  One source is not enough.  If you’re hearing something new or something that contradicts what you think you know, don’t take the article at face value.  Go find a second source, and make sure that the second source didn’t use your first source as their source.  (Caveat:  I do have a list of absolutely trusted writers.  But it is my list, and is based on a lot of factors.  Okay, fine.  I’ll share one.  His name rhymes with Tall Candle.)  Also, if you’re finding conflicting advice, don’t be afraid to ask questions.  Any writer worth her salt is willing to accept some peer review.

And yes, if you’re wondering, I felt very MacKenzie McHale for all of 3 seconds. 

??????????????????????????? (photo from: http://www.fanpop.com/clubs/the-newsroom-2012/images/33579445/title/mackenzie-mchale-photo)

I imagined myself, headset on, shouting, “We’re not going live with this until we confirm a second source!”  Then I remembered that no one but me was consuming this information.  Oh well.

If you’re on the other side of the keyboard and are writing an article you plan to send out into the world, here are a few guidelines to live by:

1) Unless you are 110% sure that your guidance applies 100% of the time, don’t speak in absolutes.  “It depends” is a running joke for a reason.

2) Find someone you trust to tech edit for you.  Heck, find two people.

3) Remember that there are a lot of young database professionals out there who are reading your work in order to figure out how to do their jobs.  Don’t take that lightly.

4) If someone comments on your article and says you’re wrong, engage with them.  They’ve taken the time to read and comment on your work.  Granted, there are trolls out there, but a thoughtful comment demands a thoughtful response.  And keep an open mind.

5) If you’re preaching something that goes against conventional thought, take the time to post links to opposing views.  Help your reader make an informed choice about which advice to follow.

6) Encourage your reader to do their own research with the information you’ve provided.

7) If you discover that you’ve presented bad information, correct the article.  Own it.

All that being said, the proliferation of online resources has made us all better.  Don’t be afraid to put your research and opinions out there.  Just research, verify, and test.  And look for that second source before going live with the scoop. 

Blog on, my friends…


Discovery… Not Just for Explorers

In the scramble to get hands on keyboards in a development shop, one of the most important phases of development often gets short shrift.  That phase is discovery.  I was lucky enough to spend a few weeks with one of our clients working through a formal discovery phase, and was reminded how valuable this time and effort can be.  It also got me thinking… How can any developer, regardless of project role, gather the information that normally comes out of a discovery effort?

Discovery Who? 

So what is Discovery, exactly?  Discovery is the part of development where you answer a few key and seemingly basic questions about the work you think you want to do.  These questions include:  Why? What? Who? When?  There are a few key outputs of a discovery cycle, including:

  • Problem statement – a short, concise explanation of what’s in need of improvement/enhancement/rework
  • Solution statement – a short, concise statement about what’s going to be done about the problem
  • Scope – a list of things that will be done, as well as a list of things that won’t be done
  • Assumptions – any assumptions that have been made about environment, people, technology, complexity, etc. in regards to the project
  • Risks – a list of things that can derail the project, which might include company direction, technology changes, dependent projects, or that “simple” external system that you need to integrate but haven’t had a chance to see yet
  • High-level time estimates – a high level SWAG (Stupid Wild-Ass Guess) at how long it should take to complete the work, or a time window based on other external factors
  • High-level resource needs – a high level list of people (or skill sets) and technology resources (hardware, software) that are needed to complete the project

Notably absent is one thing:  How?  That’s because the how is design, not discovery.  Resist the overwhelming urge to start designing the solution before you fully define the problem.  This is the part that I struggle with every time I’m asked to help out with the discovery phase.

A Developer’s Life

Discovery information will come at you in one of two ways:  either you’ll be asked to answer the above questions and help define the project, or someone might come to you with all or part of this information and expect you to be involved in the subsequent phases of the project.  In both cases, it is absolutely vital that you (Yes, you… the dashing figure reading this post) feel comfortable with the outputs of discovery before you start designing or developing anything.  This is known (in my head) as Sneaky Discovery.   (Credit to Julie for her invention, the Sneaky Proof of Concept… I’ve adapted the concept for Discovery) It ain’t as evil as it sounds, I promise.  Let’s talk about this one for a bit.

I know… it can be uncomfortable to question a project’s intent or setup, especially when you haven’t been asked to.  But bear with me for a moment.  I’m not asking you to put on your fedora w/optional press pass tucked into the band and annoy the ever loving crap out of the people around you while sporting your best Humphrey Bogart voice.  I’m just asking you to take 30-60 minutes and make sure that you feel good about the work you’re about to do.  Let’s look at a dramatic reenactment of a common situation:


Scene: John, a diligent data professional is at his desk, working on the latest product release.  Enter Manager (Mike).

Manager: Hey! Bob!  How are you?

John: It’s, um, it’s John.  Not Bob.  I’m good.  What can I do for you?

Mike:  Good, Bob, good.  Glad to hear it.  I just walked out of a Very Important Meeting with Very Important People, and I have a new set of tasks for you.  We’re going to speed up Database X.  Also, I have these 17 new reports that need to be developed… um, somewhere.  Let me check my notes… Ah, there we are!  I’ll e-mail this right over to you so you can get started.  How long do you think this will take?  Can you have it by next Friday?

John takes a deep breath, and time stops.  Enter Mysterious Announcer (MA).

MA:  Right now, John is faced with a career conundrum.  His fate is in his hands, even though he doesn’t realize it right now.  He can either nod and say “Yessir, right on that, sir” and make his manager feel temporarily happy about his decision to hire John, or he can ask a few questions that might make his manager feel a bit uncomfortable in the short term, but will save the company thousands of dollars in the long term.  Let’s see how John handles it.

Time restarts. John turns to his Manager.

John:  Gee, Mike.  That’s a lot to absorb.  I have a few questions to ask before I commit to a time estimate.  Mind if we sit down and talk through the project together?

Mike: (uncertain) Sure?  I think we can do that.

Angels sing, bells ring, beautiful and intelligent women begin to notice John’s unconventional good looks and charming, yet adorably shy, personality


Friends, this is your moment to shine.  When faced with a situation like this, it’s up to you to do your own version of Sneaky Discovery and make sure that you’re not diving into the shallow end head-first.

When John gets a chance to talk to his manager, here are a few basic questions he should be asking:

1)      Why are we doing this?  What is the problem we’re trying to solve here?  (Problem Statement)

2)      What’s the solution that we’re going with?  Is it the only one available?  Why did we pick this one? (Solution Statement)

3)      What are the things that we’re going to do, and how does each of them help solve the problem we’re fixing?  What are we NOT doing? (Scope)

4)      Any assumptions about technology direction, complexity, or anything else that led us to the next Friday deadline? (Assumptions)

5)      Anything I should know that can cause us to get off track? (Risks)

6)      How long did you assume this project would take?  Have you made any commitments about delivery?  (High-level time)

7)      Who else is working on this? Do we have a dedicated development environment? (High-level resources)

8)      BONUS QUESTIONS #1: Who is impacted by this change?  Is it okay if I check in with them?

9)      BONUS QUESTIONS #2: How are we testing this?  How are we deploying it?

This conversation will probably take less than an hour for a narrow scope of work.  It does two things:  1) you step into the project with more information than you had initially and, 2) you show your manager that you want to understand the work you’re doing.  Good managers appreciate this.  If you’ve a manager who doesn’t appreciate your questions and desire to understand the work you’re doing, you’ve got a lot to think about, my friend.

Now, the worst case scenario is that John’s manager can’t answer these questions.  What to do then?  In a perfect world, you pull the comically large brake and hit the giant red STOP! button.  Now, it is up to you to decide how to handle the situation.  I’ll tell you how I normally handle stuff like this, because it happens… often.


Manager: Um, I don’t really know why we’re doing this.  The CTO asked for it, and I’m here to get it done.  That’s why I’ve been the vice president of middle management for 15 years, damn it!

Audrey:  Okay.  I can do this work.  However, let me lay out some of the risks for you.  We don’t understand why we’re doing it, and we might make poor design decisions because we don’t see the big picture.  Also, without a clear understanding of what problem we’re solving, I don’t know how to tell you that we’re done.

Manager: Okay, works for me.

Audrey:  Can you put that in an e-mail for me?


If I feel likely to get burned, I put my concerns/feedback in e-mail.  If I trust my manager to hear me, I tell him in person.  The point is that I never dive into design or development until I’ve either gained broad understanding of the project or at least told someone that I’m working under less than ideal circumstances.

As long as we’re all sharing, let me tell you… we’ve all been there.  We’ve all taken the task, done the work, and delivered something without really knowing why.  It happens.  The trick is to know when you’re dealing with a tough situation and how to mitigate risk to you as well as your team.

You’ll likely find that it is up to you to help flesh out those Assumptions and Risks, and to make suggestions about Scope.  As the in the trenches guy, you’re probably aware of issues that your manager isn’t.  Don’t be shy about sharing that information.  Trust me, ‘tis better to air the dirty laundry at the beginning of the project than to grumble at the end because you “just knew” that this issue was going to come up.

An Aside on Scope

We’ve all heard of it, and if you’ve worked for any amount of time in this business, you’ve lived it.  The dreaded scope creep.  Many times, scope creep happens because discovery was left wanting.  Or, the team isn’t disciplined about sticking to the original scope.  The pain of scope creep is never totally avoided, but it can be minimized.  Foremost, make sure that you have some scope to work with.  Remember how Manager Mike said that he wanted to “speed up Database X”?  Wow, that’s a broad statement.  He also wants to do it pretty quickly.  This means that you probably can’t re-architect the entire system or purchase shiny new hardware to solve this problem.  You might end up with something like this in your scope statement:

Task:  Speed up Database X

In Scope:
1. Tune top 10 worst performing queries
2. Examine index maintenance plan and add key missing indexes
3. Archive old data that is no longer used and remove from primary database
Out of Scope:
1. Change logical design of the database
2. Upgrade hardware
3. Rework data access layer

On a highly regulated team, this may end up in a formal document.  If you’re like many developers and things run a little more fast and loose in your shop, there is nothing wrong with shooting off an e-mail or having a conversation about what can be done in the time you have.  You could say, “Mike, we have a week and a half to do this work.  Let’s focus on low-hanging fruit and non-invasive changes.  When we have more time, we can talk about more aggressive changes”.  (Managers love idioms like “low-hanging fruit”)  He might say, “Great!”, or he might come back with, “So if we wanted to address your out of scope items, how long would that take?”  Both are acceptable responses, and you should be ready for them.  Point is, put some boundaries around the work you’re doing, and set expectations about how much can really be done with the time and budget provided.  This allows you to be the guy who never says, “No”, but also protects you from unreasonable requirements.

Final Thoughts

One last thought on discovery within the development cycle.  As developers, we want to know everything up front.  We want details, and we want them now.  Here’s the thing… it ain’t gonna happen.  Take a deep breath, accept the unknowable, and focus your Sneaky Discovery efforts on getting some clarity on why you’re doing what you’re doing, what you’re doing (and not doing), and what might throw the project off.  The rest will shake out during design and development (and possibly testing), and that’s okay.  We’re never going to get it perfectly right, but asking a few of the right questions at the right time can get everyone comfortable with how the rest of the project is going to go.

Now, get out there, get proactive, discover some stuff, and get vocal about how your work fits into the larger picture!  I’m rooting for you!


Great way to begin 2013! (Hint: it involves a TLA)

Quick, happy announcement… My fellow Datachix, Julie Smith, has received the SQL Server MVP award for 2013!  Also, I’ve been renewed as a SQL Server MVP for 2013.  What does this mean?  Yeah… 100% of the people who blog on this site (all two of us) are MVPs!


So, Julie, while I am not the first to congratulate you, let me be the first to do it on our blog. 🙂

On a personal note, I am grateful and humbled to be a part of the MVP community for another year.  Having experienced 12 months of the program, I’m honored that Microsoft saw fit to include me for another year.

As a bonus, here’s a picture of Julie being awesome:


Rock on, my friend!


p.s. Note to self:  It’s been a while since you blogged about anything, Audrey.  Get your act together and post more often!  (Nodding…)

#Meme15 – Why do you blog?

I have a confession to make… Blogging is hard. Staring at a blank page, cursor blinking in that impatient, foot-tapping way, crappy blog ideas spewing from my desperate mind like oil at Spindletop circa 1901.

Pictured: Audrey having crap ideas

“I know… I’ll blog about my cat. I’ll, um, equate my cat’s love of shiny things to my love of foreign key constraints. You know, because foreign keys are shiny. Wait? What? I’m an idiot.” So, when Jason Strate (B | T) proposed the idea of #meme15, I was all for it. Tell me what to blog about? Save me from myself? Sign me up!

This week’s questions are:

1) Why did you start blogging?

2) Why do you currently blog?

Question 1: Why did you start blogging?

Let’s go way back to January 2010. Julie Smith (B | T) and I were talking and we said, “Hey! Let’s do a blog! It’ll be fun! We can write funny stories, be irreverent, and amuse ourselves with our oodles of witty”. I’m pretty sure there was wine involved. We didn’t think much about networking or career development or even education. We just thought it would be fun to do. Some technical blogs can be, shall we say… dry. We name no names. We both like the idea of making data fun. “Hey”, we thought, “we crack ourselves up regularly. Maybe we can crack someone else up”. That’s it. Almost 2 years later, we’re still blogging. We love it. And our reasons for it have evolved. Which brings us to…

Question 2: Why do you currently blog?

Easy. I can name all my reasons in three words: Me. You. Us. Oh, you want details? Gosh, you’re demanding. Okay, twist my arm. I’ll elaborate.

1) I blog for myself.

This is an important principle to me. I blog because I enjoy it. My first rule of writing is: Amuse Yourself. If something amuses me, I like to share the fun. I think that as humans, we’re all storytellers. We want to know that there are people willing to listen to our stories, and what is a blog post but a story? Sure, it might not be Shakespeare, but it’s still my story. It could be called narcissism to say that I want people to hear what I have to say, and that’s okay. Anyone who tells you that they’re 100% altruistic is probably trying to recruit you into their cult. Don’t believe them. They’re going to make you wear burlap robes and ugly running shoes and refer to their leader as Supreme Ultimate Bob. And burlap is so 1990’s.

Trust me. I'm only thinking of you.

Point is, on the day that I don’t get a rush from hitting “Publish” on a post is the day I stop blogging.

p.s. That’s my Gavin, showing off his new winking skills. Watch out ladies!

2) I blog for you.

It’s sappy. I know. But it’s true. Absolutely NOTHING in my professional life makes me happier than having someone tell me that a blog post I wrote (or a presentation I did) helped them do their job better. The idea that something I put out there made somebody else look smart… that’s so cool.

Dramatic reenactment: Me hugging our readers

Photo courtesy of Stuart Miles http://www.freedigitalphotos.net/images/view_photog.php?photogid=2664

This is part of the evolution of blogging. When we started, I honestly didn’t think anyone was paying attention. Then, I had a few people come up to me and say that they’d read my post on Random Topic X, and that they had a question or that they’d applied my solution. It was a little scary to realize that people occasionally pay attention to my ramblings. I realized that I had a responsibility to be as correct as I knew how, and to continue to share what I learn. And it is so SATISFYING to share information. Try it. You’ll like it.

3) I blog for us.

By us, I mean the SQL Community. A community like ours requires care and feeding. I’ve benefitted in a thousand ways from this loose network of passionate people, and if I’m going to take from the community, I feel an obligation to give something back. If everyone read blog posts but no one ever wrote them, then we’d all be reading the same Microsoft Support articles about Installing SQL Server 6.5 over and over again. Boring. Blogging allows me to contribute to this awesome community while letting my Geek Flag fly.

What? I'm a geek, not an artist.

Plus, I get to have cool conversations with interesting people. Like I said, “Sign me up”.

Blog on, my friends…


SQL University – Entity-Relationship Modeling (Part I)

Hey there! Welcome back! Or just Welcome, whichever applies. This week at SQL University (organized by SQLChicken himself, Jorge Segarra) we’re discussing data modeling. On Monday, we talked Conceptual Modeling. Today, we’re going to talk about Entity-Relationship Modeling. Now, fair warning, this is a BIG topic. People spend their entire careers working with ER Diagrams, and there is a plethora of material out there for you to peruse. Also, today I’m going to focus on OLTP, or transactional, data models. There is an entire discipline focused on OLAP, or dimensional data models (data warehousing), but I firmly believe that you’ve got to understand how to build an OLTP model before you tackle an OLAP model. I’m going to hit the high points, hopefully give you a few things to think about, and and direct you to some materials (and people) that can help round out the topic. Obviously, this isn’t a comprehensive list, but it’s a really good starting point.


Community Members:

  • Karen Lopez (B|T)
  • Louis Davidson (B|T)
  • Thomas LeBlanc (B|T)

A note: Not only do these three people write about data modeling, they also present regularly. Keep an eye on their blogs for great content and information about where you can go watch them teach in person.

User Group:

PASS Data Architecture Virtual Chapter


Case*Method: Entity Relationship Modelling by Richard Barker

This book is the one I received as course material in my very first database design class in 1997. I still use it. In fact, it’s sitting beside me right now so I can reference it as I work on this post. It’s out of print, but you can still find new and used versions for sale online. First published in 1990, it’s my personal definitive guide to purist modeling. It says Oracle on the cover, and you’re going to see COBOL code inside, but trust me… you’ll learn from this book.

Pro SQL Server 2008 Relational Database Design and Implementation by Louis Davidson, et al

Louis does an amazing job of teaching relational data modeling as well as couching his work in the context of SQL Server. If you’re a SQL Server professional (or want to be), pick up this book.

Data Model Patterns: Conventions of Thought by David C. Hay

Once upon a time, I used to think that all work I did had to be original. Now, I’m older and wiser. Mr. Hay provides an entire book of data model patterns for different industries and types of data. It’s a great reference to see how to do it right, and to get some ideas for your own database.


So, what is an Entity-Relationship Diagram (ERD)? It is: A visual representation of entities, the relationships between entities, and the attributes that describe them.

At the end of the day, an ERD has three major components:

  • Entity – a thing/object that is significant enough that we need to know information about it
  • Relationship – an association between two entities
  • Attribute – something that describes the entity or the state of the entity

At this point, it would be easy to say, “Oh, I get it… tables, foreign keys, and columns!” Resist that urge. There is a lot of value in getting your mind away from technology and database platform for a while and thinking in terms of an ERD. Why? Well, your focus isn’t really on defining a database yet. I mean, we all know how this story ends (spoiler alert: with a database), but let’s let this plot thicken for a bit before we read the back page. What you’re doing at this point is defining the business. If you do it right, someone should be able to look at your ERD of Widgets, Inc. and with a little understanding of notation be able to say, “Ah, I see what they do for a living.”

Also, Entity-Relationship Diagram <> Database. Yes, what you model with an ERD will probably end up in a database, but I think that unless your database is pretty narrow in scope, you’ve probably got multiple ERD’s in a single database. Or conversely, you might have many databases that reflect a single ERD. It’s why Entity-Relationship Diagrams are referred to as “logical models”. You’re not defining the physical structure of the database; you’re defining the logical structure. The physical structure has a whole other set of factors to consider.


As I mentioned above, an entity is a thing. Looking at Thesaurus.com, there are a few other synonyms for entity that apply in this context: Individual, Item, Material, Something, Stuff

And an entity doesn’t have to be a physical thing. It can be a concept or idea. It’s a NOUN. Remember that if nothing else sinks in today: entities are nouns.

As with all things, there are some guidelines you should follow. Building on the conceptual model we started with the last post, let’s start looking at an Entity-Relationship Diagram that models Country. (The nations, not the music.)

1) An entity is one thing, and that one thing should be defined by only one entity.

The entity COUNTRY is one thing, a single concept. If I follow this rule, I shouldn’t have a second entity named NATION, because that overlaps with COUNTRY.

2) An entity should have a name that represents the type of thing, not an instance of the thing.

This can be a tough one. Basically, your entity name should be high-level enough that it doesn’t limit how you can describe it later through relationships and attributes. For example, my entity is named COUNTRY, not UNITED STATES. COUNTRY is the type of thing, UNITED STATES is an instance of that thing.

3) An entity must be uniquely identifiable.

If my entity isn’t uniquely identifiable, then it isn’t a single thing. It’s multiple things, and in the world of data modeling, trying to cram two things into one thing is a bad thing. As you’re making your list of entities, ask yourself: Is there something about each occurrence of this entity that differentiates it from every other occurrence? That something is your natural key, or business key.

Aside: Next time you see my fellow Datachix Julie Smith, ask her about business keys. Then go get some popcorn and enjoy the rant. Take your time; she’ll still be going strong when you get back. 🙂

4) Entities are singular.

Why? Because they represent one thing! For some reason, there’s a lot of debate about this. Just because your entity has multiple instances of that thing – COUNTRY has United States, Canada, Mexico, etc. – doesn’t mean that you’re talking about Countries. You’re defining an entity that contains instances of COUNTRY. Thus, singular.


Entities should be represented by a rounded rectangle with the entity name in ALL CAPS inside of it. If it helps, make note of a few instances of the entity. If you know the natural key already, you can note that too.

So, let’s list some entities and occurrences related to this Country ERD:

The next step is to define the relationships between these entities.

VERY IMPORTANT CAVEAT: I’m using this subject area for illustrative purposes. My goal is to keep things as simple as possible, so I know that there are a lot of other things I might do with a Country ERD, and that there are some business rules I’ve oversimplified.


What is a relationship? It is: A named, meaningful relationship between two entities.

Each relationship has two sides, and there are characteristics of those sides that need to be defined. If you can’t define the relationship, then you might not have one. It’s okay – there are always other fish in the sea.

1) Name – this is commonly known as a “verb phrase”. Where an entity is a noun, your relationships are verbs. Try to come up with meaningful verb phrases. If you’re stretching and end up with 42 relationships with verb phrases like, “is related to”, then you might be forcing it.


Verb Phrase: A STATE/PROVINCE is contained within a COUNTRY, and a COUNTRY is made up of STATE/PROVINCE

2) Cardinality (how many) – there are three kinds of relationships: One-to-Many, Many-to-Many, and One-to-One

If we refine our verb phrase, we can add information about cardinality. In this case, we have a One-to-Many relationship between STATE/PROVINCE and COUNTRY

A STATE/PROVINCE is contained within one COUNTRY, and a COUNTRY is made up of one or more STATE/PROVINCE

3) Optionality – this tells us whether there is a mandatory relationship or not. Some entities must be related, others are optional. Let’s refine the verb phrase again:

A STATE/PROVINCE must be contained within one COUNTRY, and a COUNTRY may be made up of one or more STATE/PROVINCE


There are many different notations, but I prefer what is known as Crow’s Foot notation because it is simple and I’m comfortable with it. But, use what works for you. Just be consistent.

I like this notation because it tells you a lot about the relationship in a very concise way. Reading it can take a little getting used to, but here’s what you do: Starting with the entity and following the relationship line that comes out of it through to the related entity, say…

“[ENTITY NAME] [optionality] [name] [cardinality] [ENTITY NAME]” where optionality is denoted by solid (mandatory) or dashed (optional) line and cardinality is denoted by crow’s foot (many) or a straight-line (one) connector.

Such as, “STATE/PROVINCE must be contained within one COUNTRY”. I know it’s a lot to take in, but practice a little, and it’ll make sense. I promise.

What you’ve defined with a relationship is a business rule. If you’re lucky enough to have business rules in advance, that’ll take you a long way towards defining your relationships.


Let’s talk attributes. Attributes are a detail that helps to describe the entity or the state of the entity.

If I’m looking at my COUNTRY entity, I might have many ways to describe it and its state

  • Name
  • Population
  • Sovereign?
  • Established Date
  • Government Type
  • Currency
  • National Symbol
  • Language

These are potential attributes for my entity. Why do I say potential? Well, because until we start filling out this model, we’re not really sure whether they fit as attributes or not. Attributes have rules:

1) Attributes should depend on the key.

So far, I’ve defined my natural (or business) key of Country as Country Code. Each attribute needs to be examined and the question asked: Does this attribute define that key and nothing else?

2) Attributes should be atomic.

This means that an attribute serves as one descriptor of the entity. It can’t represent two things. Say I had an attribute called LanguagePercent that contained information like “English-60%” and “Spanish-40%”. This needs to be handled differently. (Come back on Friday when we talk about ERD refinement to see how)

3) Attributes should not repeat.

Taking the Language attribute, let’s think of this in real-world terms: Some countries have a single, official national language (I’m looking at you, France), but others have multiple recognized languages. Belgium has three: Dutch, French, and German. I don’t want to have to add attributes to my entity like Language1, Language2, Language3. There is a solution: LANGUAGE isn’t an attribute after all. It’s an entity, with a relationship to COUNTRY. How about that? We just refined our list of entities.

What’s really cool about this is that COUNTRY and LANGUAGE actually have a Many-to-Many relationship once you break it out and start working through your verb phrase:

“COUNTRY must communicate via one or more LANGUAGE, and a LANGUAGE may be the communication method for one or more COUNTRY”

Many-to-many relationships are valid business/real world relationships, but they don’t fly within a database. On Friday when we talk ERD refinement, we’re going to have to address this.

4) Attributes should not describe other attributes.

What if I had two attributes, National Symbol and Has National Symbol? Well, the information contained in National Symbol is going to be dependent on whether there is one. We don’t want that. Our attributes should only define the entity.

Given this, we have the start of an Entity-Relationship Diagram for Country.

We’re going to stop here for today. For my homework, I’m going to go through the rest of the entities, define the relationships between them, and then define attributes. If you’re feeling frisky, try fleshing out the model on your own. On Friday, we’ll compare notes then start the very important refinement process. This is where we take a first draft of an ERD, and see how we can improve it. Data Modeling is an iterative process. No one creates a perfect model on the first try – that’s why we’ll spend Friday learning about how to methodically refine our ERD.

Thanks for reading, and I hope to see you again on Friday.


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.

Continue reading SQL University – Conceptual Modeling

A Call to Arms: Ladies, I’m Looking at You!

I’m helping to organize SQL Saturday #89 in Atlanta. This morning, I looked to see where we were with speakers and submissions. Running through the list, I saw we had 32 speakers. Awesome. Scrolled through again. We had three women. Not awesome.

As of right now, we’re at 35 speakers and 3 of them are women. That’s 8.6% for those keeping track at home. That’s less than 1 in 10 speakers. I just spoke at SQL Saturday #77 in Pensacola, and was the only female speaker. This is not good. This is not the trend I want to see. (By the way, thank you to Melissa Coates (Blog | Twitter), Jen Underwood (Blog | Twitter), and Sarah Barela (Blog | Twitter) for submitting!)

I have a very strong opinion about objectivity in our community. Pick me based on the value of my content, not based on my gender. I’ve said a few times, publicly, that if we just leave smart women to their own devices, gender diversity will work itself out. Wow… That’s just not happening.

What is happening? Was I wrong? Where are we missing the boat? Where are the smart women who are database professionals, and why aren’t they clamoring to submit sessions? You know what’s the worst? I tried to think of someone I could call and say, “Submit already”! I couldn’t think of anyone. That made me angry. Really angry. Ask Aaron Nelson (Blog | Twitter). Poor guy had to listen to me rant (a lot) today. On reflection, I’m mostly angry with myself. I don’t have anyone in mind? I haven’t thought through who I should be encouraging to submit an abstract? Shame on me.

So, I’m stepping off my standard-issue Gender Neutral Soapbox and putting out a call to arms. Ladies, (yes, you… I’m talking to you), submit a session to a SQL Saturday. Get involved! Put yourself out there. Is it scary? Yes. It’s also fun, exhilarating, and rewarding. The first time someone e-mails you to tell you that your presentation taught them something useful, you’ll be hooked. I promise.

Here’s my commitment to any woman who’s trying to decide if she wants to try her hand at presenting and is on the fence: I will be there for you. I’ll review content, help you practice, give you pep talks, whatever you need. There are too few of us out there. Get involved. Help prove me right – that we’re just as capable, just as passionate, and just as ambitious as the boys.