Pour Some Data On Me

Today’s SQL Server 2012 Anniversary question is:

I thought the image I created above might inspire you.

Follow @SQLServer on Twitter and answer their daily questions to win fame and prizes.

Good luck!

Metadata Stuffing: Why I Hate tbl_ for Table Names

This week was Canadian Thanksgiving so this post on metadata stuffing is timely.

Today Thomas LaRock (@sqlrockstar | blog ) posted a rant about our Database Design Throwdown topic on naming standards for tables and other database objects.  Tom is a fan of what I call “metadata stuffing” in object names.  That’s basically shoving as much additional information as one can into object names so that one does not have to go find out that information from its rightful place.

My reason for wanting to use prefixes is simple enough: I want to know if I am looking at a table or a view when reviewing code.

Karen doesn’t believe that anyone should be using object names as a place to store meta data about the objects themselves. I would like to agree with her but then we’d both be wrong.

He even created the nice graphic of my quote I use here. Thanks, Tom.

Object Confusion Abounds

That quote is indeed one I use during our debate.  It’s a snarky introduction to what I think the real problem is: our tools aren’t doing enough to help us with this potential confusion of tables and views.    When you are writing or looking at a query, the syntax for referencing a table or a view is exactly the same.

When a developer writes:

USE AdventureWorks;
GO
SELECT Name, ProductNumber, ListPrice AS Price
FROM Production.Product 
ORDER BY Name ASC;
GO

He has no idea for certain if Product is a table or a view.  The syntax is the same.  And if the developer is working in a text editor of some sort, or with some native tools, there’s no tooltip or other help that the he can use to check what type of object they are querying.

Why does this matter?  Tom has a great presentation where he shows the impacts of trying to make stuff work and you don’t know what you are looking at.  That’s why Tom wants to do this with his objects:

FROM Production.tbl_Product

or

FROM Production.view_Product

So that the object type is injected into the object name.  That seems so innocuous, doesn’t it?  What could go wrong?

I’m here to tell you that this is a slippery slope.  One of the most egregious examples of this sort of meta data stuffing I’ve run across is one that required all this meta data to be prefixed in front of every table name:

• tbl_ prefix
• Primary Systems that managed the table
• Primary subject area that the table belongs to in the data model
• Classification of the role the table plays in the database (Associative Entity, Domain Entity, Master Data, Reference Data, Log Data, etc.)
• Three letter login of the DBA responsible for administering this table (I KID YOU NOT).

So in this wonderful naming scheme, we’d get:

FROM Production.tbl_MFGR_ORDERPROC_DOM_KQL_Product

I have found that once an organization starts thinking of stuffing, their designs become turkeys really fast.  It’s ugly.  Think about the tools you use, with all those nifty object lists on the left side. To find a table you need to know all that great metadata as you scroll through the list, hoping that PRODUCT Is buried in there, somewhere.  And what the heck was the name of that guy that dressed funny who did all that data stuff for the company before he won the lottery?

All that metadata that should have been managed elsewhere, not prefixed in front of the “real” name of the table.  In fact, it was.  In the data model and in the system catalog. Every time any of that data changed (DBA assignment changes, DBA wins the lottery, whatever), we had to rename the table and change all the code and reports that referenced it.  Sure we could have isolated systems by this change by using views and or aliases but that is additional complexity for no performance gain, either.  Refactoring might have helped, but eventually we’d still have to change all the code and queries.

Why I Hate Metadata Stuffing

1. It’s redundant data.  Just like with business data, the reason we want to minimize redundant data is because we then have to worry about updating the data in multiple places. There’s cost and added risk for that.
2. It changes.  I don’t know about you, but I don’t have the luxury of taking down a production system just to update  a change in the name of the DBA’s favourite TV show or whatever lame naming scheme someone thought up. Sure, tables can’t change into views or indexes, but all the other type of stuffings will change.
3. It takes up real estate. I get all kinds of flack from developers and DBAs for the length of object names when I want the names to be meaningful.  It’s funny how spelling out CUSTOMER is unacceptable, but adding the exact same characters in front of every object of its type is A-OKAY.  What’s up with that?  Somehow optimizing names for developers is more important than loving your data?  Show me where it says that in the Project Charter.
4. tbl_ is a tell for bad database design.  I don’t know where this particular naming scheme originated, but when I do a database design review and I see this naming scheme, I know that the designer learned design in a one hour webinar “training course” and has not really mastered the complexities of enterprise database design and maintenance. The design will be less than best practice 90% of the time.  This naming scheme is prominent in programming books, introduction to database books, presentations by non-database people, and uninformed blog posts, by far.  It’s not popular with people who do professional database design. Sure, some products use this, too, but do you really want to take database design best practices from vendors? How many professional data architects do you think they have on staff?  I will most likely see a database design that is highly optimized to make development go faster.  Not for data integrity or loving data.
5. It’s not needed “for consistency”. One of Tom’s points is that if we are going to prefix views, we have to prefix tables to be consistent.  Actually, no, we don’t.   If we have to bite the bullet and prefix views because our tools let us down, we can choose not to clutter up tables names just to punish those objects, too.  I’m assuming that since Tom prefixes tables and views, he prefixes columns, too, right?  its just being consistent.  < snort>
6. It gets in the way of using the data. Tables and Column names are the most user-facing parts of a database design.  When we in IT insist on munging up these names with a bunch of systemese, we make it more difficult for business users to get at their data.  It shows that we have optimized the database design to help a relatively small number of technical users (developers, DBAs, ETL folks) over the needs of the business.  Ultimately, we build databases to manage data.  For the business.

