Andrew David Joseph Hall
1993-1994 Distributed Client/Server SQL Database
In 1993 and 1994 I undertook my last project for the Royal Bank of Scotland. It involved taking over and rescuing a failing project.
The Project
Upgrading an existing data capture and reporting system for Central Credit Control from stand alone to distributed databases.
In my role overseeing the introduction of Personal Computers into the International Division of the Royal Bank, I had worked with the Central Credit Control Department to build a system to capture exposures to Corporate Customers and apply weightings and aggregations to allow Central Credit Control to understand the Royal Bank's exposures for the purposes of ensuring Capital Adequacy, consistent with the first Basle Agreement on Capital Adequacy.
The database allowed a Corporate Customer’s total current and historical exposures to the bank to be monitored and reported, including all the customer’s subsidiaries and major investments.
This system used the Smart System database on three separate PCs (London, Manchester and Edinburgh) and was kept one day behind the times by exchanging floppy disks and merging the three separate databases.
The new project was to upgrade the system to a distributed database, allowing more users to enter data and obtain reports as well as bringing the data into real-time in the three locations.
Gupta's SQLBase and SQLWindows and Novell Netware had been selected as the platform for the new development.
Why the Project was Failing
The project was failing for two reasons:
- They had written reams of code that was functional, in small pieces, but could not be integrated.
- The team did not have a good understanding of database locking strategies on SQL Databases.
My Approach
I put together a team of 3 programmers.
The team shrank the code and made it more robust.
We did this by building components or "widgets" that could be "dropped" into a dialog and used data from the database to control their behaviour rather than code.
The widgets interacted with the database but not with each other so integration and testing were simpler.
We built the time-series dimension into each component or "widget".
The size of the code reduced from Megabytes to Kilobytes so it worked on the Computers that were available at that time.
Widgets allowed rapid prototyping of dialogues and assisted in refining the requirements definition with users.
We redesigned the database:
- Implementing a minimalist, but effective, locking strategy
- Including system data in the database rather than in the code
- adding a time-series dimension
The reporting requirements of the system were quite sophisticated and the success of the system depended critically on the quality of the design of the database. My data-analysis and design skills ensured that the database design we used delivered the funtionality that Central Credit Control wanted.
Adding the time-series dimension allowed historical reporting without batch processes to capture "views" of the data on reporting dates.
Re-usable Code
Using Gupta's SQLBase and SQLWIndows we developed re-usable code whose behavior and content was determined by the results of SQL queries - built dynamically within the code - from the SQLBase database.
Widgets
In modern Internet parlance we built our own SQLWindows/SQLBase "widgets" which could be dropped into a dialogue and populated with attributes and data from the database.
Each data element in the SQL Data Base had "valid-from" and "valid-to" date time stamps so each Widget understood the data and time for which it was intended to be operating and the queries built by it would return the data relevant in that time frame.
Widgets allowed us to build separable, testable and reliable code.
Widget code and data were isolated from other widgets and testing of each widget was thorough. As a result, each instance worked as expected, no matter how often it was used.
.svg.png)
