Dynamic-SQL Act II

In my last post, I set the stage for a miniseries of posts regarding dynamic SQL construction. This is a problem many software vendors face: Allowing the user direct-access to the database is a huge red flag, and one many engineers strain to avoid. Should there be no alternative, however, great care should be allotted in making the users’ experience as friendly as possible, (while, of course, keeping the user safe from dismantling their own data).

Typically for relational database schemas, (RDb’s), this results in a tree-generating interface which abstracts the actual syntax away from the user. In theory, it’s an excellent way to afford the power user the ability to generate near-infinite custom reporting tools and also help bug-fixing by relying on a few, axiomatic functions.

This doesn’t come without cost, however. Since a relational database is, by definition, segregated into distinct entities, it can be difficult for a program to discern which relationship between two entities to choose. This decision can be critical and time-sensitive for a user. As mentioned in the previous post, if a Person can choose a Profession to be a Painter or a Plumber but not both, then software which attempts to offer criteria based on these relationships should be contextually aware of what the user is asking for.

Most often, problems like these are kept to a minimum by not offering choices too complex for the system to evaluate in a reasonable time. For instance, let’s continue our previous example and introduce two new entities/tables called Baby and Ghost. These have the relationships “A Baby grows up to be a Person” and “A Person can die and become a Ghost.” For our system, we would like to hide the fact that between the Person and Ghost steps, the person who dies must be listed as Deceased. This is much the same as we do not store the Person’s profession as a line-item, but rather as a separate listing – perhaps with dates they started and ended a particular job. To the user, the details should be available but not mandatory, and requiring the Deceased entry could be an undue hastle and cost precious processing time.

Thusly, our system has to be quite intelligent. It must realize that the Baby becomes a Person, which can obtain none or many Professions, will become Deceased, and could be believed to return as a Ghost. To keep the system under control, an interface is often limited to entities only two relationships, (“two-hops”), away from those already specified. Keeping up with our requirement for interface ease-of-use, the next logical step is to define a way to recursively discern these two-hop relationships and offer the user a means to resolve conflicts or ambiguous decisions. In so doing, we could offer the Baby and Ghost entities, (a four-hop relationship), from the start.

The question then becomes, “What tools will we need to perform such an operation?” Handily enough, I believe we have the capability, even on the Web, with JavaScript and proper algorithm implementation.