What’s the Cost, Benefit and Risk?

One of my Splendid Truths is that all design decisions should assess cost, benefit and risks.  In the overall scheme of things, just prefixing “tbl_” in front of a table name isn’t that costly and it isn’t that risky.  Tom assesses his designs based only on potential for performance harm according to his post.  He “laughs” at my position.  I’m happy that my stance on metadata stuffing brings happiness to his day.  But performance is only one data point out of many for making a design decision.  Usability, clarity, business goal support are other factors that a database architect needs to consider when assigning a name to an object.  If we optimize something for a subsystem, we do it as the expense of other subsystems.

Our Tools Should Help Us More

Having said that, I feel the pain of people having to work with sub-standard tools or having to use tools that just refuse to help.  Tom showed how SQL Server Management Studio tooltips can help.  But all those command line “I don’t need any stinking help” aficionados are left on their own to know what they are looking at.

Oh, and Tom:

ProperNoun_Tom, Pronoun_you Verb_made Pronoun_me Verb_laugh Preposition_with Possessive_your Noun_post.

(See how all this stuffing gets in the way?)

One of my other Splendid Truths about database design is:

Your tools will impact your data models and database designs more than you can imagine.

We shouldn’t sit back and let that happen. Stuffing is great with Tofurky, not with databases.

Thinking This Might Be a Useful Set of Rules for PASS Summit Sessions…

Recap: #SQLSat157 San Diego – Space and Data

This past weekend I attended SQL Saturday San Diego, AKA, #SQLSat157.  This was my first time speaking at this event and I want to give lots of thanks and kudos to the organizers for putting on a fine event.

Because I arrived in town early to meet with friends from both the space and data world, I was able to visit the San Diego Air and Space Museum.  It was fitting that it was the 50th anniversary of President Kennedy’s Rice University speech on space exploration:

There is no strife, no prejudice, no national conflict in outer space as yet. Its hazards are hostile to us all. Its conquest deserves the best of all mankind, and its opportunity for peaceful cooperation many never come again. But why, some say, the moon? Why choose this as our goal? And they may well ask why climb the highest mountain? Why, 35 years ago, fly the Atlantic? Why does Rice play Texas?

We choose to go to the moon. We choose to go to the moon in this decade and do the other things, not because they are easy, but because they are hard, because that goal will serve to organize and measure the best of our energies and skills, because that challenge is one that we are willing to accept, one we are unwilling to postpone, and one which we intend to win, and the others, too.

Not only is this fitting for motivating a generation to invest in space exploration, it’s fitting for professional development work, too.  We attend and speak at SQL Saturdays not because it’s easy, but because we need goals to serve to organize the best of our energies and skills. I can’t tell you how many times I’ve been inspired to learn something new because I saw a fellow community member demonstrate how it could help make life for end users or co-workers better.  And SQL Saturday gives me a full day of these sorts of workshops and demos…all for free.  How great is that?  It means giving up a Saturday and for those of us who travel to speak, 2-3 days plus expenses.  And yet every time I leave one, I think "That was so worth it".

Sessions

I spoke three times at this SQL Saturday: DB Design Throwdown, the Women in Technology Panel, and Career Management for Data Professionals.  Between those, I was able to see just a couple more sessions.  I really enjoyed Lynn Langit’s (@lynnlangit | blog)  NoSQL for the SQL Server Developer.   Lynn did a fabulous job explaining the differences between SQL and NoSQL technologies, as well as demoing MongoDB and cloud-based technologies.  You should spend some time on her blog; she has a lot of great stuff with plenty of videos and demos.

I also had the pleasure of being on the WIT panel with Lynn.  This panel, moderated by Tara Kizer, focused mostly on how we can energize the next generation of girls (and boys) to be interested in IT careers.  Lynn is doing some fabulous stuff over on http://teachingkidsprogramming.org, where she and her partner, Llewellyn Falco (@llewellynfalco | blog )  are building a framework for, well, teaching kids programming.  

I talked about the importance of talking with girls in your life, which is my usual homework assignment for attendees.  Having someone in the IT profession share the fact that the industry isn’t just about typing and programming can make a real difference to a girl who just needs to hear that IT professionals can make a difference in the world.  In fact, I have another blog post coming up soon on that topic.

Download the Database Design Throwdown: The Trailer presentation.

