Hands on: Database values

What happens if we upsize?
Well, one of the main features of a client-server system is that the database is held centrally, so that different people can see and interact with the data simultaneously.

A normal primary key is all that we need. The very act of moving to a client-server database has solved the problem, so we no longer need the GUIDs. Since GUIDs are complex, unwieldy and downright ugly, we should remove them from the database as we upsize.

On the other hand, suppose that some of these people are working deep in a jungle far from any connection.

It may be impossible for them all to be connected to the server at the same time, in which case we may need to keep the GUIDs. So keeping or replacing the GUIDs is your call, not that of the upsizing tool, which is why upsizing can never be fully automated.

And this example also illustrates the earlier point that the structure of the database is sometimes defined by looking at a problem (in this case, disparate locations for entering data) and the features offered by that particular database engine (not client-server, but does have GUIDs).

When we upsize, at the very least we need to re-examine the problem and, given our knowledge of the new database engine, see if we can solve the problem a better way.

A dead default
Another issue that affects the upsizing process is that default behaviour can vary between engines. In a way similar to that described above, if we happen to accept the default behaviour in an engine, almost without us being aware of it, it becomes part of the database behaviour itself. When we upsize to a different engine with a different default behaviour, the results can be completely unexpected. I came across a good example recently.

A friend upsized his Access database to SQL Server and retained the Access front end. After upsizing he could see and edit all the existing rows (the ones created in Access before upsizing). He could create new rows in the Access front end and these would happily post back to SQL Server. They didn’t look any different from the original rows; however he found that once posted, he couldn’t edit those new rows, even though he could still edit the original ones. So he ended up with two classes of row in the table - editable and cannot be edited.

When he did try to edit one of the new rows, the error message told him that someone else had changed it in the meantime. This is clearly a load of old nonsense, because it happened even if he was the only user on the machine.

The reason for this odd behaviour centres on the defaults in Yes/No fields. Access allows only two values in a Yes/No field - Yes and No. No is the default, whereas SQL Server allows three - Yes/No/Null and the default value is Null.