Hide full article
When you're setting up a database for the first time, do you know exactly what you'll want out of it, for the rest of the life of the database? Heck, do you know what you're going want from it next month? You probably have a general idea, and if you've created databases in the past or are working with a consultant, you'll probably spend some time mapping out your data structures, reports, and more. That work is usually highly worthwhile, since it can be difficult to rework overall architecture in many database programs.
Until 2003, I hadn't created a custom database by myself in years. Geoff Duncan set up all the databases related to TidBITS, and all the rest of my database tasks were ably performed by dedicated programs such as Now Contact (for our shared address book) and Eudora (for archived email) that did an excellent job at what they were designed for, but didn't venture past those confines. But at the end of 2003, we started Take Control, and I knew we were going to need a database to track orders and generate royalty statements for our authors. It's conceivable that there is a dedicated royalty database program available, but I couldn't imagine it would be able to handle the raw data we received from eSellerate, and I didn't want to pay a consultant to create something until I knew exactly what I wanted the database to do.
Deciding on the View -- After some thought, I decided to create my royalty database in ProVUE Development's Panorama, a powerful and quirky database program that was one of the first Macintosh databases ever. I had used Panorama last back in 1992, so I'd forgotten anything I once knew about programming it. What I did remember was that Panorama's RAM-based architecture made it lightning fast when you were performing queries, something that's often not true of other databases unless you've indexed the fields upon which you're querying and you're performing a relatively basic query (Panorama queries can search for text in the middle of words, search for text phonetically, search for fields compared to other fields, and search using an arbitrary formula). Also key in my decision was Panorama's Data Sheet, a spreadsheet-like view of your data that matches the way many (if not most) people who are not database professionals like to visualize their data. That tabular visualization is the reason so many simple databases are created in Microsoft Excel, even though Excel isn't particularly accomplished at database work.
<http://www.provue.com/>
The reasons these two facts - fast queries on any field and the Data Sheet's tabular view - were important to me is that I knew going into my project that I didn't have a clue what the database would look like in a year. And not only could I not predict what I was going to want, I didn't have time either to guess at what might be important or to spend a lot of time creating a database that performed tasks I didn't need. I knew I needed to calculate monthly royalty statements for a couple of books based on the data in tab-delimited text files that I received daily from eSellerate. At that point, though, I could barely imagine that some authors would be writing multiple books, that we'd also be paying editors and translators (some of whom could also be authors), that we might end up with multiple authors sharing royalties on a book, and so on. Plus, when I started writing the database, I knew relatively little about my incoming data. I couldn't predict that we'd want coupons for individual books, for bundles of books, and for entire orders, and until some appropriate orders came through, I had no idea how affiliate adjustments had to be made. In short, I was flying low, fast, and blind, and I needed a highly maneuverable airplane to skim through my data. In a word: Panorama.
Zooming In -- I'm not going to attempt to tell you everything about how Panorama works; it's both powerful and deep, and I'm sure I've merely scratched the surface of its capabilities. What I will tell you, however, is a bit about some of the features that enabled me to understand my incoming data and extract the results I needed. Each month I have enhanced the database a bit more as I learned more about what I wanted.
As I noted earlier, Panorama's spreadsheet-like Data Sheet is a wondrous thing. You don't have to create any layouts to enter or view data in Panorama, for the simple reason that in the Data Sheet, you can see and change every field of every record. Of course, you can create layouts that help you focus on specific fields or that are used in generating printed reports, and I did create them eventually. But even still, I spend most of my time in the Data Sheet, since it's such an excellent tabular overview of my data. So, every month I would use the Unix cat program to lump together all the daily reports I received from eSellerate in email, then I'd strip out the column header records on each using BBEdit, and then I'd import the resulting text file into Panorama. Seems clumsy, doesn't it? I mentioned this while chatting with Jim Rea, Panorama's creator, and he sent me the bones of a procedure - which is what Panorama calls the little programs you write to automate tasks beyond what's possible from the interface - that looked in the appropriate folder and imported all the files in it, automatically stripping the column header records. Life was already a little easier (and there's also a mailing list for Panorama users to share similar pointers).
<http://www.provue.com/support.html>
The Data Sheet not only looks like a spreadsheet, it works like one as well. For those first few months, before we had coupons or affiliates to complicate matters, I relied on the commands in Panorama's interface to generate raw numbers that Tonya then further massaged in Excel to generate an actual royalty statement. Within the Data Sheet, you can sort on any column (columns are fields, rows are records), show just the records that match single or multi-field queries, and, most interestingly, group records by field. Grouping is tremendously helpful because it lets you collect records into what Panorama calls "summary records." Summary records are real records that can contain the same types of data as normal records, but they're temporary; you can use them to hold the results of mathematical equations as subtotals, and when you're done with a particular grouping, remove them. Summaries are essentially a hierarchical outline, with an outline level for each grouped field, and you can view any level of the outline independently. This feature proves to be useful because it enables you to - for any ad hoc query - group and subtotal the records found by the query, view the subtotals, and then expand either the entire set of found records or any individual group to see how you arrived at particular subtotals.
An example will make this more clear. We now have a lot of books to track, and every now and then when I'm running royalties, I notice that my database and eSellerate's payment report disagree about how many copies of each were sold in a month. So I do a search to limit the displayed records to just books sold in the appropriate month. Then I group the database on the Title field to make a summary record for each book. Then, while in the Quantity field, I use the Total command in the Math menu to count the number of each book sold in that month. Lastly, I change the outline level so only my subtotals and the grand total (created automatically when I used Total) are showing. With these steps, I've gone from a list of several thousand records to about 20, and I can easily compare my numbers with eSellerate's. When I find a subtotal discrepancy, I expand the summary record for that book, and glance at the raw data, which usually reveals the reason right away. This is a simple, though real example, and I use this basic technique often to understand my data and the result of any calculations I'm performing on it.
Automation -- Of course, just because you can perform all these commands directly from Panorama's interface doesn't mean you should. Panorama has a full-fledged programming language, complete with local and global variables, looping commands, and so on. Any time you find yourself repeating the same actions over and over again, it's time for a procedure. My first procedures were quite simple; they just selected data, grouped it, and performed calculations on the records in the group.
The most important procedure I wrote calculates the amount of money different people earn for each sale of each individual book, storing those numbers in new fields I've created. In concept it's simple: multiply the unit price by the quantity, subtract the transaction fee, calculate any coupon discount and subtract that and any affiliate charge, and then divide the resulting subtotal between the author, editor, publisher, and any translators. In reality it's simple too, or it was until we ended up with oodles of specific coupon codes, some of which apply only to specific books (but which still appear in the records for other books purchased in the same order), others of which apply to the entire order, and all of which can have different percentage adjustments or fixed discounts.
All of the complicated "if a record has this coupon, perform this calculation" code for coupons has caused my earnings calculation procedure to get ugly, and adding new coupons to it each month has become a painstaking and error-prone process; that in turn was the hint I needed to move to the next level in Panorama, using its relational capabilities.
Linking Databases -- In the beginning, Panorama was a flat-file database, but at some point in its long evolution, ProVUE added the capability to link databases by lookups, statements in a procedure that go into another database, find appropriate records, and extract the relevant data from those records. Once you have the data, you can do with it whatever you want, such as using it in calculations, inserting it into the database and so on.
My first effort at linking databases came when I wanted a way of connecting book titles to authors, since eSellerate didn't know the author name to include it anywhere in our raw data. I could have written a procedure that found all instances of "Take Control of Upgrading to Panther" and filled in an Author field with "Joe Kissell", but that would have required constant modification of the procedure to account for new titles. The right way to do this is to create a Books database that contains fields for Title, Author, Editor, and Translator, and then, whenever I needed to know the author (or editor, or translator) of a book, to look that up in the Books database. Adding data for new books to the Books database is much easier and less error-prone than modifying a procedure each month.
That's especially true of the enhancement I'm working on this month, which is a new Coupons database that tracks all the different coupon codes, the percentage or amount of discounts they embody, and information about when and why they were generated. I haven't quite finished the code yet, but the idea is that for any record in my Orders database that has a coupon, I can look up the coupon in the Coupons database, determine if a discount should be applied, and do the math based on the information reported back for that coupon. Since we've used about 50 coupons so far, this is proving already to be a more coherent and accurate approach. One use for the Coupons database has already arisen - a minute's work with Panorama's Text Export Wizard and I was able to export an HTML table of the coupon codes and descriptions to share with our authors on our internal wiki. The Text Export Wizard is only one of many useful canned utilities that ProVUE has written and bundled with Panorama.
Yet another advantage of breaking the system down into multiple databases is that it leaves room for growth. At the moment, the percentages we use to calculate author, editor, and translator royalties are the same for everyone. But I could imagine a situation where they weren't the same, and if that comes up, it should be relatively easy to add fields to the Books database that specify what percentage each contributor to the book should earn. Luckily, with Panorama, there's no reason to cross that bridge until I come to it.
Layouts and Reports -- Unlike many databases, there is almost no manual data entry in our system, and what there is (mostly direct sales from organizational purchase orders and the like) could be done directly in the Data Sheet in our separate Special Sales database. However, when we published the "Take Control of Panther" print book with Peachpit Press, we created a situation where we would not only have to enter the data from the statements they sent us, but we'd have to break apart the entered record to be able to share the proceeds appropriately with each of the authors whose book was included in the print collection. For that, I created a layout - basically a form containing fields and labels - into which the data could be entered. Then I wrote the procedure that split up the entered data so it matched the format of other special sales and attached that procedure to a clickable button. Now, when it comes time to enter any bundles, such as our print books, I can type in the numbers, select the appropriate bundle from a menu, and click the Split Bundle button to break it apart. In essence, I'm using the layout both as an interface with which to enter data and as way of transforming the entered data into the form I need. Layouts can thus protect the data from me (preventing me from modifying bits I shouldn't accidentally) while at the same time protecting me from the data (by hiding irrelevant fields). And if I'm ever concerned about something I've done, I can easily check in the Data Sheet to make sure all the underlying data is correct.
The most obvious use for layouts, of course, is in generating printed reports, which are one of the more powerful and tricky parts of Panorama, because they rely heavily on the summary records created by grouping (since that's how you get subtotals, and you usually want to include subtotals in your reports). Panorama handles all this with the concept of report "tiles," onto which you put boxes that display information from specific fields. Tiles are associated with different summary levels (since you probably want a variety of subtotals and totals on many reports), and there are a variety of options for how they float on the page. Panorama also offers a full graphic editing environment for adding boxes and lines and text and images to your report. It feels like MacDraw of yesteryear, and my main irritation is that getting a report to look just right is often quite time-consuming, especially since you can't necessarily predict the length of any given field. Nevertheless, using it I was able to create reports showing both historical sales over time for each book and monthly royalties for each author, editor, and translator.
Interface and Usage Nits -- I won't pretend that Panorama is perfect by any means. Its interface, having evolved across many years and many incarnations of the Mac OS, is quirky, bordering on weird in places. For instance, you can't close Panorama's Preview window (for previewing reports) with Command-W, and moving to the next page (you can't move the previous page) requires clicking a tiny page button in the upper left corner; a set of modified scroll arrows would be more obvious. Opening a procedure or layout in a new window (rather than taking over the frontmost window) requires holding down a modifier key. Little things trip me up too, like trying to close dialogs by pressing the Escape key; it works in system-level dialogs like Print and Save As, but not in any of Panorama's native dialogs, and worse, if a Panorama dialog contains a text field, pressing Escape types a character in that field. And as cool as the Data Sheet is, you have to be a little careful in it, since it's easy to add a new record accidentally by pressing Return or by scrolling past the last visible record. Luckily, Panorama prompts for confirmation if you accidentally try to delete a record by pressing the Delete key when you mistakenly thought you were editing the contents of a field.
The Grand Total -- Usage nits aside, the way Panorama allows you to take advantage of its features over time, as you discover what you need and learn more about how to use Panorama itself, proves to be the most unusual and attractive aspect of the program. I'm no database expert, but other databases I've used over the years haven't been nearly as flexible or forgiving if you decide that you want to change the way the database works in some fairly radical fashion. If you are looking for a database that can grow with you, Panorama is worth taking for a test drive. The free evaluation version is fully functional, but once your database has more than 250 records in it, you'll be prompted to play a little game every time you print or save.
<http://www.provue.com/panoramatestdrive.html>
Panorama V runs natively in both Mac OS 9 and Mac OS X, includes over 3,000 PDF pages of documentation, and costs $300 for a full development version (you can also get runtime-only versions for $130).
