Hands on: The future of Access revealed

According to Suraj, Microsoft believes many users find this too difficult to do and a multivalue field lets those users achieve the same end in a simpler way.

The problem is you’re then sorely compromised because querying becomes more difficult and very different from the way in which it behaves with single-value fields.

The difficulties associated with multivalue fields were first identified and discussed in the 1980s, so the problem, as they say, has a history.

There is a bit of good news here, though it’s only obvious when approached from an odd angle. For a start, Microsoft is quite right that multivalue fields are conceptually easy for users to understand, which is why the company has taken this route.

It is possible to separate the ease of use of multivalue fields from the actual implementation. So, for example, while the user might be able to put multiple values into a single field, it is perfectly possible, under the covers, to store the data in the traditional manner, in three separate tables.

So the graphical interface can present the power user with what appears to be a multivalue field but, in fact, the engine could store the data as a normalised set of data in three tables.

So you think, “Aha, that makes perfect sense: Access 2007 is making it appear easy for the user while handling the data in a correct relational way.” Well, it is, but up to a point.

If you address the Ace database programmatically, you can see all three tables. If anyone (including developers) looks at the tables in any other way, for example using the database design window, they will only see two tables, one of which has a multivalue field.

You may think this borders on the worst of about three different worlds, and I would have to agree. What has happened is that the development team could only achieve a certain amount in the time available and hasn’t managed to present the full set of three tables in the database window.

Suraj says this will happen in a future version but, in the interim, we have to contend with this odd half-implemented feature.

The user sees multivalue fields (which are easier to understand) and the data is held in a fully relational way. The weird bit is that the GUI designed for developers won’t show you those perfectly formed tables.

My advice is simple: don’t use the multivalue field. It is simply an adjunct to the tools we have always had, so you can continue to work in the traditional way.

While it may make life slightly easier for the power user, it makes life potentially more difficult for the Access developer. But then, as I said before, I have always been a reactionary.

Problem unsolved
So, what’s wrong with multivalue fields? In a nutshell, everything. If you want a rule-based, technical answer, they are forbidden by Codd’s Rule 2 – the guaranteed access rule: each and every datum (atomic value) in a relational database is guaranteed to be logically accessible by resorting to a combination of table name, primary key value and column name.

This is impossible if the column can contain multiple values. If you want a more pragmatic answer, SQL stops working in the way it’s supposed to function. For example:
SELECT Sale.Items
FROM Sale
WHERE Items=“Herring”;

What should this return? Two rows (1 and 3) because both of those Sales included Herring, or none because none were for Herring alone?

In one sense, the behaviour is still predictable (it will return zero rows), but to find a sale that includes Herring, the multivalue field forces us to write SQL in a different way.

It is difficult to stress the problems caused by multivalue fields too s trongly: the relational model is predicated on the notion that each field can only contain a single value. Changing that is a serious step. It breaks the model. Once that is done, the beast is loose: the falcon can’t hear the falconer; this is the end of days.

So, anyone who does it had better have a good reason for doing so.