Hands on: Trawling engines

A database engine is designed to store, manipulate and query data. That’s it; nothing more; easy, really. The odd thing is that this simple and entirely accurate view is often not understood even by database professionals and, for reasons that will shortly become apparent, that can cause serious problems.

By far the most common type of database engine is the relational database engine (examples are DB2, Access, SQL Server, and Oracle). Relational engines store data in tables and allow us to organise the data according to a set of rules, producing so-called ‘normalised’ data.

Essentially the rules say that each piece of data should be stored only once and that data about the same type of thing should be stored in the same table. For instance, data about birdspotting should be in one table and data about a book collection should be in another.

One of the advantages of normalised data is that you get certain guarantees about its behaviour when you query it. If you phrase the question correctly, you always get the right answer and all of the data can be queried in a predictable way that can be reproduced.

So what doesn’t a database engine do? Database engines, whether relational or not, have nothing to say about how the data is presented to the user after the query has been run. They don’t have any ‘interest’ in how it’s presented on a form, in a report, on labels, or anywhere else. The database engine will fetch the data requested but that’s all.

Access is something of an oddity in the database world in that, in addition to a database engine, it comes with a built-in form designer and a report generator. High-end client-server databases (SQL Server, DB2, Oracle) don’t have tools for building forms or reports - you are expected to use a different piece of software for that, produced either by the same manufacturer or by a third party.

However, it still obeys the rule. Access’ database engine (Jet) has no truck with layout, it just so happens that Access also includes some additional components (form and report tools) that clearly are concerned with layout and let you go some way to controlling layout. However, they don’t always go far enough to solve everyone’s problems. The report generator especially is not stunningly good, but saying so is rather like complaining that a dog doesn’t sing very well: most database systems can’t sing at all.

Sometimes the presentation of data is a major issue and one that it’s not always easy to solve. Within the space of a few days I received two questions which on the face of it were very different but, in fact, both were questions about layout.

A question of address labels
The first question came from Rodney Bryant who runs an Access membership database, one category of which is a ‘family membership’ that covers the whole family including children. Rodney would like to create an address label that addresses the children independently, so that if Mr Smith has two children, Alice and Ben, he wants a label reading ‘Alice and Ben Smith’.

The data is normalised so it is very easy to extract a list of names of all children covered by a family membership agreement. Each child’s name will appear in a row in the answer table. To achieve a line on a label reading ‘Alice and Ben Smith’, we would have to take data from several separate rows and present it in a single row. That’s a description of a presentation job, not a database job, which is why SQL has no direct way of doing it.