MVP Summit 2015 – A Few (Surprising) Lessons Learned

MVP Summit is always an amazing event. This year was no exception.  It’s one part boot camp, one part super-secret secret-telling time, and one part family reunion. Along with that, we get cool swag (like the utterly amazing Data Platform jackets Jennifer Moser hooked us up with this year), interesting conversations, and time with the guys & gals who build the products we’ve bet our careers on. Needless to say, I was happy to be there.

This year was also a little different, and I want to talk about that for a minute. There has been a lot of buzz since Satya Nadella took the helm at Microsoft that things were going to be Different. That product teams were going to align, that they’d be smarter about how they build software, and that they’d move faster than they ever have before. I have to be honest… I thought it was all marketing hype. Until last week.

The very first thing I noticed on Monday morning was that the level of transparency was through the roof. As a person who builds software for a living, I know that we all err on the side of pretending like we have all the answers and that our process is bulletproof. That was not the message from anyone on the Microsoft team last week. While it is always awesome to hear about what’s new on the technical side of things, there was another level of value coming out of the talks. Honesty. A willingness to fail. Engagement that was real. Actual two-way conversations.

One of the things I love to do during presentations is take a lot of notes. Along with the obligatory talking points and feature notes, I like to write down things that are said by the presenters that resonate. I cannot share the exact quotes because of NDA rules, but I have been given permission to share the gist of what I learned.  Because I spend way too much time on Imgur, I’m including memes to illustrate my points.

Don’t be afraid to fail. Failing, and failing fast, gets you to the good stuff.

success kid

Sometimes, you have to admit that you’re doing something totally new and that you might not already be an expert. This is okay. Go learn it, then you can build it.

doge

There’s a lot of new stuff coming at us. Embrace it. It ain’t going away.

kitten hug

Applaud the person who points out that things aren’t on the right track.  She’s the one who is unafraid.  (And as Mr. Herbert taught us, fear is the mind-killer)

penguin cake

Experiment. Try something different. Be willing to fail and then try again. It’s science.

meme by: http://knowyourmeme.com/users/deathbyexile
That’s Neil deGrasse Tyson, y’all

In all seriousness, to hear these kinds of messages coming from the most venerable software development organization in our business was inspiring. It made me feel like going home and taking a few risks. It made me feel like we were all in this together. Data and data management is moving at an insane pace these days. Always changing, always moving forward. Keeping up is overwhelming on a good day. That the experts at Microsoft are saying , “We’re learning right along with you. We’ll get this.”, it is empowering.

My point is, the technical stuff was great. The product positioning information was helpful. But my real takeaway last week was that… well, let me share one little story…

I was in a meeting about a (NDA – sorry, y’all) thing. The presenter threw out some concepts and thoughts about the thing. I raised my hand and said, “I think I have a use case for you. Let me run you through a scenario that one of my clients has.” After I explained what I needed, I asked, “So, how would you solve this problem?”. The response? “I don’t know yet. But I think we can solve it together. Let’s stay in touch and see if we can come up with some good ideas.”

And that’s it right there. I went to a session about a topic where Microsoft didn’t have the answer yet. They still got in front of us and talked about where they were, what their goals were, and what they were doing to move forward. And when we had ideas or real-world problems to solve, they engaged. They asked us for help. Not “help”, as in, “fill out this survey for us; we promise we’ll do something with your feedback”. We were treated as peers and as people on the ground who had real value to add to the conversation. It was a little bit amazing.

And you know what? It’s working. They’re doing more, faster. They’re innovating in a way that big companies aren’t supposed to be able to do. I’m excited about where we’re headed.

So in short, thank you to Microsoft, the MVP Summit organizers, and everyone who makes our experience as MVP’s special. It was an awesome week.

Fail fast, my friends.

–Audrey

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…

–Audrey

A Giant Thank You, and a Link

First of all, thanks so much to everyone who supported, organized, and attended the Day of Data Warehousing fundraiser yesterday. I am humbled and honored to have had a chance to spend the day with all of you. Couldn’t have asked for a better group to spend a Thursday with!

As promised, here is the link to my slides, demos, databases, and documentation: http://sdrv.ms/16tszZZ

If you have any questions, comments, feedback, or just want to say hi, please get in touch!

Personal E-Mail: audreydhammonds@gmail.com
Work E-Mail: audrey.hammonds@innovativearchitect.com
Twitter: @DataAudrey

Model on, my friends…

–Audrey

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!

mvp

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:

Wizard

Rock on, my friend!

–Audrey

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…)

SQL Saturday 126: I presented DQS Finally! in Indianapolis

