The Anti-Buzz: Databases

by Andrew Emmott on June 15, 2013

in Anti-Buzz,Software

Antibuzz has been running as a weekly column (more or less) for three years. We have decided to give Andrew a few weeks off and run some “best of” columns from the past. This column was published originally in 2011 and has some basic info that is still relevant.

 

TIMG_0804he Buzz: A database is a big spreadsheet where you store phone numbers.
The Anti-Buzz: A database is a bunch of little spreadsheets where you store phone numbers.

Among people of my lot, it is quite common to hear non-technical people use the word database. Usually they are talking about an Excel spreadsheet they use to keep track of their local Kiwanis chapter or something similar; members’ names, addresses, phone numbers, e-mail, contributions, whether or not they were at a specific event, etc. This is invariably an elongated mess, with new columns appearing as needed.

This makes us cringe. If you want to get under the skin of any computer programmer, just show them your Excel spreadsheet and call it a database. And this happens often enough that we joke about it. It seems an anthropological study could be done on this ecology of spreadsheet databases. What bothers us the most is the fact that we can’t in good faith say that these things aren’t data bases, nor do we want to discourage people from using technology to solve their own problems.

From a purely theoretical point of view, anything could be a database. You could call your drawer full of coupons and gift certificates a database if you really wanted, (a database of savings!). It seems all you need to be a database these days is the ability to contain things and then be opened. And all you need to be an artist is to put pencil to paper. There’s a wealth of description and detail that gets lost in this colloquial understanding of databases and yet it’s not exactly wrong either; it is art-snobbery to call your spreadsheet anything other than a database.

And I don’t really see a better alternative for the average person. Heck, I have a “database” of my students, and by that I mean a text file grouping them by team and email address. The takeaway here is that it can be an uphill battle trying to discuss databases with the average person as the topic is a little more loaded than usual. I think this is because everybody understands the abstract quality of databases. The person who makes a database of their Kiwanis chapter also makes one for their community theater troupe and their roller derby team. They “get it” in other words – data needs to be organized, different data can be organized differently, and all of this can be managed in a similar framework.

True story.

So this is actually the article that I meant to write when I first started doing this for my father. It was over dinner, he tells me that dentists have various problems, such as encountering salesmen that use terms like “b-tree”. There are things we never expect our parents to say. If you are a computer scientist, one of those things is “b-tree”. When a practice management software salesman begins discussing the underlying data structures of the DBMS – yeesh, “data structures” isn’t even a term you guys should ever have to hear – this goes far beyond need-to-know terminology. This is like a car salesman talking to you about the physics behind the engine casing – I’m talking down to electrons. Just not something you care about. “Is the car a car?” Is what you want to know. “Does it have a steering wheel and do what I tell it to do?” You don’t care about how much adamantium the engine has.

So here’s my goal, to explain away some buzzwords, and to let you know what you need to be on the lookout for.

DBMS – Database management system. Think of it like an operating system. You use Windows, Mac or Linux. Or, when using a database you use Oracle or MySQL of Access or B-trieve or any number of others. Most DBMS are significantly more complicated than any OS. At the high end, the expectations of a database are far greater than the expectations of any normal computer. You want only the right people to change to the right data in the right way. You want to allow everyone to manipulate the data at once, but obviously a given piece of data must only be modified by one person at a time. If the database crashes in the middle of a transaction, you want it to recover gracefully.

b-tree, data structure, etc – A data structure is a strategy for organizing information at the programmer-level, with different strategies having different tradeoffs. A b-tree is commonly used in databases because it has advantages specific to what databases need to do at the low level. As stated, you don’t care about this part – it should be an off-limits topic with any salesman. Here’s why: your business is small. Unless you have millions of patients, which you don’t, the DBMS would have to be very very poorly implemented before you’d notice a performance difference. When computer scientists talk about large problems, they are talking about things like the human genome project. They are talking billions and trillions, or worse. There are some pretty inefficient ways to flip through your thousands and thousands of patient records, but you’d never know it because microprocessors are described in how many billions of things they get done each second. For you it just doesn’t matter.

Relational database – Anymore “database” and “relational database” are virtually synonymous in that relational models dominate the field. You’ve probably asked the question “but what does relational mean?” and received a variety of unsatisfactory answers.

Something about your data having relationships with itself. I think the concept is easier to explain from the other end: what does relational data allow you to do? It allows you to ask open-ended questions: How many patients with last names beginning with ‘S’ have had appointments on a Tuesday? List the patients using area code ‘777’ who provided email addresses in the gmail.com domain. Somebody left their hat in the lobby – list the patients who were here yesterday. Relational data is amenable to these sorts of conjunctions but so, you might say, is your theater troupe spreadsheet. However such a spreadsheet becomes cumbersome because you are forced to take in all of the information at once and the arrangement might not be conducive to the question you are asking. Like the article’s tagline suggests, the relational approach is akin to making a bunch of little spreadsheets, (Or tables, if you ever hear that term bandied about), each relating a minimal set of data to some identifier. To answer a query, these tables are composed together so that you get a “spreadsheet” of what you want.

SQL – Structured Query Language. This is the universal standard in which those open-ended questions are asked. This is also the point of contact with your database and what allows the applications you use to exist in a separate domain from your database. The applications you use get their information from your database using SQL, and use SQL to put new information in. The application is completely decoupled from the database in this sense – it could work with any DBMS as long as it was allowed to ask SQL queries. When updating a patient’s record, you aren’t actually mucking around with files like you are used to, you are seeing the results of a query, translated into nice fields, and when change what is there the application again sends a SQL command to the database telling it how to update itself.

I looked into the major practice management software, and they all use a DBMS that supports SQL queries, but what is ironic is that many of them are not relational databases. This is primarily an artifact of history, (Dentrix, for example, was in development before relational databases were the proven standard), but the juicy tidbit is that SQL abstracts your data in a relational way, and so your database might be doing a few backflips just to get that SQL support to work. Again, the luxury here is that you are small, and any lost efficiency is swallowed up.

But that they all use SQL should tell you something. You, the user, are probably never going to use SQL directly, but as someone who is concerned with the long-term support of your system, SQL enables the developers of your software to be more agile with the deployment of new features. They can switch to a new DBMS without having to start from scratch. They can implement a new view of your data without having to rewrite the database – they just have to compose the right queries. The ability to quickly adapt is the hallmark of a good software engineer. “Relational” and “SQL” might not affect your daily life, but they are a good litmus test for the caliber of developer you are dealing with as they indicate that they are really in this for the long haul.

Security – the final point I want to hit home. Security means more than the obvious things. Yes, we don’t want magical hackers getting into your records, but they shouldn’t be your only concern. When it comes to databases, security is just as much about protection from yourself. Security means an employee can’t log in an change their own salary – but you can. Security means you can’t insert a phone number as a patient’s name, or a name as a phone number. Security means that your data is guarded against “illegal” states and other data entry errors. Sometimes these features come across as pedantic, but they go a long way to keeping your records clean and error-free, which goes a long way toward keeping yourself organized and informed, which is the whole reason you signed up for this practice management software thing in the first place. This is the well I would draw from when asking database-related questions of your vendor. What features help keep me safe from error?

As a whole, databases are an incredibly rich topic, one that I don’t claim to be a master of and one that constitutes a major sub-field in computer science. One could easily devote their life to studying databases, (As many do), and never run out of things to learn. I hope the things I have given you here today have broadened you perspective a little and armed you a little better against the buzz.

by: at .

Share

Comments on this entry are closed.

Previous post:

Next post: