Summaries of bespoke database projects

	Professional Database Developers
Aldex Software Ltd.

Introduction

This page contains brief summaries of some of the many projects that we have undertaken over the past few years. If you require further details please contact us. We also have a list of our clients and some comments that they have made about us as well as a few more detailed case studies and screen shots of some of our applications.

Image Capture & Retrieval
An interesting and somewhat unusual application! The client required images to be captured from several different car assembly lines and to then save them into a central SQL Server database (ie in the database itself rather than as external files). The images were to be retrieved from the database at a later date via bar-codes. In addition to the image capture the system also needed to interface with the assembly line controllers and with various robots. This was both to co-ordinate timings (eg to control when an image was taken, to move a robot arm at the correct time, etc.) and to retrieve data from the various controllers. This retrieved data is then compared, together with an OCR (Optical Character Recognition) extracted version of the data, and if any of the data items do not match then a message is sent to stop the production line!
As you may expect it required significant on-site testing and had to be very robust and reliable since a problem in the software could shut down the (very expensive to run) assembly lines. The application was developed using VB6 and SQL Server and went live in June 2003.

Sales Force Automation (VB6 + SQL Server + SQL Replication)
Designed to allow a mobile sales force using disconnected laptops to easily undertake highly complex calculations simply by entering/selecting a series of values and parameters on the screen (customer details, equipment required, quantities, options, accessories, operating environment, annual hours, support package, finance options, term, etc.). The system then calculates standard weekly rental figures which the salesman can further adjust by using an on-screen discount slider; which also affects his commission! Data is then sent back to a central database by means of SQL replication, which is also used to update the laptop with any new versions. Management and Admin. staff then review the quotations on the central database, authorise if necessary and print out contracts ready for signature. Automated e-mail and SMS text messaging is also integrated into the system. The system was developed using SQL Server (both standard and desktop editions) and Visual Basic. It supports 65 field based staff and 25 back office staff spread throughout the UK. It went live in Jan 2002.

Outsourcing Contract
This is a full outsourcing contract for a multi-national corporation involved in the manufacture of telecommunications equipment (everything from mobile phones to repeaters for sub-sea telephone cables). We provide both development and support facilities to the company and the contract is currently in it's fourth year. The database handles returns and repairs for the company’s nationwide equipment sales and currently has some 25 users on three different sites connected over a WAN (Wide Area Network). After winning the original contract we rewrote the software to Best Practice standards, added data validation, error trapping and audit logging routines, resolved a range of outstanding problems and converted it from Access 2.0 to Access 97. In August 2000 we redesigned it and converted it to SQL Server 7. We support the application by a mixture of on-site visits, telephone calls, remote dial-up and e-mail. In addition to this contract we have also undertaken a number of database development and support activities for other departments within the company, based upon recommendations from the original department.

Attendance Logging System (VB.Net + XML + SQL Server)
This is a system for logging attendance in schools and colleges which interfaces with a variety of other systems. On entering the room for a particular lesson each student swipes their personal smartcard through an Infineer card reader. The system then reads this raw attendance data and compares it with a Local Authority database which holds details of the students and which smartcard they have been given (this piece of the jigsaw has yet to be implemented at the Local Authority so until then we are emulating it in a local database). From this information we can determine the room that the card was swiped in, the time that the card was swiped and the student associated with that card; hence we know which student was in what room at what time. A further interface is used to extract information from Capita's SIMS educational package detailing which lessons each student is timetabled to attend and in what room. By comparing all of this information any latecomers or non-attendance can be detected. Details of any non-attendance is then sent to the relevant school administrators during the lesson in question. Historical data can also be extracted to identify trends such as persistent truants or the effectiveness of any new school policies. In addition to attendance at lessons the system also logs both morning and afternoon registration. Overrides for school trips, authorised absences, room changes, etc. are also included. This application was developed using VB.NET with SQL Server as the database engine and using XML to exchange data with the SIMS system.