Thanks to the folks who came out to see my DQS presentation in Indy this past weekend.  Thanks to Caroline Bailey, Hope Foley, Kyle Neier , Eddie Weurch, and the rest of the Indy crew who put on an excellent event.  Here is my slide deck as promised.  I met many fantastic folks and had a paleolithic good time.

Thoughts on the Latest PASS Fracas…

First, I want to tell you what this post is NOT about. It is not about the PASS BoD. It is not about Sri Sridharan. It’s not about my opinion of the decisions made by the PASS BoD in regards to the two open board positions. Okay, glad we got that out of the way.

Here’s what it is about. Community. Family. Even when we don’t agree with each other.

Let me tell you a story about myself… When I was 13, I played softball. My team took first place in our league, and that meant that we got to compete in the District Tournament. Now, our coach had the option to select 4 players from other teams in our league to supplement our team at the tournament. One of his selections was a first baseman. I was the first baseman for our team. I got benched. I was furious! I thought it was unfair that I lost my spot, when I helped our team win the league. I thought I should be on the field. I even went so far as to write a very strongly worded letter to my coach. (Yeah, I was THAT kid) I was complaining to my mom about the ordeal, and said that I didn’t like the girl who took my position.

Here’s what she said to me: “Honey, let me ask you something. What’s more important to you? That you’re the one on the field or that your team wins? She’s taller than you (important in a first baseman) and has a better bat. Are you going to blame her for your coach’s decision? You need to decide right now who you are. Are you the person who thinks of your team first or yourself first? I expect you to put the team first, and you had better be the loudest, most supportive person on that bench, and be ready to step up whenever you’re needed”.

My mom was never one to mince words. It was a hard lesson to learn as a kid, but a good one. Even though I disagreed with the coach, I stayed on the team. I cheered every play, and was ready to sub in as needed. Guess what? We won the District Tournament. I would have missed out had I quit.

What does this have to do with our latest PASS drama? Well, besides giving me an opportunity to tell you a story about myself, there is a point here. We’re a community, and a family. While we don’t always agree, and sometimes disagree vehemently, we’re still part of the same team.

And here’s my point. We might not all agree with the decisions made by our PASS BoD, but let’s cheer for our players. Let’s give Kendal van Dyke (Blog | Twitter) and James Rowland-Jones (Blog | Twitter) the best possible chance to be successful and effective as appointed board members. Will we hold them to a high standard? Of course. Don’t we always? Will we ask them to do a (mostly) thankless job for no pay? Yes. Will we tell them when we think they’ve made a bad decision? Yes, we’re pretty good at that. But, let’s give them a chance. They didn’t ask to be put in the middle of a controversy. They stepped up and accepted leadership roles within our community. That counts for something. Now, I don’t know James Rowland-Jones, so I can’t speak from experience about him.  But I can only assume that he cares about this community based on what he wrote here. I know Kendal personally, and I know that he’s a kind, hard-working person with honorable motivations.

Should we blindly follow the Board of Directors?  No, of course not.  Should we have an opportunity to vote on the by-laws?  Yes. Should we raise the red flag when we disagree?  Hell yes.  However, should we blame and publicly vilify two community volunteers who got stuck in the middle? Absolutely not.

Let’s all take a deep breath. I’ve done it, and here’s what I asked myself. If I were in Kendal or James’ shoes right now, what’s the one thing I would ask for? The answer I came up with is, “Give me a fair chance to show everyone that I can do right by the community”.

So that’s what I’m doing. Kendal and James – I’m cheering you on. I hope that you work hard and lead us well.

Wait… One more note before I go… Let’s be kind to each other.  The world is tough enough as it is without mean people in it. 

–Audrey

#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…

–Audrey

SQL University – Entity-Relationship Modeling (Part II)

Hey there! I hope you’ve had a great Friday. Today, we’re wrapping up Data Modeling Week here at SQL University. Many thanks to Jorge Segarra (Blog|Twitter) for giving me the opportunity to cover these topics. Data Modeling is my first love, and I’m thrilled that I’ve had a chance to write about it. There is no way to cover anything but some of the big ideas in three posts, but there is a lot of information out there for you to learn from. I provided some resources in the last post, Entity-Relationship Modeling (Part I), and I think they bear repeating. Jump to the bottom of this post for recommendations on further learning.

Any-who… On Wednesday, we talked about the basics of an Entity-Relationship Diagram (ERD). Entities, Relationships, and Attributes were discussed, and we got the beginnings of an ERD about Country down on paper. If you haven’t read that post yet, you might want to jump over to it and go through that first. (Link in paragraph above) What we’ll talk about here will probably make more sense. Or not. All up to you, my friend.

