Commodities and IdentifiersWhere's the filet mignon?by Joe Celko In my last column, I promised to discuss commodity identifiers: codes used to identify items that are handled in the aggregate, as a commodity. The English language distinguishes between things that can be counted and things that are aggregated. Books, being countable items, therefore need an integer column on a table (for quantity). Aggregated items, such as water or sand, are measured by mass, volume, or some other unit. Three situations are possible when you track a commodity in a database: Your database can agree with your actual numbers, the database can underreport quantities, or the database can overreport quantities. When the database and actual aggregate numbers match exactly, everyone's happy, albeit slightly suspicious. It's too good to be true. Distinct items can be counted accurately, sure enough, but I expect some errors and loss with an aggregated item. For example, liquids evaporate or scales aren't calibrated. The goal is to keep your error percent within limits and worry about only large shrinkages. Ne Quid NimisI knew a magazine publisher who used to "reverse shoplift." He carried a small stack of magazines under his coat to a newsstand that didn't carry his publication. When the owner wasn't looking, he tossed the pile on a rack. When a customer bought a copy, the point-of-sale (POS) system scanned the barcode, the UPC entered the system, and the title would eventually be reordered. It gave the newsstand return credits for the unsold copies and got the UPC code into the wholesaler's database. Quantum SufficitAnother story involves a grocery store chain that tracked the following by weight: bulk meat coming in the back of the stores to the butcher; cut and shrink-wrapped meat sold at the cash registers; and trimmings, which were weighed and sold wholesale. The weight sold in shrink-wrap was computed by the price of each package and the bar code that identified the cut of meat. Managers saw a slight loss of weight between the back and front doors. But one store sold much more meat than it brought in, and it also had a very high employee turnover rate! No, the butcher didn't chop up and sell the staff. An inexperienced employee had inadvertently set all the cut-types in the menu to the first package's barcode - hamburger. The various names of the cuts still appeared on the scale display with the right price and weight. The butchers never noticed the identical barcodes. But only the words "meat: beef" appeared on the checkout display, and the scanner still gave the right price. The clerks never noticed anything wrong: Who reads barcodes when you're scanning groceries? The POS captured the barcode and the weight for reordering purposes. When the total volume of meat was calculated, headquarters used a database with the correct price for the meats to compute the total weight. This meant, for example, a $20 truffle-stuffed filet mignon was computed as $20 of hamburger at $1.00 per pound, so the number recorded was 20 pounds of cheap meat instead of 1 pound of filet mignon. Imagine what plans the company made with its delivery trucks and cooler storage space to accommodate the "surplus!" Aurea MediocritasThe underlying problem: the butcher shop scales allowed multiple values in what should have been keys in the system. Each meat category entry had only a unique position and description in the display list, but lacked a true key. It was only a matter of time before something went wrong. Think about this when you see a table that has only an auto-increment column for a key. Joe Celko [celko@northface.edu] is vice president of RDBMS at North Face Learning in Salt Lake City and author of five books on SQL.
|
 |
Most Popular This Week
IE Weekly Newsletter
Subscribe to the newsletter
| |
|
|