|
||||||||||||||||||||||||||
For a programmer or a technology enthusiast, the concept of a database is something that can really be taken for granted. Maybe you’ve read Christian’s guide on how MySQL works for WordPress, or my article on using SQLite Database Browser to learn how to use a simple database. However, for many people the concept of a database itself is a bit foreign. SQL calls, queries, tables, records, keys – all of those terms and more make up the field of database design and database management, and there’s not a whole lot that’s simple about it.On the other hand, the concept of a relational database – in the simplest form – doesn’t really have to be overly complicated. Before you assume that this isn’t something you need to know, consider that just about every day of your life you interact with a database of some form. When you take money out of the ATM, purchase an item using your frequent shopper card, or badge in at the gym, you are actually populating some table or tables inside of a database.
So, why should you care? Well, because once you appreciate how a database works and how data that seems to be completely independent can be correlated with other data, you will start to really appreciate – and hopefully be more cautions – of how those interconnections can come back to haunt you.
How did that other clothing company know to send you a catalog of formal wear less than two weeks after you just purchased a suit? How did the car dealer know three years after you purchased a car, that the warranty is almost up and to send you an offer of an extended warranty? It’s all about queries, correlations between data, and doing something about the results. That’s the magic of a database.
How a Database Works
So, if you’re a database administrator, or a programmer that can write an SQL statement with your eyes closed, this post will probably bore you to tears. But, if you’re completely new to the concept of databases, or you’re curious how they may be a part of your life without you even knowing it, then this is the article for you.To explain how a database works, I’m actually going to use Excel. Excel is an excellent tool to create a spreadsheet, but a spreadsheet is simply a collection of what would be called “tables” in a database. A table is exactly what it sounds like, a table full of data that’s organized by field.
In our example, we’re going to pretend this is the database of a gym. This fictional gym has a membership, and we can pretend that the single table above is the information that gets populated whenever a new member signs up with the health club. Part of the sign-up process is weighing in, so that the club can help you come up with new ways to manage your weight and your health. In fact, the health club actually has an awesome cafe with health food on the menu, members can have a bite to eat of something healthy right after a workout, or grab a drink right before.
Since this is a very high-tech club, they’ve also added another table to their database. This next table gets new data added every time a member purchases something at the healthfood cafe. This transaction (another database term for the addition or subtraction of a record) takes place right at the register. You will notice that between the two tables, there’s some similar data, like the member name. There’s also unique data, like the transaction ID and the date and time.
The unique data helps to keep all of the information organized, while the common data between all of the tables is what helps programmers to connect-the-dots, so to speak. I’ll show you some logic that goes into that in a moment, but first, the club needs to add to it’s ever-growing database. Obviously, when members come into the club or leave the club, they need to use their club id to “badge” in and out using the digital scanner. This action fills out yet another table. This new table simply keeps track of when the member checked-in and checked-out of the health club.
So, there you have it. A very simple database that the health club put together. Three basic tables with very unique uses. One is the membership sign-up list, the other is a record of health club store purchases, and the third is the sign-in and sign-out times of each member. These are all seemingly unrelated information, right?
Well, the magic of a relational database is that you can extract information from each of these tables and then correlate them together to come up with some really interesting data. For example, let’s say the club wants to figure out what sorts of food the heaviest members are eating at the cafe. They could easily figure this out by running a “query” against the database, asking the membership sign-up table for the weights of members over a certain weight – let’s say 200 pounds. Then, you would ask the cafe purchasing table for the purchases of those people that are over 200 pounds.
When you run such a “query” against a database, it provides the results in a sort of “temporary” new table. Here’s what such a new table looks like. It’s a listing of what the club’s heaviest members bought to eat and drink at the health club cafe.
The query language is actually pretty simple, for something of this nature. In Microsoft Access, for example, if the membership table is called “membership” and the cafe purchase table is “purchase”, the query might look like this: “SELECT purchase.member, purchase.description, purchase.date FROM purchase WHERE membership.name == purchasing.name AND membership.weight > 200″
Now, when you start looking at the information collected throughout the health club, you can see what types of things the club could figure out. Do people who sign in at certain times of the day tend to buy more at the cafe? Do people of certain age groups tend to check in more often at certain times of the day? All of this information will help a business implement things like advertising or marketing campaigns.
Other Types Of Databases
Now that you know how a database works, what sorts of databases might you come across during your everyday life? Well, ever time you visit most blogs or websites, and either post a comment or sign in, you’re interacting with a database. In the case of WordPress, the database looks something like this.
As you can see, there are a bunch of tables – 15 in this case – to hold all sorts of information like user details, post information, comments, and more. This is the backbone of a WordPress blog, and many other advanced sites have similar database back-ends.
When you open up one of those tables, like the posts table for example, you can see that all of the information that gets displayed on the actual web page is stored right inside of a database.
If you’re a gamer at all, you’ve probably interacted with a database. Some games are more heavily dependent on a back-end database, and probably the most database-intensive games are multiplayer online games. For example, if you’re familiar with the massive online space game OGame, that is one example of such an online game that is hugely depending upon a database to run the show. Just the playerlist for any of the worlds are clearly laid out just like you would expect in any database table.
Game databases hold everything from user scores and achievements to current game item statistics or inter-relationships between characters, objects and more. Lots of games would not be possible at all if it weren’t for the back-end database to run the show.
Another place you’ve surely come across a database is when you shop online. See those nicely formatted product results that are listed on Amazon after you run a query looking for a product?
Yup, those were extracted from a database, using a query like the example I showed above (albeit slightly more complicated of course). And when you go ahead and decide to move forward with that Amazon purchase, you can be certain that your purchase details and history is populating yet another table in Amazon’s massive database.
Another place you may not suspect depends upon a database is your own computer registry. This is actually called a “hierarchical” database, because as you can see when you browse the registry, the information is stored in more of a hierarchy than it is a relational format. However, the premise is the same.
People can actually write up queries to extract information from the system registry that look just like database queries, except the lookup “table” that’s used is a KeyName and the “ValueName” is the actual data stored in the database for that key.
So, you don’t really need to know how to program a database or even how to use one, but by being aware of the volumes of data that these databases can store, and how easily they can be used to correlate information that seem very distinct in the real world, you can stay alert to the type of information you may want to share (or not share) with businesses.
Did this explanation of database design help with understanding how they work in your own life? Are you surprised how easy it is to connect-the-dots for things that you do every day? Share your own thoughts about databases in the comments section below.
Image Credits: Symbolic Data Exchange via Shutterstock