So what are we talking about today? Refinement! No, not finishing school refinement. Wait… maybe we are. What we’re going to do is polish up the first cut of the ERD by walking through the entities, relationships, and attributes and identifying areas that don’t quite work. Then, we’ll talk about how to tweak those areas to make them walk with their shoulders back and their eyes up, like the prim and proper ERD elements they are.

<Soapbox 1> You will never get any model perfectly right on the first try. It’s okay. The fear of imperfection can create inertia, and that’s why a lot of modeling projects stall and eventually fail. Recognize that you don’t know everything up front, and that your data isn’t perfect (and neither are you). Just do what you can with what you’ve got. Something is almost always better than nothing. </Soapbox 1>

Between Wednesday and today, I went through the entities for the Country model and added them to the ERD. I added attributes and even some inherited information between the entities.

A note on attribute notation. I used underlines and italics to note some different things about the attributes. This isn’t formalized, but it helps me see what’s what. If I’m using a real modeling tool (which I’m not here), a lot of this metadata about the attributes will be handled by the modeling tool GUI.

But, for today, here’s the notation I’m using. I kind of like it, because I’m not dependent on a particular piece of software, but it tells me what’s going on. There’s a not-so-subtle message here: Care less about formal notation and more about consistency and readability. Do what works for you and your organization.

Here’s what I ended up with:

I’ll let you read through the model since you totally rock at reading ERD’s now. Next up, we’ll start addressing issues we see.

MANY-TO-MANY RELATIONSHIPS

The first issue we’re going to address is Many-to-Many relationships. In the real world, these kinds of relationships happen all the time. For example, COFFEE is drunk by many PEOPLE, and PEOPLE drink many cups of COFFEE. However, in a database, Many-to-Many relationships can create all kinds of problems. Trying to query data like this can be a bit of a nightmare, so we resolve the problem with what is called an Intersect Entity. It is just what it sounds like: An entity that intersects the relationship between two entities. We take that Many-to-Many relationship and turn it into two One-to-Many relationships. You know what? It’ll make more sense if I just show you:

This is actually very simple:

1) Create a new Entity, and give it a name that is the concatenation of the names of the entities you’re intersecting

2) Split up that Many-to-Many relationship into two One-to-Many relationships, with the Many side being on the Intersect Entity. Now I have two sets of verb phrases:

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

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

One thing that you’ll want to make sure you adjust in the relationships: The optionality of the intersect entity needs to be mandatory, because it’s all inherited information.

3) Allow the Intersect Entity to inherit the key from the two entities. The combination of these two becomes the key for the Intersect Entity. (Note: A key made up of more than one attribute is known as a composite key)

4) Finally, add non-key attributes to the Intersect Entity. Now, this is very, very cool. Remember on Wednesday when we talked about Attribute rules? One of them was “Attributes must be atomic”. The example provided was about the percentage of people who speak a language within a country. An attribute like “English-60%” is not valid. However, we can now take advantage of that Intersect Entity, and we have a perfect place to put that information. Percentage is only one thing (atomic), and it helps to define the combination of COUNTRY and LANGUAGE.

We have one other Many-to-Many to resolve: CONTINENT and COUNTRY. Trivia Question: What countries are transcontinental? Answer: Egypt, Russia, Turkey, Kazakhstan, Azerbaijan, and Georgia. Keep that one in your back pocket for your next team trivia night. 🙂

INHERITED ATTRIBUTES

Classic Inheritance

When there is a relationship between two entities, the child entity (the one on the Many side of a One-to-Many relationship) will inherit the key of the parent entity (the one on the One side of a One-to-Many relationship). If you’re familiar with physical database implementation, these are your foreign keys. Thinking in the context of an ERD, there is a very important principle at play:

An Entity owns its data.

An entity is the one and only place for information about that particular thing. I don’t want information in more than one place. Let’s look at COUNTRY and GOVERNMENT TYPE:

I have an inherited key of Government Type Name. I also have an attribute in COUNTRY of Government Type. Because I’ve inherited the key of the GOVERNMENT TYPE entity, I don’t need the Government Type attribute any more. The advantage of allowing entities to inherit attributes like this is that I have a single place to store Government Type information, and will ensure that I don’t get variations on the descriptors later on. We’ll remove the Government Type attribute from COUNTRY.

The Aggregation Question

