Browsing articles tagged with " data modeling"

Let’s Talk Agile Development and Data Modeling…Friends or Frenemies?

Jul 18, 2012   //   by Karen Lopez   //   Blog, Data, Data Modeling, Database Design, Events, Speaking  //  2 Comments

I’ll be joining host Graeme Simsion (@graemesimsion) and panelists  Terry Buino, John Giles, and Chris Woodruff for a lively and (I’m hoping) contentious discussion about Data Modeling in an Agile Environment.  This free webinar event will be hosted by as part of their monthly series on data management issues.

We invite you to join us in this monthly DATAVERSITY webinar series, “Big Challenges with Data Modeling” hosted by Graeme Simsion. Join Graeme and two or more expert panelists each month to discuss their experiences in breaking through these specific data modeling challenges. Hear from experts in the field on how and where they came across these challenges and what resolution they found. Join them in the end for the Q&A portion to ask your own questions on the challenge topic of the month.

We four panelists come from a variety of backgrounds.  Two of us are Microsoft MVPs, one just wrote a book on Agile Data Modeling and another calls himself a born-again agilist.  Graeme is always the stimulating and controlling jocular host at these events…he’d have to be to manage the characters he has to herd in just a short period of time.

Many data architects and modelers tell me that they can’t or won’t work on agile projects.  Or that they’ve heard that there are no data models in agile approaches.  Worse, they’ve attended presentations by certain industry pundits who have been so anti-architecture that they don’t understand why anyone would attempt this.  But it’s not that way in practice.  Most of my projects over the last few years have been agile or SCRUM.  So I’m going to bring my experiences and stories to discussion, along with tips that I have for working on a software-focused delivery methodology. 

Find out whether agile is your friend or frenemy.

All you have to do is register to attend.  It’s free, too.

214-748-3647, Hello…Is it Me You’re Looking For?

Jul 17, 2012   //   by Karen Lopez   //   Blog, Data, Data Modeling, Database Design, WTF  //  No Comments

A developer, Justin Reese, just shared his own story about using the wrong datatype for phone numbers, over at DailyWTF.  He did this in an XML document, but I see the same mistake being made over and over again in data models and database designs.  In fact, this is a key part of my Data Modeling / Database Design Blunders presentation.  

My client reported that was a strange bug on a certain page in an app I built for them. Where the contact information for a series of offices was being displayed, all the information was correct except for one piece: the phone number. For multiple locations, the phone number displayed was the same: 214-748-3647.

I love reading about his quest to track this problem down and what the issue turned out to be.  I also love that he wrote a DailyWTF about himself.  We all should be doing that: sharing our mistakes so that others can learn from them.  I call that "Free Advice That’s Paid For" in my blog posts. 

In my presentation my first blunder is using numeric datatypes for data values that aren’t actually numbers.  Telephone numbers are one of them.  They may have leading zeros.  We don’t do math on them, usually.  ZIPCodes are another example. Store them as INTEGER and you’ll lose leading zeros.  And many Postal Codes have letters.  Think you have only US customers? You might.  But customers, people who may owe you money, have a way of moving around.  Of course, every design decision comes down to cost, benefit and risk.  So some designs may make a good case for using numeric datatypes for storing values that aren’t actually numbers. But all the protections for data quality and correct retrieval need to be designed in, too.  That’s the trade-off.  Also in my presentation I give a rule of thumb:

If business users call it a number, it ain’t a number. 

SNAGHTML39344e3bCustomer Number. Account Number.  Vehicle Identification NumberSocial Insurance Number.  Social Security Number (yeah, it’s all numbers now, but nothing would stop the powers that be from changing that).  This is especially true for numbers that are managed by people outside your organization.  You just don’t know when they might decide to add letter or special characters.

I get feedback from at least one person at each presentation that my blunders are way too obvious or that they aren’t serious mistakes.  As much as I see poor or inaccurate datatype selection, I have to politely disagree.  These are the number one mistakes I see.  They compromise data quality, lead to tragic data errors, even.  Storing numbers that in fact aren’t numbers as INTEGERS or other numeric datatypes is error prone, leads to nasty slow queries due to all the casting and table scans that may happen.  Eventually, those incorrect data values are going to come looking for you.  Usually after work hours, in production. If you’ve never seen them in the wild, then either you don’t get out enough of you’ve been blessed by working with highly competent data modelers and database designers.  And we all know how rare those are.

