A Primer about Relational Databases

There are two types of databases, flat and relational.

A simple or flat database is much the same as a card file...one card per piece of information...like a phone number rolodex or recipe file...one card holds all the data needed about one item...all the cards about all the items in one box represents one "collection" or one “relation.”

A relational database is substantially more sophisticated...it’s like having several rolodex’s on your desk that borrow and share information with each other...automatically!

(As I was a funeral director and designed a detailed relational database for funeral homes, the following description is couched in funeral home terms
...but you'll get the picture.)

A two-relation database would be like having two interconnecting rolodexes, e.g., one holding a list of, say, all types of services available and another holding the information about each provider of every service. A two-relation list is about all you can do on paper...a yellow page book is a fair representation of a two-relation "database on paper"...it lists all services (doctors, plumbers, lawyers, exterminators, etc.) AND it lists a little information about each provider...all the doctors' names and their addresses and a phone number...and one can easily see how unwieldy it can get. Just think how thick one would have to be to hold each doctor's price list, inventory of instruments, patient list, supplier info, etc. A computer based relational database can do all that rather easily.

A funeral home often has several flat databases, such as its funeral files, probably a list of accounts receivable, its ledger of payments received, perhaps a book from which letters can be copied, a list of its inventory with the jobber for each item and perhaps a phone book. The problem is that each is ignorant of what's in the other.

If you're looking at a funeral file and want to know whether it's been paid, you'd have to get out the accounts payable ledger and check it (unless you've doubled your workload and you enter the payment in both locations).

If you notice an account in the payable journal that you thought had been paid, you'd have to get out the receipts ledger and scan down the page to see if a payment had been made (unless you've doubled your workload and you enter the payment in both locations).

Get the point? A relational database is conversant...one "rolodex" tells all the others...true single entry bookkeeping.

Computer based flat databases were an initial improvement over paper-based flat data bases...once you learned how to use them They can find the information very fast but the information is still not interactive...you might know which casket was used on a specific funeral but you probably wouldn't know the phone number of the jobber of that casket. You might record the name of the doctor, or the nursing home or the registrar of the town of death but you probably wouldn't have their various phone numbers handy, you'd have to look them up in another location or another flat database. An electronic flat database is good but an electronic relational database is much better.

If, in addition to your funeral folder with data about the decedent, you were to have a second relation, say, one that holds data about your inventory of caskets. You could assign a reference number to each casket in this inventory file and access all the data about any one casket by entering merely a matching reference number in the data file for the decedent. That is, in addition to the decedent’s name, date of death, Social Security Number, etc., you enter the reference code for the casket selected...the relational database then looks into its other "rolodex" about that casket and accesses it...all without re-entering anything about that casket. Once(!) entered, all the data about that casket is available to each decedent file that contains a matching reference number...forever. That type of ability goes on and on...retrieving data by matching a reference code...or a name...or a number...or by matching any preset information that is keyed in (such as the name of a hospital).

When the funeral home receives a payment, it is entered ONCE in the Receipts Relation and the database automatically credits that payment to the funeral bill in the Financial Relation and marks the Funeral Data Relation paid in full (once it truly is...it even knows enough to indicate if it's overpaid). If a check bounces and you change the data already entered in the Receipts Relation, it tells the other relations AUTOMATICALLY and the proper information is immediately available! Neat, huh?

FHMac is a relational database with fourteen relations.


Back to the William P. Walton, III Home Page

William P. Walton, III - Mac DATABASES
Wayside, New Jersey and Orient, Maine

E-Mail - WPWaltonNJ <AT> aol.com

2002 & 2003-2007 (WW) - William P. Walton, III - All Rights Reserved