Hotel Bookings
A large multi-user system written in Visual Basic with a SQL Server 2000 back end. It logs hotel and conference bookings on behalf of corporate clients and includes searches, bookings, confirmations to the hotel, discounts, incentive vouchers, commissions, a complete accounts sub-system (invoicing, payments, dispute handling, statements, VAT, etc.). This system makes bookings world-wide and hence has to cope with foreign currencies and conversion rates. A third party fax server (Fax!Now) as well as e-mail is integrated into the system. It went live in August 2001.

NHS Cataloguing and Requisitioning System
This is a large project consisting of a suite of some 8 different Access front-ends and 4 different back-end data stores together with an internal e-mail system. The project was developed over an 18 month period and forms a complete, integrated, supplies system for NHS Hospital Trusts. It is implemented throughout a hospital with nurses or ward clerks placing requisitions for goods or services from an electronic catalogue on the ward, these are then sent over the network, authorised electronically, consolidated together (to take advantage of bulk prices), split up by supplier and ordered via the method appropriate to each supplier (paper, fax, E-mail, EDI). When the goods are subsequently received at Goods In they are checked off, the packs are split and the individual items are then delivered to the appropriate ordering point. Also included is an electronic Catalogue Management System and a local Stock Control system incorporating bar coding and automated replenishment of stocks. Some of the many complications of this system included sophisticated budget control (for example different budget allocations for each requisitioning point for each month), multiple authorisation mechanisms, a built-in email messaging system, complex receipting requirements (incomplete or partial deliveries, split deliveries, returned goods, alternatives, goods delivered but not ordered, goods on loan, sale or return, etc.), multiple catalogue support (the same item may be available from more than one supplier/catalogue and with different prices), emergency orders (which had to bypass the normal order consolidation channel) and differing order consolidation schedules for different suppliers (orders for one supplier might be consolidated every 4 hours whereas for another it might only be once a week). The package was developed in 1998 and is currently running in 6 different NHS Trusts.

Marketing Client/Server Database
The client for this project is a multi-national media company who wanted to convert an existing series of separate, isolated databases held in various forms (eg Access, Excel, Word, paper based records, etc.) into a single, consolidated, SQL Server database that would act as a central resource for all of it's offices throughout Europe. Timescales for this development were very tight as the original software company chosen to undertake the work was unable to complete it. We were therefore brought in rather late in the day and, for legal reasons, had to undertake the development from a clean sheet with no reference to the previous work (which was probably a blessing in disguise). By permanently chaining a couple of our developers to their desks for a few months (just joking..., we let them have an hour off after the first month!!) we managed to pull back the original time estimate by a month and squeaked in with the final version just one day before the deadline expired. The system was written to a Client/Server design with an Access 2000 front end and a SQL Server 7.0 back end. The main challenges were the normal client/server ones of optimising performance to ensure that the heavy work was carried out by the server rather than by the client, catering for multi-national dates and address layouts and in designing the system for multiple, bi-directional, replication. This system went live in Jan 2000.

Emergency SQL Server Database Recovery
First thing one morning we had a worried call from an Australia company. Their main SQL Server database was 'suspect' and they were unable to access it. Unfortunately they did not have an recent backups (oops)! Could we help? Luckily we were able to recover over 99% of the data, although some of the rows in one of the tables was irretrievable. Needless to say our new client was very pleased : "Once again thanks for everything. From a potential catastrophe yesterday where we thought we lost all our payroll data we now only have a couple of minor niggles with the system which can be easily fixed."