Thanks to David Maxwell (@dmmaxwell | blog) for the pointer to this WTF

Join Us: Webinar Panel on Big Challenges on Data Modeling 24 May 4PM EDT

May 23, 2012   //   by Karen Lopez   //   Blog, Data, Data Modeling, Events, Speaking  //  1 Comment

I’ll be part of a rockstar panel of data modeling experts this Thursday, 24 May for  Graeme Simsion will be moderating us (it’s worse that herding cats stoned on catnip) as we talk about our biggest challenges in the data modeling world.

You need to register before joining, but it’s free.


  • David Hay: President of Essential Strategies (Resident curmudgeon)
  • Alec Sharp: Sr. Consultant, Clariteq Systems Consulting (Process guy who still loves data)
  • Karen Lopez: Sr. Project Manager, InfoAdvisors (Well, that’s me)


You can join the pre-show chat about 15 minutes before show time; I encourage all of you to join early and get your heckling and questions warmed up for the recording.

Hope to see you there.

Deadlines and Data Architects: You know the Date, Do You Know the Deliverable? #MemeMonday

Feb 6, 2012   //   by Karen Lopez   //   Blog, Data, Data Modeling, Database  //  7 Comments

It appears I’m an expert at deadline-driven accomplishments.  I mean, it’s meme Monday and even though I’ve known about the deadline for today’s post for weeks, I’m writing it at lunch on said day.  I’ve been thinking about what I’d write about for all this time, so it’s not like I’m just starting…but still, the deadline is what making this post happen.  I wish I could be the type of person who gets stuff done because it just needs to be done, but deadlines are what drive me. 

The ultimate inspiration is the deadline.
-Nolan Bushnell

I’ve been thinking about some experiences I’ve had working with other data architects and working with deadlines.  Usually as part of a team, we don’t get to set our own deadlines; they are set by a project manager or if we are lucky, via negotiation with the DBAs, developers and other architects on the team.  Our deliverables are typically the input into their deliverables, so their success is depends our getting our deliverables to them.  In my experience, though, it’s too common for data architects and data modelers to forget what deliverable those teams are waiting for or for them to make it available in the right format. 

In his post for #mememonday, Thomas LaRock (@sqlrockstar | blog ) give his best tip for working towards a deadline: work backwards.

What is Your Deliverable?

imageThis is the most frustrating question.  It should be very apparent to a data architect what deliverables the team needs from them.  But time after time I see modelers focused 99.999% on the data models.  Yes, your data models should be beautiful.  They should be complete, correct and pretty.  Every single one of them should get a trophy. All the meta data you want to capture about them should be in the data model.  Do you think, though, that your development DBA and developers are tapping their feet waiting for the PDFs of the data models?  Or do you suppose that they just might want that alter script to run in their environment so that they can get to work on their deliverables? 

Ideally, the team wants both.  They want the model to be created and they want the scripts with all the changes needed for their next deliverable.  I can assure you, though, that the scripts are what they want first.  I can also assure you that no one will love the data models more than you do, Mr. Data Architect.  So embrace the love you feel for the data models and funnel it into getting the "real" deliverable done:  DDL.  Sure, it hurts a bit that they don’t love your models as much as you do, but they will learn to love them because they produce beautiful databases.

On most my projects (mostly development ones) I never lose sight of the fact that ultimately, the data models are a method to get to good quality databases and data.

What Makes for a Good Deliverable?

I worked with a data architect who was very passionate about her data models.  She spend days making them beautiful.  She filled them with all kinds of nifty meta data to help people understand their data better.  She wrote definitions that were paragraphs long, then added more notes to them just to make it even better.  She often missed review meetings because she was too busy making a more beautiful data model.  She missed deadlines for delivering DDL because she wasn’t quite sure on how to do that.  She researched data modeling layout techniques to make models more readable.  She demoed different layouts and took surveys about the alternatives to find just the right one.  And she got further and and further behind on delivering revisions to development, her only direct customer of her work.  The team had gone from every other day deliveries of fixes and enhancements to the development databases to every other week.