Another attribute that we should look at is Population. In my original ERD, I have Population in multiple entities: CONTINENT, COUNTRY, STATE/PROVINCE, and CITY. This is a tough one, and is going to require a decision on your part. Here’s what you need to ask yourself: Can the Population of a CITY aggregate up to a STATE/PROVINCE Population, then up to COUNTRY, then up to CONTINENT? Ideally, yes. In practice? Maybe not. Not only do you need to think about the principles of data modeling here, you need to think about your data quality and your business.

From a purist standpoint, we should store this attribute at the lowest level we can (CITY), and depend on the relationships between the entities to allow us to calculate Population at a higher level (STATE/PROVINCE, COUNTRY, or CONTINENT). From a pragmatic standpoint, we don’t really know if the entire population of a continent lives in defined cities. Also, are we looking for estimates or hard numbers?

The downside to storing an attribute in multiple places is that now I’m going to have to maintain that attribute multiple times. Let’s say I increase the Population of Atlanta by 5,000. Now I have to go through and increase the Population of Georgia by 5,000, the Population of the United States by 5,000, and the Population of North America by 5,000. That’s a bit of a nightmare. Conversely, what happens if I get an update to the population of North America? How do I push that data down to the lower levels? Allocation rules? Let me tell you something about allocation rules… they aren’t fun. I still get the shakes when I think of a system I worked on that had to manage financial allocations.

So, all that being said, be a purist and approach this question optimistically. If you’re not sure, assume you can define Population at the CITY level and aggregate up as needed. If someone proves you wrong, ask yourself if you really need Population at every level. Remember, you can always change it later, and use physical implementation to manage your data. Let the structure of the ERD work for you and keep things as simple as possible for as long as possible. We’re going to remove Population from STATE/PROVINCE, COUNTRY, and CONTINENT.

I could make the same argument about Area (as in the measurement width X height). But looking at the business rules, how often does the area of a continent change? Especially the “official” measurement. Same goes for COUNTRY and STATE/PROVINCE. While I could theoretically aggregate this data, I don’t see a lot of value in it. Entity-Relationship Modeling isn’t all about following checklists and rules. Sometimes you’re going to have to make judgment calls. Don’t panic. First, you’re pretty smart. Second, you can always change the model if you chose wrong.

Let’s take a quick look at the current state of our model now that we’ve made some tweaks:

Wow, she’s looking pretty good so far. Let’s see what else we can to do polish her up.

ATTRIBUTE GRANULARITY

We touched on attribute granularity a little when talking about Population and Area. We also have another, less obvious, issue. Take a look at STATE/PROVINCE. We have two attributes, Capital City and Largest City. You can definitely make the argument that these attributes help define a STATE/PROVINCE. But, sitting back and thinking about it for a while, we also have a CITY entity. We could also define a CITY as being the Capital City or the Largest City.

Plus, remember that principle we talked about earlier? Entities own their data. Which entity owns information about cities? CITY does. STATE/PROVINCE has a relationship to CITY, but it shouldn’t be in the business of defining information about cities. CITY is quite capable of doing that herself, thankyouverymuch. I want attributes to define business keys at the lowest level possible. Plus, we’ve got a potential data integrity issue here. What if someone fat-fingers the value for Capital City and types “Sacremento” instead of “Sacramento”? And what if I want to know the population of the capital of California? Now I can’t get that information from CITY, because the misspelled name doesn’t exist in my table. We’re going to move Capital City and Largest City out of STATE/PROVINCE and into CITY, and instead of having to store the name of the city again, now we just need some sort of indicator.

IS MY ATTRIBUTE REALLY AN ATTRIBUTE?

The final area of refinement we’re going to discuss is the one you’re most likely to miss. Again, don’t panic. If you miss it, remember that you can always clean it up later. What we want to do is look at each attribute and ask the question, “Is this really an attribute?”

Let’s take the Currency attribute in COUNTRY. At first glance, yeah, a country has a currency. Makes sense. But, let’s dig in a bit more.

1) Does this attribute have meaningful information I’d like to store about it?

Remember the initial definition of an Entity? A thing/object that is significant enough that we need to know information about it. What might we want to know about currency? Well, we might care about exchange rate and stability. I can’t throw Exchange Rate into COUNTRY, because it doesn’t describe the COUNTRY, it describes the CURRENCY. (Attributes should not describe other attributes) Oh, look at that… CURRENCY just got upgraded to ALL CAPS. It’s an entity now.

2) Will the values in this attribute repeat?

