Browsing articles tagged with " Database Design"

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

She’s Cute, So it’s Okay…

Jul 12, 2012   //   by Karen Lopez   //   Blog, Data, Data Modeling, Database, Snark  //  No Comments

image

…and, yes, this is quote from one of my former DBAs.

Clustered Indexes and UniqueIdentifiers

Jul 11, 2012   //   by Karen Lopez   //   Blog, Database, Database Design, Fun, Snark  //  No Comments

Tom LaRock (@sqlrockstar) posted today about clustered indexes on uniqueidentifiers.  He has a great image there, which got me thinking about making my own….

 

image

Normalization Myth 142…

Jul 10, 2012   //   by Karen Lopez   //   Blog, Data, Data Modeling, Snark, SQL Server  //  No Comments

image

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 www.infoadvisors.com.

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.

Speaking: DAMA IA (Des Moines) Oct 18 – 10 Database Design Blunders

Oct 4, 2011   //   by Karen Lopez   //   Blog, Database, Speaking  //  No Comments

My next stop on the DAMA chapter speaking tour is Des Moines DAMA Chapter, where I’ll be presenting on 18 October.

 

10 Database Design Blunders

What’s going on in your physical data model? How many people can or will update it to match the reality of what’s going on in your databases? Who decides what goes into the physical model?

In this presentation we discuss 10 physical data modeling mistakes that cost you dearly. Will your physical design lead to performance snags, development delays, bugs and weakening of professional respect?
Data Architects are often tasked to prepare first cut physical data models, yet these skills usually overlap those of DBAs and Developers and this overlap can lead to contention, confusion, and complacency. With this presentation, you’ll learn about the 10 blunders, how to find them, plus 10 tips on how to avoid them.

More information should be available on their website soon about the location and timing.

Pages:«1234»

Subscribe via E-mail

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


Categories

Archive

UA-52726617-1