Call Centre Data Capture
A system for company in the financial sector that is used to capture data in a call centre. Information logged includes customer details, the advert that was responded to and a range of financial details. The application was developed using Access combined with Echo CT Integrator which is used to perform call-popping. This integrates the telephone and the computer so that when a telephone call is answered the call details (originating call number, number dialled, etc.) are instantly available to the application. This allows the telephone number of the caller to be captured and automatically fed into the system, where it can also be checked to see if any previous calls have been logged from that number (and if so then that customer's details can be automatically popped up on the screen without the operator having to type anything into the computer). The number that was called is also used to capture details concerning which advert the user responded to (as each advert uses a different number). This system went live in May 2002.

Web-Enabled Database
An application using a browser front end linked to a SQL Server back-end over an Intranet. The system allows users to View, Add and Edit details about forthcoming projects in order to provide management with an strategic outlook for the next few years. As the client wished to employ both Netscape and Internet Explorer we developed the user interface as a server-side component using Visual Basic Web Classes. This is browser independent and can scale to high numbers of users. In order to make the system more user friendly, and to provide greater interactivity, we included a number of client-side JavaScript components. Security was handled using NT challenge/response authentication (otherwise know as NT integrated security) allowing users on appropriate NT domains to use the system without going through an additional username/password login (i.e. we detect and used the existing authenticated NT User). This system went live in September 2000.

Campaign Management/Bar-Code Tracking System
This started out as a relatively straightforward Campaign Management application written in Access to handle marketing campaigns for a high street bank including calculating the number of posters and leaflets required for each campaign and producing picking lists for each branch (every branch has a different combination of marketing positions hence the posters/leaflets for each branch are different). The original version went live in the late spring of 2000. We have subsequently extended the system to add package tracking facilities and to integrate it in with the TNT freight distribution system. This involves the production of special bar-code labels, scanning packages prior to collection, the transmission of files containing the scanned information to TNT's Unix mainframe and the subsequent downloading of files from TNT's mainframe containing details of the packages scanned by TNT. By this means we can track a package all the way from initial production through to delivery to the customer. As the system runs continuously one interesting feature is it's ability to automatically send an SMS message (ie a text message to a mobile phone) or a pager message if there is a problem with the system during the evening/night.

Sales and Marketing Database
This company produces special effect equipment for television studios. The requirement was for a Sales Support and Marketing database, written in MS Access, to maintain a list of contacts, enquiries, customers and sales details including both main and secondary equipment (peripherals, accessories, cards, etc.), serial numbers for both main and secondary items, security codes, access permissions, version numbers, etc. In addition to the standard Sales and Marketing functions the system had to cope with swapped out or replaced secondary items, changes to security codes/permissions and to keep a history log of all equipment, including, for example, the date that a secondary card was swapped for a later version, what the original cards version and serial number were, what the new cards serial number and version are and what the new permissions are. Other facilities included mailmerge to Microsoft Word for letters and mailshots and the import and synchronisation of contact and customer data from a Microsoft Outlook database. The system took two months to develop and went live in Dec 1999.

Administration Database
This Access database was for a UK government organisation that provides advice and assistance to foreign students wishing to study in the United Kingdom. The organisation had previously developed a series of basic databases to help with the administrative work of the council, for example to record student registrations, maintain lists of UK educational establishments, store contacts at those establishments, record subjects and the level of course offered, log qualifications awarded, record links with overseas educational establishments, identify companies offering sponsorship and so on. The initial brief was to take the two most widely used databases and to turn these into proper, professional systems. This included a range of tasks including redesigning the database into a normalised relational form, splitting it into a front and a back end, adding automated re-attachment routines, tidying-up the user interface to provide a consistent, 'branded' look and feel, cleaning up the code, replacing user entered text with drop down selection lists, adding a flexible reporting utility and adding archive and restore functions. This work took just over a month to complete, was finished in August 1999 and has been a big success. Subsequently we have converted a further two databases the same way.

Emergency SQL Server Support
Our answer phone recorded a message, left at 1:30 in the morning (!) a few days before Christmas, asking if we could provide emergency help for a SQL Server installation that was due to go live very shortly. On talking to the caller the next morning (... we do sometimes work late, but not usually that late!), it transpired that they were putting in a new system (SQL Server database engine with an Access front end) but were having severe performance problems and wanted someone to have a look and make sure that SQL Server was configured correctly. We duly arrived the next morning and, as we already suspected from our conversation with them, the problem lay in the poor design of the database and associated application rather than with configuration settings in SQL Server (which is what they were hoping for). For example most of the forms were set up to retrieve all records, whereas for client/server one should try to only retrieve one record at a time. Additionally the system was not using Server Side Views, Stored Procedures or Pass-through queries; hence all of the data for each table had to be retrieved over the network before a (local) query could be run; the exact opposite of how client/server is supposed to work! We took one form, which was taking 10 seconds to move from one record to another, and rewrote it using a mix of Pass-through Queries and Server Side Views. The time dropped to less than 1/2 second, a more than 20 fold increase!

Engineering Company Database
This engineering company manufactures bulk material handling equipment, such as grain elevators and coal conveyor belts, the majority of which is exported overseas. Their specification was for a large and complex Access database to form a complete 'Line of Business' system starting out with Enquiries, through quotations, stock control, contact management, job creation, logging drawings, assigning personnel, tracking labour and materials used, managing costs, documenting project meetings, producing invoices, maintaining equipment history records and finally producing a range of management reports and statistics. Complications included multi-currency dealing (as sales could be anywhere in the world), logging export documentation and handling complicated contact/company relationships (one parent company could place the original order to be delivered to a subsidiary in a different company and a different country, all with a web of varying contacts for different purposes and which might change over the life of the project. Another complex area was in the handling of steel stock, which can be priced and ordered by area, length or weight, is removed from stock for a job by length and area with any unused steel returned to stock, but perhaps with different dimensions since it may have been cut up and partially used. This system took just over three months to develop, went live in December 1998 with 15-20 users and has been running successfully ever since.

Mail Order Database
The first version of this system was developed for an antiquarian book dealer who sells specialist books worldwide by mail order. It included a book catalogue and supported quotations, letters, orders, delivery notes, financial management routines (customers in debit, amounts outstanding, spend per customer, credit limits, etc.), reserved books, 'wants' and customer interests. A further requirement was the need to produce catalogues of books relating to a particular subject, or subjects, excluding any books that were present in the last catalogue on this subject, and to then produce a mailing list of customers with the appropriate interest. Another complication was the need to use a dedicated label printer due to the large number of mailing labels required. This system went live during the summer of 1999. Since then we have created versions of this system for two other mail-order companies; both with varying requirements from the original.

Facilities Management Application
A Facilities Management system developed for a privatised arm of the old Property Services Agency. The software has a very wide ranging set of functions including a hierarchical Asset Register, Forward Maintenance Register, Asbestos Register, Inspection Scheduling, Planned Maintenance, Budget Forecasting, Risk Analysis and various Small Works functions (for example placing orders with contractors). This system took three months for the initial development with a further 2 to 3 months for subsequent enhancements and went live in February 1999. It is currently installed at over 60 MOD sites as well as at three Local Authorities. We continue to support it and have undertaken a wide range of further enhancements and changes over the years.

In 2003 we developed an alternative version of this application using SQL Server and VB.NET. This was required both to cater for the specific requirements of a major MOD project as well as the need to run the system over low bandwidth WAN links.

Pricing Analysis Database
A reasonably straightforward data entry and pricing analysis system for a major pub and hotel chain operator. The system logs quarterly pricing data for a range of products at each of their 'houses', together with equivalent prices for each establishment's main competitor(s). A series of statistical and analytical comparisons and reports are then generated which are used in setting prices for the following three months. One complication with this system was the requirement to have consolidated prices in bands, for example 57 houses were 1 to 2 pence more expensive than at the last survey, 18 houses were 2 to 5 pence more expensive, 4 houses were more than 5 pence cheaper, etc. This system took six weeks to develop and was completed in the autumn of 1998.

Return to front page