scanning foreign key relationships in the opposite direction
Monday, January 23 2006
The major advances to my web-based SQL front end came quickly today. Using the foreign key relationship infrastructure added to txtSQL yesterday, I wrote a function that scanned those relationships in the opposite direction. Say, for example, you have a table called car containing rows of data for different car models and makes. In addition, you have a table called part containing rows of data for different car parts. Now imagine that each of the parts has a foreign key relationship with car so that there is a many-to-one association between part and some make of car. That foreign key relationship is stored in the table part, not in car, so when you were editing a specific make of car with my tool, it didn't know what parts were made for that car. It just knew about the car and maybe whatever tables it had foreign key references to (say, country_of_manufacture).
But today's scanning function changed all of that. With a given table, it looked at the field definitions of all the other tables in the database and came up with a grouped list of every other table with a foreign key relationship to car. If, in any of these groups, it didn't find any matches for a particular car in that particular table, it created a link allowing you to add one. If it did find matches, it listed them with links allowing you to edit or delete them.
I actually took this system a step beyond what I've described, making it so that in the case of a foreign key back-relationship with a mapping table (one having only foreign keys and a primary key as fields), the mapping table itself is skipped over and the scan continues out to a real data table, a table with actual leaf data in it.
I just re-read my description and it all comes off as frightfully complicated. But it wasn't all that difficult to get it working. The key here was how utterly generic it was; any table in the database which has things associated with it automatically gets a complete set of tools allowing you to easily see, add, delete, and edit those things. What the tool is doing is automating, on the fly, the steps I myself would take if given the task of creating a robust editor for a pattern of stored data in a relational database. Owing to the obsessiveness of its scripts, my editor has attained a completeness and robustness that would normally require months of dedicated testing and honing. It's the happy culmination of a direction I've been heading with content management since I began building tools in late 1998.
For linking purposes this article's URL is:feedback
previous | next