Optimized design approach for a large database quick-referencing and lookup task bar tool

First some background…

I work for a fairly large and reputable mortgage company as a .NET Developer, supporting their proprietary software package. Their database infrastructure is massive and non-intuitive. The system follows a non-relational design approach.

The problem I’m attempting to solve…

During software bug fixes and improvements, the department often performs repetitious tasks such as looking up row ids and what they represent, table names, columns, etc. The task is done mostly through our proprietary software suite through a developer access account. The other option is to query the database directly through SSMS. Either method takes time and is often repeated multiple times throughout the day by multiple developers, ultimately effecting productivity.

My solution…

I have started designing a winforms application that will run in the windows task bar. This application will utilize the windows API by hooking into the system clipboard to monitor and respond to global hotkey events. It will keep a local cache which will contain frequently referenced/look-up data such as IDs and their relevant metadata. This cache will contain a fairly large amount (100000+ rows) of metadata which is frequently refreshed on a variable timeframe, for example, every 3 hours.

When the application is first run, it will build its local cache of metadata and periodically compare against the larger data source (SQL Database) on the server for new row changes such as inserts or updates which it will mirror in cache.

While the application is running, it will monitor for changes in the system clipboard. When the user copies a string or value, it will query against its much smaller local cache to see if there is any relevant metadata that might be useful to the user. When a recognized string or value is detected, a notification will be presented to the user similar to that of an Outlook Express tooltip when a new email is received. Otherwise, the application will ignore it.

When a notification is received, the user will be given options to view the relevant metadata in a pop-up, or substitute a string or value with any piece of relevant metadata. For example, the user could replace the ID value in the clipboard with its relevant Name value and vice versa. So when the paste command is used, it will paste the substituted value as opposed to the original copied value. The user will have the option to use registered global hotkeys to perform on-the-fly substitutions and drill-down lookups. The application will also store usage statistics such as when a value was looked-up, or how often.

My ideas on how I’d design such a tool…

Firstly, I will be using C# with the .NET Framework 4.5. The source data is kept on a very large SQL database with over 300 tables where the largest dataset is over 6.4 million rows. So obviously, querying against the source is out of the question. So I will be using a local SQLite data cache which will be periodically updated with row changes. It’s free, flexible, doesn’t require any installations on the client system and works out of the box.

The SQLite cache will be frequently queried against, so speed of metadata retrieval is of the utmost importance as the user needs to be notified as soon as the clipboard change event is triggered. I’ve decided to go with a relational data structure for this reason. Each table row in the local SQLite cache will contain a pre-calculated MD5 checksum to act as a row version representing its row data. This checksum is specific to the application and will be calculated on insert or asynchronously behind the scenes.

The issue is, the source SQL database does not contain timestamps or rowversions or checksums of any kind. (Badly designed source database by a previous development team, but nothing I can do about it sadly as much as I’d like to.) My solution to this is to periodically calculate the MD5 checksums for each row of the source database and compare the row to what is in the application local cache.

This whole process is meant to avoid having to truncate the cache tables and having to bulk insert the entire dataset of the source data on every cache refresh. This would be cumbersome on the SQL source system especially with X amount of clients calling the same local cache refresh at a given interval.

Some of the metadata that I want to be made readily available to the user can be text blobs with up to 4000 characters in length such as Scripts or notes. This will be information that the user can choose to see as opposed to being instantly available to them in the details popup or notifications tooltip. For example, this information can be accessed only if the user navigates to the Script/Note control tabs explicitly. For this reason, I’ve chosen to separate these blobs/texts of data into their own separate child tables of the main metadata table. My goal is to optimize for near instant metadata retrieval.

The application will need low level access to the clipboard and keyboard input events. In which case, I will be using Win32 native calls to monitor for these events.

Finally, my question(s) to you… the Obi Wans of the programming and design world…

If you were the designer, what changes would you make to optimize my design?

What else would I need to consider?

What issues or implications would you foresee me running into?

What design-pattern would work best in this scenario?