Relational Databases

RTG Bills, our time and billing software for law firms, uses a relational database to store information. Clients, matters, timekeepers, codes, fees, expenses, payments, adjustments, and more are all stored in the database. Perhaps you've heard that a relational database is a good thing, even though you don't know exactly what it is. We will discuss, without mathematical details, what it is and how we use it.

The information in a relational database is stored in tables. If you are familiar with spreadsheets, think of each table as a spreadsheet. A table has rows, also called records, and columns, also called fields. For example, in RTG Bills, the Timekeepers table has a row for each timekeeper, with columns named Number, Name, Initials, and so on.

In this table, the Number column uniquely identifies each row. In other words, you can't have two timekeepers with the same timekeeper number. Every row of a table in a relational database must be unique.

Now consider the table where fees are stored in RTG Bills. The name of the table is TimeTrx (short for time transactions), and it looks something like this:

In this table, an arbitrary "transaction number" (the TrxNum column) has been assigned to each row in order to uniquely identify the row. That's necessary because it is possible for all the other columns to be exactly the same for two rows.

A Database Query

Notice that the Timekeeper column of the TimeTrx table contains numbers, not names or initials. The numbers in the Timekeeper column refer to the numbers in the Number column of the Timekeepers table.

Suppose we want a list of fee transactions that shows the timekeeper initials for each transaction. The database understands a language called Structured Query Language or SQL. Here's how we ask for the results:

SELECT TimeTrx.*, Timekeepers.Initials
FROM TimeTrx, Timekeepers
WHERE TimeTrx.Timekeeper = Timekeepers.Number

The SELECT line tells the database what fields to return: everything from the TimeTrx table but only Initials from the Timekeepers table. The FROM line just lists the two tables to be used. In the WHERE line we finally see the "relational" aspect of the relational database. We tell the database to match up the two tables, using the Timekeeper column of the TimeTrx table and the Number column of the Timekeepers table.

The result of this "query" is another table that looks like this:

Notice that this table has all the fields from the TimeTrx table, plus we've added a column with the timekeeper initials.

What The Database Software Does

The database software stores data in the tables, and it retrieves data in response to queries. It will add or remove rows (records), for example, whenever you add or delete a timekeeper or a fee transaction. It can also add or remove columns (fields), an important capability when a new version of RTG Bills needs to store additional information. For example, when we added the ability to charge taxes, we added a field named TaxCode to the TimeTrx table.

RTG Bills uses queries, like the SELECT statement shown earlier, to retrieve information from the database. Some reports require quite complicated queries to gather information from several tables.

The database software figures out how to retrieve the information that RTG Bills requests. That makes RTG Bills itself simpler and more reliable, because it does not need the programming logic to combine information from different database tables.

Where Is The Relational Database?

The relational database consists of two parts: a file that contains the actual data and software that manipulates the information in that file.

The database file for RTG Bills is named Rtgbills.mdb. There is a similar file for RTG Timer, named Rtgtimer.mdb. When you first install RTG Bills or RTG Timer, an empty database is placed on your hard disk. It contains all the tables and the columns for each table, but the tables have no rows of data.

The database software consists of many pieces, all of which are installed in the Windows system folder. This software is known as the Microsoft Jet database engine.

Item Numbers in RTG Bills

After you enter fees and expenses, you may need to change an entry. The usual way to do that is to click the Fees button, type the matter number, and press Enter. RTG Bills queries the database to get all the unbilled fees and expenses for the matter. The transactions will be displayed, ten at a time, in reverse chronological order. You can look through them to find the one you wish to change.

Sometimes, however, you may want to go directly to a particular transaction. If you find an error on a report that shows the item number of the transaction, such as the Fees and Expenses by Timekeeper report, it would be convenient to go directly to that transaction, using its item number.

You can do that in Version 2.04 of RTG Bills. Notice the Item button in the partial screen image shown here:

If you click the Item button, type in the item number, and press Enter, you'll be able to edit the transaction without having to find it in a list.

What we are calling the item number here is the field TrxNum, described earlier - a number that uniquely identifies each row of the TimeTrx table.

Improved Backup Commmand

When RTG Bills and RTG Timer Version 2.04 is released, it will include an enhanced backup capability. In the current version, the backup will fail if the destination disk fills up. You cannot continue the backup onto another disk of a removable drive.

A standard floppy disk holds only 1.44 megabytes. The RTG Bills database will eventually exceed this size. On the other hand, if you have a Zip drive, with a capacity of 100 megabytes or even 250 megabytes, most likely you will never fill up one disk.

The backup feature in the new version has two major improvements. First, you can continue a backup onto another removable disk. Second, the data is compressed, so it takes up less space. Together these changes make it feasible to use floppy disks for backup. However, that doesn't change the fact that Zip disks, CD-R disks, and CD-RW disks are all faster and more reliable than floppy disks.

Another new feature is a program called RTG Restore for restoring data from a backup. Previously, you had to use Windows commands to copy the files from the backup medium into the RTG Bills (or RTG Timer) folder. Now RTG Restore will lead you through the process of restoring your data.

We have always been concerned that making it too easy to restore data from a backup would have negative consequences: an inexperienced or careless person can "restore" obsolete data and overwrite good data. We have tried to address this problem in several ways. RTG Restore contains warnings about what you are about to do, including showing you when the backup was created, so you will know that you are "going back in time" to that date.

In addition, RTG Restore must be run by double-clicking its icon in Windows. It is not on the RTG Bills menu, and we do not add it to the Windows Start menu during installation. Finally, if you are concerned that someone will accidentally restore data, you can remove RTG Restore from the hard disk or place it in a location that other people cannot access.

RTG Bills and RTG Timer are trademarks of RTG Data Systems. Other company and product names may be trademarks of the companies with which they are associated.

Back to the RTG News page

Copyright © 2001 RTG Data Systems