Our modeler didn’t have time to learn the features of our target DBMS because was busy making the model a perfect data model. Our sprints were failing because database changes were coming too slowly and were often done incorrectly.  The DBAs and developers had to spend days cleaning up the DDL she had produced because she didn’t know how to use those features of our modeling tool.  So not only did she miss the deadlines, she missed the deliverables.  She didn’t understand this, though. In her mind, the data model was all anyone needed.  In reality, what they really needed was working DDL scripts to apply to their environments.   That was what good meant to the team.

What Format?

I worked with another data architect who would spend months working on the data model, then publish it inside a PDF of a Word document.  The actual data model file was not shared.  No DDL was shared.  Nothing was delivered that teams could actually import into their tools or apply to their local development environment.  This also meant that it was nearly impossible to comment on the data model or easily provide corrections or feedback.  The modeler wanted corrections to come to him in emails and he would make them and generate the Word documents and PDFs months later.  Requests for the data model file itself were met with "You don’t need that, just retype the model in whatever tool you use".  We can’t build stuff on PDFs.  Produce them, sure.  But they aren’t the deliverables we are looking for.  Your team (your customers) can tell you what format works best for them.  Just ask.

Where Do You Deliver the Deliverables?

I work with many data architects who don’t use the same deliverable locations as the rest of the project team and I think that’s a huge failure.  Professional development teams use some sort of versioning or configuration control environment to share their work product.  Data architects should use those, too.  Yes, we have our repositories and model marts, but those are typically only accessible by people who use the modeling tools.  They are my versioning control for the models, but I also deliver to the teams in the same place they expect to find all the components of their systems.  Maybe it’s just a wiki, or an intranet location. Where ever it is, I’m delivering there.

I worked with a data modeler who refused to use or learn the team’s versioning system.  So he just emailed around files, with no version numbers and expected people to be able to search through their emails to find scripts to deploy them in production environments. He seemed to randomly include and exclude people in the distribution list.  He often just attached a bunch of files, then said "here they are" in the body of the message.  Email is the worst versioning system in the world.  Don’t use it for deliveries.

Another approach I find annoying is fairly new, though.  I work with a an architect that keeps all the files he works on for several clients on Dropbox. All in one giant folder.  When he gets his work done,  he sends out an email saying: it’s all on Dropbox.  We as a team have to try  to figure out what the files are, which is the right version and try to ignore all the other stuff that sitting there in the folder.  As afar as I’m concerned, he might as well randomly generate his data models.

Work Backwards

If you have the answers to those questions above, you can work backwards to meet your deadlines.  My inefficient team members mentioned  above don’t do this.  They start with a generic to-do list of how to produce data models, start at step one and work their way as far as they can before they hit the deadline.  They often miss deadlines because they haven’t started at the end and worked backwards.  Don’t be that guy (or gal).  Know what you are expected to deliver, when, where, and in what format.

I start with what my deliverable is, what format I need to produce it in, and what measures I should use to ensure it’s good enough.  Then I start there with the tasks that will get me closest to that completion.  It’s only when those are completed do I keep working backwards to fill in the other high priority tasks.  If I still have time left, I do more.  I make it prettier.  I make it more useful.  I make it beautiful.  I love it.

Not all data architecture is done on development projects.  I understand that.  If your duties include supporting development teams, though, you need to support them.  That means loving your data models, the data AND databases.  There’s no reason why you can’t have it all.  Just remember which parts you’re supposed to deliver first.

Have you worked with data architects or modelers who worked backwards and got the job done?  What about people like I’ve mentioned in this post?  What would you wished they had done instead?

7 Mistakes You *CAN* Afford to Make

Jan 2, 2012   //   by Karen Lopez   //   Blog, Data, Data Modeling, Fun, Snark  //  No Comments

I just saw that my article on Enterprise Data Modeling: 7 Mistakes You Can’t Afford to Make at Information Management magazine was on their list of the Top 10 Stories of 2011.    In fact, it made it to the top five stories. My friend Corey Smith ( blog | @heycorey ) then took it upon himself to ask

I’d like to know the 7 mistakes I *can* afford to make, so I can stop worrying about them?

Tweet by Corey

