|
|
|
pseudo-column
Monday, November 16 2009
When I'm not working on the ultimate crapatorium, I occasionally find myself solving people's database problems. Today, for example, I spent some time marveling at dozens of junk records being inserted into a table by some errant code that it seems I must have written. I wanted to delete all the extra copies of the one good record, but which one was that? Which of these records had primary keys serving as a foreign keys in other tables? This question is normally unanswerable except by a targeted series of queries tailored to a specific database. But I knew that in my databases (and all databases I work with are "my" databases, in that I view them through my tools), all the relations between tables are known and can be explored automatically. So this afternoon I wrote some code to count up the number of records in other tables for which a particular item in a table is referenced by a foreign key. This number is displayed as a "pseudo-column" along with the real columns of the table. For those who don't understand database terminology, an example is in order.
Let's say I have a database for my beer exporting business. In my database, I have a table called "country" that is referenced by my "beer_manufacturer" table and my "customer" table (because both beer manufacturers and customers reside in specific countries). Now imagine me, as the company web guy, looking at the country table and seeing columns such as "country_name" and "international_dialing_code" as well as an additional tool-generated column (a pseudo-column) showing a count of the number of other records in the database pointing back to this one. For the country "Germany," for example, this pseudo-column would contain a number equal to the sum of German beer manufacturers and German beer customers. If I was looking down my "country" table and saw two "Germany" records and only one of them was referenced by other tables in the database, I'd know the unreferenced one was a junk record and I could delete it.
The problem with calculating this information is that it is extremely expensive. It requires scanning every related table for every data record displayed (and my tool typically displays 50 records at a time). If I wanted to be able to sort a huge table based on this pseudo-column, the machinations would be enormous. So I decided to only calculate this number when my tool was placed in a special mode, and then to only do it for records being displayed. This meant that I could handle the "sorting" for this pseudo-column using my nifty front-end (Javascript) table reshuffler. In paginated tables, it wouldn't provide an accurate sort, but it would be good enough for tracking down the sorts of junk records I'd built it to discover.
Gretchen taught her last class of the semester down at Eastern Correctional Facility, so we celebrated by having dinner at our new favorite Indian restaurant, the one at the corner of Wall and John Streets in Uptown Kingston. Early in the meal, Gretchen asked the owner of the place, a small, dark, and very friendly man, if perhaps he was actually from Pakistan. "No, no, Bangladesh! It is near India!" Gretchen told him she'd figured maybe he wasn't really Indian because the menu included beef dishes. "You are very smart," the owner admitted. As always, he shook our hands as we were heading out the door.
While we were Uptown, we stopped in at Herzog's just before they closed so I could buy a pair of hinges for the brownhouse. I needed them for the "upper sink," which will have to fold out of the way on occasion to give me access to the "lower sink." I'd already made the upper sink out of a huge stainless steel lid from an industrial coffee urn that I'd salvaged from a Bearsville dumpster back in 2006.
Back at the house, I spent some time chiseling countersinks into the upper sink's wooden frame to hold the wings of the hinges I'd just bought.
For linking purposes this article's URL is:
http://asecular.com/blog.php?091116 feedback
previous | next |
