2 Shows Live, SQL Server World Changes, More

We have developed a proprietary "Records Management System", which is really just a generic database where users can create custom "Record Types", store them, retrieve them, make reports, etc. A record is just a set of fields really. You could think of a record type as a table and the columns are the fields, but actually we use a single table whose primary key is the record id and field id, so fields become rows. We felt this was the only way to go if you expect to write reports and perform searches spanning many record types, and if users can create an indeterminate number of record types.

As a "gift" to our users, we gave them an interface that lets them organize their records in a tree structure, in other words, hierarchically. For example, they can have a root record (let’s say of type "Container", just a generic record for placing other records under). Then they can organize other records under the root as children, which can have children, etc. We maintain the tree structure in a separate table, each node pointing to a set of rows (fields) in the main data table.

This opened a can of worms because now users typically will want to ask questions like "show me all my documents stored at any level under the ‘Department X’ record with field ‘Y’ = ? and field ‘Z’ = ? and…, and sort it by field ‘W’…"

Well, the problem with that is that you get into some very nasty complicated SQL requiring hierarchical relationships on top of the normal nasty SQL that occurs when you use rows for fields instead of using columns. This proved too much to take on initially, especially for designing our report generator module which must generate custom SQL for each user query.
Thus for now, we use a heavy handed approach — we only support queries against the fields of the record (i.e., only relational queries), so if they want to find records under a particular department node in the tree, they must create a Department field in the record template and each record must have it’s Department field "coded" (filled in).

Of course that has many weaknesses (extra data entry work, data entry errors, maintaining changes to redundant fields), and we are researching alternative designs that would allow true hierarchical queries.

Hierarchical organization in fact opens the door to object oriented thinking for the user, where they may want to think of their data in an object paradigm. We have felt the usefulness of this ourselves.

As an example, a record is associated with a complex formula called a "Retention" which determines when the record is due to be destroyed. That formula can be a record in itself, with fields the represent the variables and constants in the formula. It is very reasonable to think that a user would want to search for all records whose retention formula specifies a destruction method of "shred", for example. In our current model, we would have to include a field in the record itself called "Destruction Method", whereas we’d rather just have a single field specifying the retention, and link to the Retention record as if it were an object which is a member of the record. Such a query is perfectly possible relationally, and we did in fact implement it. But the generated SQL is very ugly (the linkage is on field values causing many intermediate self joins) and not generalized to arbitrary relationships between records (objects).