Database Basics

In non-technical terms a “database” is a collection of information, organized in a clear way for rapid searching and retrieval. If the real-world equivalent of a word processing document is the typewriter and the real-world equivalent of a spreadsheet is the ledger, the best physical model for most databases is probably a rolodex or card file, where each card represents a record (such as a contact or a recipe) and each card contains information about that record (such as address, ingredients or directions). Mundane real-world “databases” might include such simple things as dictionaries, phone books or encyclopedias. More complex databases includes such things as the LexisNexis database of articles, credit information used by the finance industry and the various databases used by the Social Security Administration and Internal Revenue Service for various “data mining” operations. Complex databases may involved hundreds of different tables and sources, with sophisticated filters and calculations built in. Simpler “flat file” databases can be expressed as a simple two-dimensional table, such as a single spreadsheet with one row per entry and one column per attribute. The simplest kind of database is usually an address list with each person on a separate line, with columns for first name, last name, address, phone number and so on.

Common Databases


Many common programs organize document data in a database and, to a certain extent, are database management systems. Email programs, for example, organize messages to sort and display messages by such fields as sender, date and subject line. Address book programs manage tables of information about contacts as do scheduling programs such as iCal, Entourage or Palm Desktop. Finance programs are essentially database managers for financial information, so if you have ever used email or Quicken, you already know quite a bit about databases.

Database Schema or Tables


At the simplest level databases are organized according to a pattern or “schema” such as a table. The best databases have a different schema for each entity: a different table for each sort of thing they are tracking. An email program, for example, will often have two tables: one to manage email messages and a second to manage email addresses. A table of addresses, or example, will list one address per row, with different columns for different attributes of each address such as first name, last name, email address and telephone number. A table (schema) of records (entities) contains fields ( or attributes) for each record, organized in a consistent way.

Advantages of a Database


Most people will organically grow toward a database, beginning with a word processing document (a basic typed list of addresses), organizing that into columns (a spreadsheet of addresses, which can be sorted by name or ZIP code) and then into a simple database program such as Address Book, Palm Desktop, Microsoft Works, AppleWorks, Access or FileMaker. A database offers many advantages such as
  1. Ability to rapidly sort or search through many records
  2. More consistent entry of many small changes over time
  3. Consistent long-term storage of data “behind the scenes”
  4. Ability to re-use the same information in different formats
  5. Ability to import or export data into different programs as needed
  6. Ability to link data easily to other data, such as customers to invoices
  7. Ability to generate reports and calculations from disparate data

If you routinely generate a list for holiday cards, for example, you can store postal information for everyone in a single place, and use the same file to store related information such as phone numbers, birthdays and anniversaries. If one is a business who is already tracking contacts, phone messages, sales, inventory and expenses, a better understanding of these disparate databases can be leveraged so that such information works together.

Identifying Databases


Almost anything which is worth tracking is more valuable when tracked in a logical, retrievable manner. The advantage of Quicken over a paper checkbook is that it can do arithmetic for you and quickly search through a ledger by date, category or payee. The advantage of MYOB or QuickBooks over Quicken is that it can also sort and report data in more sophisticated ways. Programs such as Entourage and Palm Desktop combine a variety of different tables with varying degrees of success, and programs such as FileMaker or technologies such as MySQL allow one to connect wildly disparate data in ways that exactly match your preferences and business needs. What sorts of things are you tracking now? How are they similar or different? How could these things be more logically related?

Among the sorts of things most people track in some way are:

  • Contacts: names, emails, phone numbers, customers, accounts
  • Tasks: things to do, dates due, project steps, maintenance
  • Appointments; dates, times, locations, notes or minutes
  • Transactions: payments, receipts, invoices, bills
  • Documents: memoes, instructions, directions, minutes, notes
  • Inventory: equipment, supplies, manuals, service, warranties, repairs

Getting Started


Frequently the best way to begin organizing things is in folders on the desktop or hard drive. Sometimes it is on physical index cards. Perhaps it is on Excel spreadsheets. The best method will depend on who you are, what you prefer and what you need to track. Once you have identified your needs, please consider contacting us to discuss what programs and procedures would best meet your needs. Phone Mac Rory at (360) 695-6929.

Links…

This entry was posted in Uncategorized. Bookmark the permalink.