SumPROP Complex Searches
Last modified: September 6, 1996
The Property Office wanted to have real ad hoc "point & click" access to their data. They wanted to be able to do complex searches, using sophisticated Boolean logic against every attribute in their database and they wanted to do it all from their PCs. Using one of my favorite Macintosh applications, TouchBase Pro, as a model, I designed the above "complex search" function. It was one of the most satisfying projects in my career. Until...
Have you ever done a seven way join against a database containing hundreds of thousands of rows from a personal computer? It timed out after 17 minutes! Argggg... As one of my colleagues and mentor would say, "The heart break of it all."
I put this task aside for several months and went on with other aspects of the Property Office project. 
When I returned to the problem I decided that a solution (if anyone knows other solutions, please e-mail me) was to flatten the relational data model. On a weekly basis, over 15 tables are now flattened into a single table, allowing the end users to run such queries as the one at the top of this page in a matter of seconds. Any reports coming off this table always have Saturday's date in the corner as a reminder that it is not real time data. They can run simpler queries & reports against their real time data.
| .................... Carolyn's Home Page |