Been There, Done That, Sent it to Production

I admit I ripped off the last part of that title. I heard it at the Access 14 Developers' Kitchen in Redmond a couple of weeks ago. One of the presenters had the all-too familiar experience of having a bug pop up in the middle of his demo. It turned out to be one of those trivial things that we all do from time to time and it was quickly fixed. While he was tweaking his code, several audience members called out variations of the familiar "Been There, Done That". Then after an appropriate pause, Doug Yudovich added his version, "Been There, Done That, Sent It to Production." In a moment of universal recognition, everyone in the room sighed and nodded in agreement.

Shamelessly, I appropriated the phrase for my own use. It's funny and it's quite pertinent to most of what we do as Access developers, isn't it? Of course, getting the inside joke requires that we all know what it means to admit we've sent our bugs to production.

Later it occurred to me to wonder if I really had sent that exact same bug to production at some point, and I just can't be sure. The problem with knowing that, of course, is that I have not recorded every bug I've ever left in a piece of code. It's certainly within the realm of possibility for me to do that. We often create bug tracking applications for specific projects. The Access Team had one in operation at the Dev Kitchen, and most of us entered at least one bug in it. But one database to track every bug I've ever experienced? I'm not THAT much of a masochist.
I'd Like that in Writing, Please
However, if I ever did decide to create such a bug history database, it would have to contain at least a HISTORY table, and probably a TRANSACTION table, and they are the tables I want to talk about today.

In a previous blog entry I talked about the more familiar kind of transaction in which money changes hands--financial transactions. There is a class of transactions, though, which don't involve money, although they do share most other characteristics of transactions.
First, let's review a list of the elements of a financial transaction:

• Vendor
• Purchaser
• Product or Service
• Quantity
• Sale Price
• Sale Date

Their corollaries in a non-financial transaction include, at a minimum:

• First Party Involved
• Item Handled or Second Party Involved (sometimes both)
• Transaction Date

It is the interaction between two entities and a date on which that interaction occurred that make this a transaction. Remember back, if you will, to the initial blog entries where I compared the elements of a database to the elements of human language, nouns verbs and adjectives. Describing transactions requires two nouns and a verb to identify the interaction between them. Note that it is the relationships between the tables that list "First Parties" and "Items Handled" which define that verb. Or perhaps it would be more accurate, logically, to say that it is the verb describing their interaction which defines the relationship.
The date is an adjective, or attribute, which describes the transaction by pinning it to a specific date (and possibly time). The verb, of course, varies with the type of transaction, but it is that element which identifies it as a non-financial transaction.
Here are some examples of non-financial transactions and the elements needed to record them.

• Requesting a catalog
• Subscribing to a newsletter
• Signing up for a free seminar or workshop.

To get a catalog, the requester makes contact with the organization and provides specific information, and the organization then records the transaction so that the catalog can be sent at appropriate intevervals. The verb is "request". A requestor requests a catalog.
This particular transaction table, therefore, will need at least:
TransactionID--the Primary key for this table
RequestorID--Foreign key to identify the requestor in the Requestor table
CatalogRequestedID--Foreign key to identify the specific catalog version requested
RequestDate--Date the Request transaction was recorded
The same set of foreign keys, with appropriate, but different, names will be required for similar transactions involving newsletters or workshops.
And, of course, it is quite likely that additional attributes may need to be defined and tracked for some transactions.
The History of the World: Part I
It seems a small distinction, but to my way of thinking, there is a conceptual difference between non-financial transaction tables like these and pure history tables, which merely record events and the dates on which they occur. I'm not sure it's always possible to identify them as different solely on the basis of their architecture. Nonetheless, here's an example where a structural difference can be identified.
Let's say we have a newletter mailing operation, for which the transaction table records the date on which the requestor first signed up to receive it. There will quite likely be a second table, a history table, in which each instance of a mailing is recorded.
tblNewsLetterRequest--The transaction table
NewsLetterRequestID -- Primary key
RequestorID -- Foreign key to the requestor table
NewsletterID -- Foreign key to the newsletter table
RequestDate -- Date (and possibly time) of the request
tblNewsLetterMailing--The accompanying history table
NewsLetterMailingID -- Primary Key
NewsLetterRequestID -- Foreign Key to the Newsletter Request table
MailingDate -- Date (and possibly time) of the mailing
In this example, the foreign key NewsLetterRequestID is sufficient to identify both the requestor and the requested newsletter, because they are stored in the related tranasction table and available to the history via that relationship on the primary and foreign keys.
A request (the original transaction) generates a mailing. Then it generates another mailing, then another and another and another (the history of mailings), as long as the requester does nothing to countermand the original transaction that set the flood in motion.
It is possible, therefore, to differentiate this specific history table from this specific transaction table on the basis of the presence, or absence, in the table of two foreign keys which taken together identify the parties to the transaction.
It's a Two-Fer
In this transaction table, both foreign keys are required; in this history table, only one is present. This difference may prove to be a reliable rule of thumb for many database applications, but I'm not fully commited to that principle as yet. It bears further consideration and review of some relevant examples. Anyone willing share some?