How Many Tables?

Database systems can be constructed using only one table to hold all information of all sorts, they can alternatively use a separate table for each type of information. In the GP clinical record context either can work. Practicel implementations usually lie between these extremes. Because information will always be in multiple sites and from multiple sources we need to accept it from those sources. XML may render much of the distinction moot.

It is possible and is an interesting theoretical exercise to restrict oneself to using a single table to hold every item of infromation in a database system. The essential is to use one field as a descripttor of the type of information held in each record, and taking it to such extremes requires that the start of the table is filled with its data catalogue, the Read Code, details fo teh initial users and so on.

The audit trail can be combined into the same table, protected with encryption and hashing and so on, and this perhaps answers one of the problems of audit trails, that if they are separated from the data their validity may be questionable.

The opposite end of the spectrum is where each time a new type of information is considered worth recording a new table is created.

Systems built on this principle will have many tables, the summary may be in a different table from the family history and repeat prescriptions stored separately from acute ones.

There are advantages to this. The all-in-one record is likely to include several fields which are needed to allow various types of data to be shoehorned into it, if multiple tables are used then this is reduced and with it there may be a reduction in wasted disk space. This is of little account nowadays of course.

The law of unintended consequences operates on computer programs, changing one thing produces changes in another unexpected part of the system. Making the system as modular as possible is as sensible a way of reducing this as is making the data storage as uniform as possible.

Dividing the data up may make access controls easier than keeping it all together, although it does not approach the Blue Book requirements for access control lists to each datum.

As data presentation and interchange, and perhaps even data storage, migrates toward XML the difference in effect of these opposing philosophies will be steadily reduced.

If we must produce systems which search large numbers of documents, each of which might contain information of the sort sought, then there will be less perceived advantage to answering questions by searching only a single table.

Meanwhile, as we link systems, our search engines and presentation managers have to include an element in their database abstraction layer capable of communicating with each target data source, and managing the security requirements of each conversation.

Using up much effort on improving the structure of an underlying database in an existing GP Clinical system is not to be recommended, although if the system is well-written in a modular form with an abstraction layer between the data and the presentation manager and hinting engine layers of the system there is nothing to prevent a steady neatening up of excessive tables if nothing else more worthwhile presents itself to the developers.

How many tables? As many or as few as you like. Relax.