Database Basics Part 2 — Spreadsheets and Databases

Tuesday Jun 29th 2004 by Beth Cohen

In part two of database basics, SMB guru Beth Cohen demonstrates how a database works by using a program most small businesses know intimately — a spreadsheet.

When is a database not a database? When it is a spreadsheet. What do spreadsheets have to do with databases do you ask? Spreadsheets are actually very rudimentary databases and, while not advisable, some people build them with what they have on hand -- which is often Microsoft's Excel spreadsheet. Still, by using spreadsheets as an example, you can learn about databases.

In part one of this series, I introduced general database concepts and how they're handy for managing the information of any small business. Now that you have an abstract understanding of the general concepts, it's time to learn how to understand them in more concrete terms so that you can start using them to solve your business problems. To help you more fully understand how databases are used, I will explain basic database concepts in terms of a program that is widely used by many small businesses — the spreadsheet.

Databases vs. Spreadsheets
There is not anything really mysterious about a database. Chances are, you have already used a database, even if you didn't think of it that way. Think of a spreadsheet "table" as an elementary type of database. In the chart below, you'll find a few lines from an address book — a fairly typical spreadsheet or database application.

As I mentioned in the previous article, the basic components of any database include records, fields and schemas. The top row in the address book above tells the user what each field represents. In database parlance, that is referred to as a schema. Looking at the next several lines of the example, each row represents a database record. Each column represents a field in the record. Although a spreadsheet table like this is an extremely simple kind of database, it does differ in some important aspects from the more sophisticated beasts we usually refer to as databases. For starters, our spreadsheet schema only contains labels for each field. A "real" database schema generally contains a great deal more field information: such as the type of data (e.g., numeric, date or alphabetical), what values are allowed, and how they are to be displayed. The information in the schema really defines what a particular database is and how it will be used, so the creation of the schema is an important part of the database design.

The fields in a database record may be arranged hierarchically. So, for example, an aggregate field representing, say, an inventory item might consist of two fields: the first being an ID number for the item and the second being another field for its location, the latter also being an aggregate field, with sub-fields specifying its room location, exact rack, shelf and bin. There are even mechanical carrel systems that allow users to access the bins by using the computer to bring the correct bin to the front.

First Name Last Name Office Department Title Phone ext.
John Smith New York Development Engineer 123
Jane Doe Pittsburgh Finance Senior Accountant 454
Beth Cohen Boston IT Director 372
Jeff Dulong Pittsburgh Development Principal Engineer 483
Shai Basilli New York IT System Manager 139

A database with one table is usually referred to as a flat file database because it is laid out internally in a linear manner. Like that address book spreadsheet above, each record is in sequential order. For more complex sets of data, a database may consist of many different tables that can be combined on the fly. For example, a table that describes payroll may include a field that contains a personnel ID to whom it is to be paid. That ID, in turn can be used to reference a separate table containing necessary information about that employee that might be more confidential in nature. The payroll clerk does not need to know personal information about the employee to create their paycheck.

Another major difference between "real" database tables and spreadsheet tables is the number of records and the amount of data it can handle at one time. With, at most, a few hundred entries, one typically accesses the data in a spreadsheet by organizing it (perhaps by sorting it) and then by examining it directly, or perhaps by creating a plot of some relevant columns. A spreadsheet is designed to efficiently handle no more than a few hundred rows and perhaps a hundred columns at most. The circuits spreadsheet mentioned above, was beastly slow and unusable by the time it had only a few hundred records in it. One sign that it is time to switch to a database is when it gets increasingly difficult to access and manipulate the data in the spreadsheet program.

A database generally contains too much information to make direct data access practical. Instead one uses a report generator to make a query that causes the database engine to extract, derive and condense only the information of interest, and present information in a humanly readable report. A data report may look like (or even be) a Web page, set of Web pages, or a spreadsheet. The report might be exported to a spreadsheet to allow the user to manipulate the data so that it has more meaning. Database engines (as the applications that handle databases are called) routinely handle thousands, tens of thousands or even hundreds of thousands records and thousands of fields efficiently. SABRE, the widely used airline reservation system has millions of records.

Now that you have learned the basics of how database size considerations, the number of records, complexity, and data sharing requirements, will affect your choice of systems, it is time to cover the data entry, workflow and report generation mechanisms in more detail. Once you have a solid grasp of database basics, we will discuss some of the many useful ways databases can be used to help you run your business more efficiently, by allowing you to organize and track your company information — whatever it may be.

Additional Resources:
Introduction to Relational Databases - Part 1: Theoretical Foundation— A nice overview of databases. An Introduction to Database Systems, Eighth Edition by C.J. Date — An introduction of database theory for the more serious student

Beth Cohen is president of Luth Computer Specialists, Inc., a consulting practice specializing in IT infrastructure for smaller companies. She has been in the trenches supporting company IT infrastructure for over 20 years in different industries including manufacturing, architecture, construction, engineering, software, telecommunications and research. She is available for consulting to help your company identify the right IT infrastructure to meet your business objectives.

Do you have a comment or question about this article or other small business topics in general? Speak out in the Forums. Join the discussion today!
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved