Evolution of an ID System – Part 4

The fourth major overhaul saw the change from using Interbase to Microsoft SQL Server for the backend database server. Database management was the key reason for changing the database server, as compared to MS SQL Server, Interbase did not have an extensive database management interface.

2004 – Present – Delphi / MS SQL Server / InDesign:

In September 2004 development started on a new University ID Card System. Initially SQL Server 2000 was utilised for the backend, this provided an enhanced database management interface that would prove to be invaluable from a developers perspective. However, from the end user perspective the change from Interbase to SQL Server offered few benefits.

Previous ID Systems utilising Interbase contained most of the business logic within the Application, but with the introduction of SQL Server a strong reliance on Stored Procedures was used, with much of the business logic handled by the database server. The reasoning behind this change was to allow for quick modifications to the business logic without needing to recompile and redeploy the main application. Unfortunately the reality was, changes to business logic were such a rare event and they usually required functional changes to the main application anyway.

The new University ID Card System was rolled out to live production in late October 2004 and has been continually updated and still in use today, with the backend now using SQL Server 2008 R2.

Screen Shot of 2013 Uni ID System

Updates to Older Systems:

As mentioned previously we had multiple systems for various clients, of these the only other system to be updated in its current development tree was the Teachers College and Polytechnic ID System, which was updated to support Adobe InDesign CS in March 2005.

Amalgamation Time:

In August 2005 development of a new ID System was initiated, this new system would amalgamate the functionality of all the other ID Systems with the exception of the University ID System.  After reviewing the requirements of each of the separate ID Systems it was concluded that the functionality could be combined into a single system that would provide the flexibility to cater to all their needs.

Similarly to the new University ID System, this new ID system would utilise MS SQL Server for the backend, and InDesign CS for the card design.   Satellite offices that did not have access to our central server were able to utilise SQL Server Express Edition, and run on a standalone PC.

Unlike the University System, the new ID system would allow for multiple card types to be configured by the operator, and there would be no restriction to the number of card types the system could support.

To provide flexibility the system offers a fixed set of field types that can be individually configured to hold data that can be printed on the card.  While most ID Cards have a generic set of information printed on them, some cards require customized information to be printed, fortunately the new system provided ample fields to facilitate this.

Screen Shot of 2013 OSC_IDS ID System Field Configuration Form

Batch Printing:

One of the most valuable features the new ID System would have was the ability to batch print.   Batch printing allowed the operator to specify a specific group or range of cards that would then be printed autonomously.   Batch printing functionality was added in late 2006 and reduced the amount of time to print an entire school from a couple of days to a couple of hours.  The system was only limited by the speed of the host computer and printer.

Screen Shot of 2013 OSC_IDS ID System Batch Printing Form

The batch printing functionality has been continually developed with additional features providing even greater flexibility.  The functionality provided by these additional features has not had to be altered since November 2011, this gives a good indication of the flexibility the batch printing functionality can provide.

The Good:

Utilising MS SQL Server for the backend provided an enhanced development and management interface that was considerably ahead of the interfaces available for Interbase at the time.

The fundamental designs of the ID Systems (both new Corporate/Schools and University Systems) has allowed them to be easily updated with additional functionality.  The robustness of the design choices have allowed both systems to be utilised for 8 and 9 years respectively.  This development stability is in stark contrast to the previous systems that saw numerous iterations released within a single year.  The fact that both these systems are deployed and still in use today speaks wonders for their development, especially when compared to previous systems.

The flexibility of the Corporate/Schools system has allowed it to be utilised for varying array of Card Types, some requiring their own distinctive configuration to satisfy the client.  With previous systems this was simply not possible.

The Bad:

In respect to the Corporate/Schools system, due to it being utilised for an extended period of time, it now has a large list of card types and some areas of the system are a little cumbersome to use.  While card types can be set inactive, there are areas of the system that still require all the configuration data to be loaded from the database (namely card type configuration forms).  As additional card types are added to the system it continues to get slightly slower, and the process details progress form becomes a common (sometimes slow moving) sight.

Screen Shot of 2013 OSC_IDS ID System Progress Form

The loading progress form can be even more frustrating if a simple configuration change is required for a card type, as configuration information for all card types needs to be reloaded.

The Ugly:

Fortunately there are no ‘Ugly’ anecdotes relating specifically to the ID Systems themselves, however there is was one small issue related to the rollout of the new University ID System in 2004.

The change to MS SQL Server for the backend saw the use of the ‘DateTime’ field type, where as data for some items was stored in a ‘Date’ field type within Interbase.  Unfortunately this caused an issue in a nightly batch process that exported data to a 3rd party, because the field was expected to only contain ‘Date’ information the existence of ‘Time’ information as well caused a major issue for the 3rd party’s systems.   However the problem was quickly resolved by formatting the data to only export the ‘Date’ information.

Coming Soon:

Now we have caught up with an overview of the previous and existing ID Systems the aim is to concentrate on Terian, which is the new ID System platform currently in development and which only just recently rolled out for live testing.


About Anthony Dowling

Founder Sjones Limited, focusing on developing ID and Security Card Solutions. Long time Delphi Developer. @AntDowling
This entry was posted in Uncategorized and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s