Hands on: Solving a storage and usabililty problem

One-to-one relationships are relatively rarely implemented and they get little airtime, so I was pleased to receive a question from Wayne Sutherland that can be answered using this kind of relationship.

Wayne has a set of data about projects and he wants a user-friendly way of entering data. Each project has 50 pieces of information associated with it: a project has an ID, start and end dates and so on (Wayne’s data is more complex, but the principle is the same).

Normally we’d place all this data into a single table because it all pertains to a single project ‘object’ in the real world. (The complete database application, of course, would be likely to have multiple tables but here we’re solely concerned with one entity – a project – and its 50 attributes).

Why would we ever use more than one table for the project data? Imagine a situation where 30 of the fields contain core information that is collected about each project without fail. The remaining fields are rarely used.

Ten of them, for example, are for data about the project architect. Some projects require the services of an architect, even though most don’t. The other 10 fields are for external finance data; again most projects don’t need external finance but a few do.

These occasional eventualities are covered perfectly by the single table solution: the only problem is that it wastes space in the table. Can we devise a solution where storage is optimised without making life difficult for the users?

Yes, we can (I wouldn’t have started this otherwise…) We’ll begin with three simple sample tables like the ones shown in the screen, where the ID field in the Project, Architect and ExtFinance tables are of type Number.

The Default Value attribute needs to be blank for all three tables, rather than the default Default Value, which is zero. We then put in place two one-to-one joins using Access’ Relationships editor. Both joins come from the ProjectID field in the Projects table. One goes to Architect.ProjectID and the other ExtFinance.ProjectID.

As I noted earlier, one-to-one relationships are rare because the implication is that all the data could go into one table. If there were always an exact one-to-one relationship between two pieces of data, there would be no point in splitting the data into two tables.

What we have here, if we specify it more precisely, is a one-to-zero-or-one relationship. For each record in the Project table there can be one entry in the Architect table (or in the ExtFinance table) or none. All the records with no (zero) entries in those tables would be wasting space were the data to be held in a single table.

That’s the basic data structure: now we need to build an easy-to-use interface for users. First we need a query to pull together the complete data for each project. Start by creating a query and add the three tables. We want the answer table to contain data for every project, regardless of whether it has an entry in the other tables or not.

For this we can use a left outer join. Right-click on the join line between the Project and ExtFinance tables and select Join Properties. Choose the option that gives all records from the Project table and only those that match from the ExtFinance table. Use the same technique to turn the other join into a left outer join that includes all records from Project.

The SQL for the complete ProjectData query reads:

SELECT Project.ProjectID, Project.StartDate, Architect.Company, ExtFinance.TotalAmount
FROM (Project LEFT JOIN Architect ON Project.ProjectID = Architect.ProjectID) LEFT JOIN ExtFinance ON Project. ProjectID = ExtFinance.ProjectID;

Running the query lets you see all the project records with all permutations of project, architect and finance displayed.

Finally we need a form. It’s easiest to create an AutoForm: Columnar, based on the ProjectData query. Labels can be edited or added later to improve re adability. Check how ProjectDataForm is working by entering a new record. Enter an ID and a start date then post the record and check out the base tables.

A new record has appeared in the Project table but no entry has been made in the other tables. Return to the form, add an architect and post the change. The Architect table now has a new row, but the ExtFinance table still hasn’t changed. It will only do so once an entry has been added from the form.

Behind the scenes Access handles the data input very elegantly: if data is entered, Access creates the necessary records and if it isn’t, it doesn’t.