|
|
|
wild tables
Tuesday, April 18 2006
I managed to stumble into a solution to my generic multi-table database query issue, the one which goes like so:
Suppose you have two tables, a "parent table" called calendar and a "child table" called calendar_event. Imagine that calendar_event has a field that could hold a foreign key pointing to any primary key of any table in the database, but that the specific name of the table was being held by a field in calendar, and varied depending on what row of calendar that calendar_event belonged to. What I'm describing is a complex relation between four fields in two tables that relates to any other table (a "wild table") in the database. It's a nonstandard relational database arrangement, but it applies perfectly to an arbitrary calendar creation tool, one where a "calendar_event" is just a husk for some other row of data from some other table. It might be a "holiday" or a "radio_show" or even (in one goofy demo implementation) a "doobie-break."
My solution was to add a field to my existing "relation" table, the one where my generic SQL editor keeps track of foreign key relationships. The new field is called relationship_type_id and allows me to store relations between tables in addition to those of the simple foreign key kind. For lack of a better name, I call my latest form of relation "multi-table." It requires only four fields to define, though it also requires an assumption that the field being referenced in the "wild" table is its primary key (since doing anything else requires a second field in the "parent" table).
Since I'm sure I've completely lost everyone at this point, here is a chart that depicts what I mean.
Representing the behavior of these relationships on the front end was as big of a challenge as representing them in the database. At first I had a plan to recursively spit out nested HTML forms, one inside the other, allowing the altering of linked rows across tables. Since HTML doesn't support nested forms, I'd have to keep track all the form data through a system of prefixes attached to input names. It took me no time at all to build support for such a scheme in my tool, but in the end I decided to go for a system of popup sub-editors that refresh the windows that launched them as they are submitted.
Every detail of this system was written so as to keep it as non-specific as possible, meaning it can edit any sort of database relationship having this characteristic. After I had it all up and working, I took it for a spin and was delighted by how intuitive and nuanced it was. This may sound kind of strange, but the tool was actually doing things that went beyond my expectations. It was as if the thousands of lines of code, all written to handle the most general of situations, had developed emergent intelligence.
For linking purposes this article's URL is:
http://asecular.com/blog.php?060418 feedback
previous | next |
