There comes a time in the life of anyone working with databases when they are struck with a peculiar mental aberration. For want of a better term, it is usually called ‘One True Lookup syndrome’ or OTLS. It is in the same league as the rather more severe affliction ‘EAV disease’ (Entity Attribute Value). It is like measles or Mumps in that it afflicts the sufferer just once and he, or she, is thereafter immune. The memory of the suffering and delirium is retained, however, and the feelings of embarrassment.

The sufferer is easy to recognise. He shakes hid head in wonder, saying ‘This is like such a cool idea, how come nobody has thought of it before, It is like wow! I mean it is so neat’. A classic symptom is that they will sidle up to you to ask how to patent software ideas. We grey-muzzles will shake our heads sadly, with a pitying smile, and just mutter solemnly ‘One true lookup table’.

OTLT syndrome comes about usually when one is forced to create a number of simple lookup tables which contain a code and a name. One gets the sudden flash of inspiration that one could roll them all into one lookup table, with a unique identifier for each row, and a type field, thereby saving the chore of creating and maintaining a number of tables. EAV disease, like OTLT syndrome both tend to be the result of pressure from programmers to push their OO designs back into the relational database, like a bathyscaphe in an alien environment . There is loose talk of ‘persisting object data in a data store’, as though one was storing apples in a shed. It also comes about when a development team attempts an ‘Agile’ development before they’ve fully understood the data model.

I remember the first symptoms well. You are under stress: in my case it was a systems analyst whose analysis skills were at about the same level as a dead sheep’s. The database was for an internet system for car dealerships, selling second-hand cars. It was supposed to value your stock, and to enable groups of dealers to supply a punter with the exact car he was looking for. The Systems analyst was making very heavy weather of understanding the business processes of the Dealers. He kept changing his mind, yet wanted a system that he could demonstrate to the dealers. It suddenly occurred to me that, instead of having a normalised and rational data model, one could store the ‘soft’ attributes of each object in a single table, the ‘Entity Attribute Value table’. One could then accommodate a changing schema without changing the structure of any table.

It all seems so easy. You have an ‘object’ table that stores the metadata of each object stored. If you are severely stricken, you make this hierarchical, so that you can implement inheritance. A swift lookup will tell you what attributes there are for any particular object and what their data type is. If you are heavily into Dynamic SQL, then you’d add rules, constraints and defaults.

You then have one instance table that is shared by all objects. The value is stored as a string, just as it is in SQLite. When a new object is created, it receives a unique identifier, and its attributes are written into this table

With this system, you can make changes to your data structures ‘on the fly’ without any apparent need for a-priori database design. You then will wish to record the relationships between your objects. I popped in a relationship table. One could then do fairly complex queries with remarkable ease. In my case, it would be ‘how many different models of car were manufactured by Chrysler in 2003′? , what is the current value of all the cars on the various forecourts?’ or ‘What Ford estate cars are currently for sale on any of the forecourts.’

I loaded the system with all the current data from ‘Glass’s guide’, so that I had all the necessary information for all the cars that had been in volume production for the past twenty years. Everything worked fine. I created test data by inventing several subscribing dealerships, and filled their imaginary forecourt with many imaginary cars. I then created an application interface based on stored procedures. I felt really pleased.

The application programmer who’d been assigned the task of creating the Internet site hated the interface. He was used to direct SQL access to tables via Cold Fusion. His first step was to create a scrollable listbox containing the details of all the cars on the forecourts. At first, he claimed that Cold Fusion didn’t support stored procedures: then he decided that my stored procedure wouldn’t work. After I’d tired of trying to explain how to go about it, I created a view for each object, created dynamically from the Object table every time a change was made. He was much happier then but decided he wanted to update the ‘Table’. The view wasn’t updateable. I wrote a stored procedure and showed him how to use it. He began to feel resentful that his programming knowledge had been ‘shown up’ and began to drop hints around the office that the database was no good.

For the Systems Analyst, who was struggling to explain the slow progress he’d made in coming up with the business and process model, the sudden rumour of problems with the database came as a sudden unexpected lifebelt. When pestered by the project manager, he put on his best ‘Mr Sincerity’ face and reported that it would have all been finished had it not been for the confusion caused by Phil Factor’s wild and wacky database design.

Things were getting difficult. A string of anxious managers demanded that I explain the database design to them. As they had no grounding in either object methodology or relational databases, this was always going to be a doomed mission.

It must have been around the time that I devised a meta-language to describe the objects and their relationships, that I had a sneaking suspicion that all I’d done was to write a database system in SQL Server. My tables were beginning to look eerily like the system tables in SQL Server.

Another problem loomed. Checks, constraints and rules provide the bedrock of maintaining the integrity of data. It is really tempting fate to say that, if there is an interface based on stored procedures, then bad data never gets into the system. One law of Relational Databases is that, if you put in all the necessary rules, checks and constraints, you never see bad data, and if you don’t, then it somehow insinuates itself in by some supernatural means. If you have mixed entities in one table, then the business of implementing these checks becomes difficult.

I was just reaching the point of crisis with the database, when I was facing the decision point of replacing my beautiful, elegant, complex model with something more conventional, that my guardian angel caused a buyer to appear who wished to purchase the system. My database had served its purpose, which was to provide a slick demonstration of a system before the hard work of understanding the real business processes had been done. The vision opened the purse-strings, and I bade farewell to my splendid database system. I did not pine for it, since the fever of OTLT and EAV had passed.