Another way to evaluate an attribute is to look ahead to the data that might be stored in it. Looking at Currency again, let’s think about what will end up in this attribute. Many countries have their own currency, so those values wouldn’t repeat. But… Euro-Zone. Most of Europe is now on a single currency. Do I want to store the value “Euro” multiple times in the COUNTRY entity, or do I want to store it one time in a CURRENCY entity and allow the COUNTRY entity to inherit the CURRENCY key? Naysayers will tell you that more entities make a model overly complex. I disagree. Having to manage one value across multiple instances of an entity makes a model complex.

<Soapbox 2> I believe that data modeling is 90% thinking and 10% physical labor. This can be tough for someone who likes Seeing Things Happening to understand. Give yourself time to think. Digest your model, think about it, and then let it stew for a bit. If you’re anything like me, you’ll be in the shower at 6:00 in the morning, and somewhere between shampoo and conditioner, you’ll go, “Hey! Currency isn’t an attribute!” Epiphanies are important, and fight for the time to allow them to happen. </Soapbox 2>

Now we have two good arguments for a CURRENCY entity. Let’s add it to the model:

This has turned into a pretty clean model. We’ve eliminated our Many-to-Many relationships, ensured that attributes live in the correct entities, that attributes really are attributes. I’m pretty happy with where we ended up. As I’ve said a few times, there is so much to learn about Entity-Relationship Modeling that there is no way we could cover it in a single week. But, thank you for sticking it out and reading what I have to say about one of my very favorite topics.

Now get out there and model something! It’s fun, I promise!

–Audrey

As promised: Resources!

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

Books:

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.

VOTE! No Really, Go Vote. Please. (And One Personal Endorsement)

Suffrage, noun. Expression of opinion by means of a ballot. The right of suffrage (which is held to be both a privilege and a duty) means, as commonly interpreted, the right to vote for the man of another man’s choice, and is highly prized. –Ambrose Bierce

If you’ve been a member of PASS (Professional Association for SQL Server) since June 1, 2011, you’re eligible to vote for the PASS Board of Directors this December. Don’t take this lightly! Remember in 7th grade when you voted for that witch Sarah [last name redacted] for Student Council President because she gave you a glitter sticker and a scented pencil? And you ended up with the WORST HOMECOMING DANCE EVER? Yeah, um, neither do I.

Seriously, fellow PASSonians, this is kind of a big deal. If you care about your local user group, SQL Saturday, Virtual Chapters, PASS Summit, or any other awesome thing that PASS does for us, you have your Board of Directors to (partially) thank for it. (Never forget the local volunteers… Never forget.) Last year, the voter turnout was abysmal. Truly. This isn’t some bond issue, referendum-only mid-term election! These are our leaders! Let’s take an hour out of our lives to read up on the candidates, log in to the PASS website, and choose the men and women who will steer this big old ship we call Community for a while.

We’re lucky. Really and truly lucky to have such an amazing slate of candidates. I know most of them personally, and all of them by reputation, and I’ll tell you what, I’d be happy with any combination of these wonderful people as our leaders.

  • Adam Jorgensen (B|T)
  • Rob Farley (B|T)
  • Denise McInerney (B|T)
  • Geoff Hiten (B|T)
  • Kendal Van Dyke (B|T)
  • Sri Sridharan (B|T)

But, I want to shamelessly promote one candidate right now. Geoff Hiten. He’ll be getting my vote this year. Why? Well, let me tell you why.

1) Geoff gives a damn. Really, truly, honestly gives a damn. When he says that he wants to make PASS work for its members, he means it. He and I have had quite a few conversations about this topic, and every single time we talk I walk away more convinced that his motivations are sincere. He loves this community, and he wants to protect it.

2) He knows his stuff. He’s a fairly rare combination of up to speed, technically brilliant, socially aware, and community-involved. He can reconfigure your cluster for you while discussing the presentation you’re working on while simultaneously staying in the loop on the latest in Big Data. And, when you call him for help, he’s there and committed.

3) Geoff quietly promotes other community members. I know; he’s done it for me. So subtly, in fact, that sometimes I haven’t even noticed until afterwards. (Thank you, Geoff)

4) Geoff will tell you what he thinks. Bluntly. He’s not a politician, and getting it right is much more important to him than being perceived as nice. Friends, this is an asset. He cares about getting it right. On the flip side, if you think Geoff is wrong or has missed the mark, you can tell him. He’ll listen. He’ll internalize what he’s heard from you and use it to make things better.

5) And the biggest reason why I’m voting for Geoff for the PASS BoD? Because when he finds out that I wrote this blog post, he’s going to KILL ME. Seriously. He knows where I work. He might show up. He’s going to hate that I did this, and that’s why I love this guy. He’s a great friend, amazing colleague, and I’m proud to say: Vote for Geoff!

Vote on, my friends…

–Audrey