|
[ Go to March 1997 Table of Contents ]
Going Once, Going Twice ... A charity auction taught me a thing or two about database programming.
Sometimes my livelihood isn't too lively. When people discover my programming credentials, they often ask me to develop an application on a pro bono basis. When it's for a good cause I have a personal interest in-such as my synagogue, my wife's church or my kids' schools-well, how could I possibly refuse? Heck, I even learn a few things doing these projects by experimenting with development tools that are new to me. Among my latest projects was designing a relational database application for a charity auction at my oldest daughter's high school. My first inclination was to use TopSpeed's Clarion (see Reviews, September 1996) for this assignment. It turned out to be a bad choice, but more on that later. Before I began programming the auction application, I drew up a database schema, using an entity relationship (E-R) model. For the auction schema, I came up with five tables (see sidebar "Go with the Flow") This schema is straightforward except for the many-to-many (n:m) relationship between items and packages, and the necessary inventory constraint: A package can have many items, and an item can appear only in as many packages as the quantity of items will support. Unfortunately, relational databases cannot represent n:m relationships directly; in addition, they require unique record identifiers to establish even simple one-to-many (1:n) relationships. To implement this schema in Clarion, I added an ID field to all five tables, and in each case defined the field to be a unique key that would be generated automatically. In relational databases, n:m relationships are usually implemented with an intermediate or intersection table containing foreign keys for the owner tables. In the auction database I added a sixth Contains table with two data entries, the ID for the Package table and the ID for the Item table. When I implemented addresses and phone numbers, I expanded them into multiple fields with appropriate formats. That was enough to get Clarion to generate an application. While promising, I was disappointed when I realized how much work was yet to be done and how obtrusive some record ID fields were in the entry forms. Clearly, I needed to change course. I'd seen a demonstration of Wall Data's Salsa for the Desktop (see Reviews, May 1996) and knew it could handle this project better. That got me thinking: Why not use Salsa instead of Clarion for this project? Salsa uses Semantic Object Modeling (SOM, not to be confused with IBM's System Object Model standard). I'll give you some basic background about SOM, but for more information, see chapter 4 of David Kroenke's Database Processing, Fundamentals, Design, and Implementations (Prentice-Hall, 1995) Semantic objects have collections of attributes. Simple attributes have a single value and correspond to relational database fields or data items. Group attributes are composites of other attributes. For instance, in the Donor object of the Salsa auction model (see sidebar "Programming with Flavor"), the Company Name is a simple attribute and the Address is a group attribute. Within the Address attribute are simple attributes for Street, City, State and Zip. Grouping these attributes in the model helps the design tool (in this case, Salsa) generate more meaningful forms and reports, and speeds the design process. For instance, you need not reinvent the field definitions for an address every time you need an address. Forging relationships The third kind of attribute in SOM is a semantic object attribute, which establishes a relationship between one object and another. In the Auction model, the Solicitor object contains a Donor object, and vice versa. You'll notice in the model that each attribute has two numbers below and to its right: These represent the minimum and maximum cardinality of the attribute. The Solicitor object has one and only one Person Name-so Person Name is a required attribute. The Solicitor object can have 0-N Donor objects, reflecting the real-world situation in which a solicitor starts out with no donations, and finishes a campaign with many donations from many donors. If you compare my SOM Auction model with my E-R model, you'll notice at least one major difference: The SOM Item object has no Quantity attribute and can have 0-1 Package objects, while the E-R Item entity has a Quantity field and an n:m relationship with the Package entity. That was a change of design, motivated by three considerations. First, implementing an inventory constraint looked like it might be painful. Also, Salsa has a keystroke defined at runtime for duplicating a record. And finally, people running this auction don't expect many multiple items-they're looking more for unique, upscale items that they'll group into packages that make sense, like a pair of skis, a pair of boots, a pair of poles, a lift ticket and a week's time-share at a ski lodge. If someone donates 100 T-shirts, I may have to change the model. You'll also notice that only the Bidder and Package objects have ID fields. Salsa automatically generates hidden unique identifiers for all objects that don't already have them. (This makes the underlying relational database logic work correctly.) I created explicit ID fields to the Bidder and Package objects to reflect the real-world use of numbers and simplify the bidding process. For instance, we might display and auction the Skis/Ski Week package as number 101, and it might sell to the bidder holding paddle number 42. Stars to the left of attributes indicate that they're used as object identifiers, which correspond to relational keys. A single star, such as the one beside the name attributes in all the objects, indicates a nonunique identifier. A double star indicates a unique identifier. The Bidder ID and Package ID attributes are not only unique identifiers-I've also made them counters, so that the database application will automatically generate the next available ID when new records are created for these object types. The starter application created from this design was almost usable. Unfortunately, the data-entry forms tended to be too vertical, so I spent some time rearranging components to make the forms fit on a single screen without scrolling. I also had to write some formulas to define the Amount Due attribute of the Bidder object as the sum of the Current Bid attribute of all the Package objects related to the Bidder object. It wasn't a big deal-the entire formula to do this in Salsa Basic is this=Sum([Package].[Current Bid]). After reading the online manual for about 5 minutes, I discovered Salsa Basic expects an object or attribute name to be in square brackets; everything else was obvious from working in Salsa, once I understood SOM. >I also had to add a constraint. To be valid, the current bid on a package must be at least as large as its minimum bid. Again, it was trivial-attributes in Salsa can have a validation rule. In this case, the rule is Result=([Current Bid]=[Minimum Bid]). The variable Result in the validation rule is a True/False (Boolean) type; a Result of False keeps the current record from being saved and causes Salsa to display a message box that indicates an invalid bid. The price ain't right At this point, the Auction database is ready for testing, but it's not done. I can almost guarantee that I'll have to redesign at least one form, and probably all the reports, in response to feedback from the volunteers using the system. It's also more than likely that I'll have to revise the schema because the committee forgot to tell me about one or two essential requirements for the database. If I were developing this application for a paying customer, I could classify some of these late changes as beyond the scope of the original bid, and get paid for them separately. That would at least take some of the sting out of being told my "great" database doesn't cut the mustard in practice. But when I'm working pro bono, "customers" usually don't feel any budgetary constraints, and I have to work even harder-although they're usually grateful for my efforts. Senior Contributing Editor Martin Heller writes about and does Windows programming from Andover, Mass. Contact Martin at his Web page at http://www.winmag.com/people/mheller, via e-mail at mheller@cmp.com or at the e-mail addresses here. Copyright (c) 1997 CMP Media Inc.
|
