Databases Demystified - An Executive Overview

I'm very fortunate to work at a company where both the business and marketing teams have a solid grasp on how databases work. They may not know the technical specifics or how to achieve a desired result, but they understand the basic structure of data and usually know ahead of time what is and what is not feasible. This makes my life and that of my development team much easier.

In an effort to promote this trend, I'd like to break down the basics of what a database is and give some high level examples of how they work. Before you get mad developers, know that I'm purposely going to oversimplify things.

What is a Database?

The most helpful way to think of a database is to picture an Excel spreadsheet. When you open up Excel, you have a blank grid made up of rows, columns, cells, and pages.

The columns of a database are the basic structure of data. They make up the full list of data elements you want to store. For example, if you want to create a database that stores a person's name and address, you might create a database table with the following columns:

• ID (more on this later)
• First Name
• Last Name
• Address
• City
• State
• Zipcode

You now have a Person table that you can populate with rows of data.

A database can have any number of tables like this to store all kinds of data.

What is a Relational Database?

The most common type of database is a relational database. This means that separate tables can have their data linked together by a common column. This is important for the way most computer software works and an important part of understanding how you can better describe your business needs.

In our Person table example, we included an ID column which is likely a number counting up from 1 for each row of data. This is what's known as a Primary Key for the table because no two rows will ever have the same ID. It is a unique way to identify a single record.

Now let's suppose we want to track additional information that is separate but related, for instance orders placed by this person in a shopping cart system. You wouldn't want to keep that type of information in the same table as a Person because this person might have multiple orders, so you would need multiple records in the Order table. You want to be able to connect these two tables though so that you know which Order records belong to which Person.

Back to our Excel example, you can think of a table as a tab in Excel. Each new tab is a new table in our database.

To build our Order table, we are going to add one more special type of column that is going to allow us to tie the orders to a person. The columns might be:

• ID
• PersonID
• OrderTotal
• Tax
• Shipping
• OrderDate

As you can see, an Order has its own Primary key in the ID column, but we also have a column called PersonID. PersonID is known as a Foreign key because it links back to the Primary key of the Person table. Every time a Person places a new order, a new record is created in the Order table and the ID of the Person who  placed the order is inserted into the PersonID column. The Person and Order tables are now considered relational because each Order is related to a Person.

Taking this further you might have an OrderItem table to track the specific items in this order. The OrderItem would have a Foreign Key (OrderID) which relates the Item back to the Order which in turn relates the Item back to a Person. In this way, all of your data can be linked together and tied to a single point if necessary.

How is data retrieved from a database?

Data is retrieved from a database by using something called SQL (structured query language). SQL is pretty easy to understand as its largely based in normal written language except is has some strict rules on what words are used and in what order. Here is an example of an SQL statement to retrieve all of the Items that are part of an Order:

Select * from OrderItem where OrderId = 21 order by ItemName

This statement will return back all of the columns and all of the rows from the OrderItem table which are part of the Order with an ID of 21 and sort them by the ItemName. It's as simple as that. Of course, these statements can get extremely complex and it's much more difficult to write them than to read them.

Conculsion

That covers the basics of what a database is conceptually. If you feel that there is something lacking from this basic description, or if you'd like further clarification on something, let us know in the comments and we'd be happy to do a follow up!