Database usability

Before we migrated to MS ACCESS 2007, we noticed that we could not sort queries by property/description in MS ACCESS 2007. This caused a big problem for us since a lot of our tasks are based on several queries and we try to keep them together by coding the description. Why we chose to do this in the first place? Well, as we all know that ACCESS isn't build to be a power house and there are times you have to combine several queries to achieve the task on hand. We did not chose to code the query name since changing the query name may break other queries.

Because of this, we began a project that stored all queries in a table and on top of that we build other facilities to link queries into tasks. In the process, we constructed a framework that we think can be a better user front end for databases.

The framework create a layer on top of the query so that we can assign a path to classify a query. This is like a link in the *nix file system. We can then build a tree structure that allows us to locate a query. We also build a task layer on top of the query so that we can combine couple of queries to create a task. On top the task, we also build a tree structure that allows us to organize tasks.

As mentioned earlier, we stored all our queries in a table, this gives us the ability to search the table and build dependency information between queries. We can also build the dependency info between queries and tasks. All of these helped us in maintaining our projects.

For example, one of our tasks is to import data from external sources. The data format and definition can change from year to year. The process/task to import the data can also change from year to year. Every year we did our best to import that data. However, from time to time, we found that we process the data incorrectly later. It become obvious that we need a good way to document the task we performed every year so that in case of error, we can trace back our steps.

With the system we have, we can create task alias/paths like:
Organization/DataType/DefintionCheck/2007
Organization/DataType/DataImport/2007
etc...
In the case that the data come in in the same format like last year, we can simply create a new link that pointed to the same task of last year. Otherwise we will create a new task and alias.

With the path/alias, we can also build a tree structure that make locating queries and tasks much easier.

At this point, the system is not finished yet and we are trying to make our system more universe so that we can adopt this system to, say, the OpenOffice database.