Download the Career Management for Data Professionals presentation.

Join me in Kansas City this week — it’s a throwdown!

I’m headed to Kansas City this Thursday to speak at the Kansas City SQL Server User Group and then on Saturday to present at the Kansas City SQL Saturday. I’d love to see you there.

On Thursday, 2 August I’ll be debating with Tom LaRock (@sqlrockstar), giving a preview of our SQL Saturday presentation of Database Design Throwdown: The Trailer.  In this wonderful smackdown, I’ll be talking about the importance of data quality, integrity and data governance while preparing database designs. I’m pretty sure Tom will be spouting wildly crazy, kooky ideas about performance, optimizing design to make life easier for DBAs and … I have no idea what else.  Probably bacon.  We won’t be giving the same presentation as on Saturday — it will be more of a trailer version of that.  Oh, wait…that doesn’t quite sound right.  It will be a teaser.  Yeah.  Something like that.  A teaser.

Kansas City SQL Server User Group

Details about the SQL Server User Group meeting:

Time

• 3:45 – 3:50 Greeting and Housekeeping
• 3:50 – 5:00 Database Design Throwdown: The Trailer
• 5:00 – 5:15 Door prizes and wrap up

Topic: Database Design Throwdown: A Trailer
Date:  August 2nd, 2012
Speaker: Thomas LaRock versus Karen Lopez

Overview: If a man is alone in the forest and there is no woman around to watch him design a database is he still wrong? Join us in this highly interactive debate regarding the options and best practices of common and advanced design issues such as natural versus surrogate keys, NULL versus NOT NULL, data quality versus performance, and others. Bring your opinions and experience and join the discussion.

About Speakers:

Thomas LaRock is a seasoned IT professional with over a decade of technical and management experience. Currently serving as a senior database administrator with Confio Software, Thomas has progressed through several roles including programmer, analyst, and DBA. Prior to that, he worked at several software and consulting companies, working at customer sites in the United States and abroad. Thomas holds a MS degree in Mathematics from Washington State University and is a member of the Usability Professional’s Association. Thomas also currently serves on the Board of Directors for the Professional Association for SQL Server (PASS), is a SQL Server MVP, and can also be found blogging at http://thomaslarock.com and is the author of DBA Survivor: Become a Rock Star DBA (http://dbasurvivor.com).

Karen Lopez 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.

Location:

8700 State Line Road
Suite 200L
Leawood, KS 66206  (map)

Data Model Driven Database Design

On Saturday, 4 August I will be presenting at the Kansas City SQL Saturday (aka #SQLSat159 — don’t get me started on why they use a surrogate key as their names for these…) on Model Driven Database Design

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?". Demos, too. Finally, 10 tips for making model-driven database development successful in your organization’s culture and environment.

Session Level: Beginner

Location: Cerner Corporation’s Riverport Campus, 6711 NE Birmingham Rd, Kansas City, MO, 64117

And Tom and I will be doing our full debate on Database Design: The Throwdown, as described above.  Registration is required for the SQL Saturday, but it’s totally free – you get swag, prizes and access to some of the best speakers in the SQL Server community.  I attended this last year in Kansas City and they did a fabulous job.  You want to be there, too.

Be The Next Microsoft Employee: @Data_Model and @VenusBarbie get makeovers – Behind the Scenes

As I shared with you previously, I’m a guest judge on Microsoft Learning’s new reality show, Be The Next Microsoft Employee.  In this contest, four SQL Server DBAs compete for a chance to work at Microsoft.  They have to go through all the normal Microsoft interview processes PLUS compete in front of cameras, crew and the Internet on a series of data-related challenges. The challenge I participated in, filmed on the Microsoft campus, was the last one and it was a doozy.

It should be no surprise to you that I took the Technical Barbies (@Data_Model and @VenusBarbie) along for the filming.  Since this was a hiring contest, I also brought along Working Woman Barbie, who comes with a suit that also turns into a glittery dance dress for after work fun.  Working Woman Barbie can talk, too.  She says fun things, but she talks too much about Ken.

When we arrived at the shoot, the first thing the Barbies got was a professional makeover.  Stylist Mimi Pettibone of StellarStyle.com has previous professional experience styling Barbies and action figures.  HOW LUCKY WAS THAT?  Mimi also gave me some great tips about styling and posing the Barbies.  Fishing line seems to be a key component.  I also showed her how I used clear braid rubber bands to help keep Barbie from losing her cell phone and shoes.  Just like I do.  Or should.

I live by a man’s code, designed to fit a man’s world, yet at the same time I never forget that a woman’s first job is to choose the right shade of lipstick.
- Carole Lombard

I’m not sure I agree with Ms. Lombard there, but both the Barbies and I had to wear lipstick for this shoot.  That’s how I knew I was on a reality show…just a different type of reality.

We took some before and after pictures.  I think I pretty much looked the same as VenusBarbie.  Mimi worked wonders with me.

Mimi did a fabulous job, don’t you think?