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:
- Product or Service
- Sale Price
- Sale Date
- First Party Involved
- Item Handled or Second Party Involved (sometimes both)
- Transaction Date
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.
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?