He wasn’t specific about whether he meant in enterprise data management or not, so I’m responding in general. These are all mistakes, but one can afford to make them, in moderation.

  1. Peanut butter: I’m not a fan of it, especially when it’s hidden in other stuff like pretzels and vodka
  2. Rounding Up:  Mathy people say that you should round up sometimes and round down other times.  And sometimes you don’t.  I say, make everyone happy and round up, right?
  3. Non-Alcoholic Beer:  I have to say that this stuff is usually quite bad, which is why it is a mistake.  But sometimes you need to have a beer and this is all they will let you drink. Like at lunch in the US or between real beers in the rest of the world.
  4. Twitter Grammar: 140 chars is some*s 2 short 2 say something 1derful.
  5. Running: What can I say?  I do it, and I want to do it more. It always feels like a mistake when I start doing it.  But I can afford to do more.
  6. Bacon: I prefer the veggie kind.  Others don’t.  We both feel as if the other is making a mistake.  That’s okay.
  7. February: It’s post holiday season, it’s not really spring and hardly anyone can pronounce it correctly.  But everyone has to do it.

So there you go, Corey,  your list.

Everyone else, go check out my article as well as all the other Top Stories in from Information Management.

Join Me & Panel of Data Modeling Experts on BI and Data Governance – 16 Nov – Noon EST

Nov 10, 2011   //   by Karen Lopez   //   Blog, Data, Data Modeling, Speaking  //  No Comments

Expert Roundtable: Data Modeling. Using ER/Studio and Models to Aligh with Governance and BI Initiatives

I’m moderating a panel on BI, Data Governance and Data Modeling using Embarcadero ER/Studio on 16 November 2011, at noon EST.

Panelists include

  • Kevin Phelps – Data Architect, American Century Investments, Inc., 
  • Chris Bradley, Business Consulting Director, Information Processing Limited
  • Jason Tiret – Director of Modeling and Architecture Solutions, Embarcadero Technologies

In this webinar, you’ll learn how to:

  • Increase visibility and buy-in with business users
  • Ensure smooth compliance with changing data privacy regulations
  • Enable data stewards to meet requirements strategically

What makes this even more interesting is that audience members, like you, will have a chance to submit questions beforehand and during the event.

Details of the event:
Wednesday, November 16, 2011
9 a.m. Pacific, noon Eastern and 5 p.m. in the UK



When you register you’ll be asked to submit your questions.  Please do so.  We love having questions from YOU!

I’m deeming the hashtag for this event to be #DMExperts  I will try to take Tweeted questions, too. 

Speaking: #SQLSat96 Washington, DC …well… Chevy Chase, MD 5 Nov

Nov 2, 2011   //   by Karen Lopez   //   Blog, Data, Data Modeling, Database, Speaking  //  No Comments

Washington Metro Escalator

I’ll be presenting Model Driven Database Design on Saturday, 5 November at the SQL Saturday Washington DC. Which is being held in Chevy Chase, Maryland.  Yes, I know this is the greater DC area.  Last year it was held in Reston, Virginia. But these data things drive data architects crazy.  That’s why you really don’t want to live with a data architect.  Ever.

Anyway, I’ll be presenting a brand new presentation that covers:

Model-Driven Database Design

Model-Driven Database Development: Myths, Magic and Methods. In this presentation, Karen discusses data model-driven database development from the point of view of the Data Architect, the DBA, and the Developer. She will cover topics such as "Who does what?", "Why are we doing this?", "Do I have to Use a GUI?" and "Just who do you think you are?". Finally, 10 tips for making model-driven database development successful in your organization’s culture and environment.

Session Level: Beginner

Karen Lopez

Speaker photo Karen López is Sr. Project Manager and Architect at InfoAdvisors, Inc. Karen is a frequent speaker at conferences and user groups. She has 20+ years of experience in project and data management on large, multi-project programs. Karen specializes in the practical application of data management principles. Karen is also the ListMistress and moderator of the InfoAdvisors Discussion Groups at

I’m up against some fairly big name speakers, so I’m really hoping to see you in my session.  Both of us can have a great time talking about data models.  See you there.

Subscribe via E-mail

Use the link below to receive posts via e-mail. Unsubscribe at any time. Subscribe to by Email