Introduction
This month’s T-SQL Tuesday is being hosted by Rob Volk. The subject is analogies. As Rob describes it, “how would you explain database concepts to someone who’s not technologically savvy“. This post will be my contribution for T-SQL Tuesday #131 – Analogies.
Once again, this is a pretty wide area, that gives everyone plenty of possibilities for topics. This is good, since I think it will encourage more people to actually write a post. Just writing a useful blog post, and getting quality links back and forth from other related blogs in the community is good for everyone who participates!
BTW, reading and then commenting on the T-SQL Tuesday blog posts of other participants helps both you and the other blogger. They get views and comments, and you get backlinks to your blog.
In my case, I am going to discuss a few analogies that I have used when I was teaching beginning SQL Server classes.
T-SQL Tuesday #131 – Analogies
Here are a few analogies related to databases that I have used over the years.
Spreadsheets and Tables
One obvious analogy is the similarity between a spreadsheet and a table, at least at a visual and conceptual level. Most people who have had at least some exposure to computers have probably seen a spreadsheet, even if they have not ever really used one. If they haven’t, you can fire up Excel, and show them one.
Once you have Excel up, showing a worksheet, it is easy to talk about the idea of rows and columns, and how that is one way to think about a table in a database. After all, like it or not, many people (and organizations) use spreadsheets as databases. Many SQL Server database tables started out life as an Excel spreadsheet. That is why you often see non-normalized database tables with 50 or more columns!
After looking at a spreadsheet, I will often bring up the Table designer in SSMS! I know many people will be horrified by that idea, but using the Table designer to teach some basic concepts can be helpful. I always give the standard caveats about the evils of the Table designer…
If you are teaching beginning SQL Server, you can use the Table Designer to talk about data types, nullability, and naming standards. You can also talk about why you should not be just creating your table designs on the fly at the keyboard, with no data modeling or analysis.
If you are not trying to teach SQL Server, you can just query a table, showing what comes back, and how that relates to what data is stored in the table.
Indexes and Phone Books
Many technical people who are not DBAs sometimes have the idea that database products like SQL Server are just magical, and that they can quickly retrieve data no matter how the database was designed. Anyone who has been a DBA has probably encountered many tables that were poorly indexed or had no indexes at all.
One analogy (that is not as relevant to younger people) is the old Yellow Pages phonebook. Back in my day, you used to get a very thick Yellow Pages phonebook that had listings (often including display ads) for most of the businesses in your area. You would get a new book every year, dropped off at your front door.
These listings were in alphabetical order, by business or product type. If you needed some product or service, you would go try to find it in the “yellow pages”. So, lets say that you needed a plumber. If you knew how the yellow pages were organized alphabetically, you would quickly go to the pages that listed businesses or services starting with the letter “P”, then “Pl”, until you found plumbers. This would not take very long.
If you had no idea how the yellow pages were organized, you would open up the book and go to page 1, looking for plumbers. Of course you would not find any plumbers on page 1, so you would go to the next page, looking for plumbers. This process would continue until you got to the page(s) that had plumbers. If you had just a little common sense, you would stop after the plumbers section. Otherwise, you would go through the rest of the pages in the book, just to make sure you found all of the plumbers…
Unfortunately, a database table with absolutely no indexes at all pretty much forces the query optimizer to do a table scan, looking at every row in the table trying to find whatever you asked it to find in a query. Even after it finds the plumbers, it still has to look at all of the other rows, just to make sure it didn’t miss any plumbers. This will obviously take more time and use more resources than if it somehow knew how to quickly find all of the plumbers. The “magic” of an index!
Final Words
I hope that these examples have been interesting and useful! I also want to thank Rob for hosting this month.
If you have any questions about this post, please ask me here in the comments or on Twitter. I am pretty active on Twitter as GlennAlanBerry. Thanks for reading!