One of my customers is an employment agency that places job candidates with clients in the financial industry. When the agency approached me about making its database application (a networked Access 2000 database with seven users) more usable, one of the problems was a very common one: how to solve a many-to-many relationship and make it easy to display and update the resulting data.
The existing database had been created by a summer co-op student years before. The student had obviously known some basic database design principles but had stumbled a bit in the execution, especially of the user interface. I'll describe a solution that involves existing Access controls, some SQL, and some simple DAO and VBA coding.
A many-to-many example
Among the many skills, licenses, and certifications that each of the agency's job candidates possessed was the ability to speak one or more languages. When deciding how to store this information about the candidates in the database, a consultant might ask the customer: "How many languages does a candidate usually speak?" Knowing that one language field in the Candidate table is not going to be enough (there is always a default, such as English), one might be tempted to create a series of fields such as Lang1, Lang2, and Lang3 in the Candidate table.
This approach is flawed for a number of practical reasons beyond just the rules of good database design. What about the candidate that speaks four or even five languages? What about all the other similar lists of fields needed for things like licenses and certifications? The resulting table would be an unwieldy monster with dozens of fields, many of which would remain empty for most candidates. Efficient searches—a very important and frequent task at an employment agency—would be virtually impossible.
Languages spoken, an important (and likely a multivalued) attribute for a job candidate, deserves elevation to entity status in this database design. The original creator of my customer's database had realized this and created a separate table for languages (along with tables for skills, licenses, and certifications, among others). The problem then became that, if each candidate can speak many languages and each language can be spoken by many candidates, there is a many-to-many relationship between the two tables. This is something that can exist at a theoretical level while coming up with a database design, but something that cannot exist at the physical level. The solution, as the database's creator knew, is a joining table, as shown in Figure A.
The joining table—tblCandidateLanguage—solves the many-to-many relationship problem between the Candidate table and the Language table. It contains only key columns; that is, the two fields, CandidateID and Language, combine to make up the primary key, and each is a foreign key relating back to the primary keys in the other tables. Now, the fact that a Candidate speaks any number of languages can be easily recorded with no wasted space and no duplication.
I started the cleanup of the original design by making sure integrity was enforced in both relationships with cascade deletes in both relationships and cascade updates between tblLanguage and tblCandidateLanguage for ease of maintenance. (Without referential integrity and cascade deletes of the "child" records in tblCandidateLanguage, there were many "orphaned" records for candidates that had been deleted from tblCandidate, leaving nonexistent CandidateID numbers behind.)
Sample data for tblCandidateLanguage is shown in Figure B.
Viewing the data, it is easy to deduce that Candidate 1 speaks English and French; Candidate 2 speaks English only; and Candidate 3 speaks English, Italian, and Spanish. Any number of languages can be added to tblLanguage and assigned to any number of candidates. It also becomes very simple to search for a list of candidates that speak a particular language. Searches run efficiently because the fields being searched are keys and are therefore indexed.
So far, this is a simple exercise in Database 101. As any good consultant knows from experience, storing data efficiently is just the beginning of application design. Presenting the data to the end user in an understandable way that also allows simple and intuitive updating is a much greater challenge.
Displaying the data
As a consultant, my job was to make the existing application more user-friendly. After cleaning up the existing data and ensuring that it would stay clean, I set about coming up with another way to display information, such as all the languages a candidate speaks. The existing application used a pop-up dialog box to display a candidate's languages in a continuous form that looked much like the datasheet view in Figure B.
Users added a new language by adding a row, entering the CandidateID manually, and then picking the language from a drop-down list. Users that got that far often didn't understand that their new entry was not saved until they moved the cursor to another row.
There had to be a better way, especially to do the updates. I considered a subform on the main Candidate form and also a list box to display current languages spoken for each candidate, but finally settled on the very simple design shown in Figure C.
The controls added are as follows:
- · An unbound List Box called lstLanguagesSpoken (the list on the left)
- · An unbound List Box called lstAvailableLanguages (the list on the right)
- · A command button called cmdAddLanguage (the upper button)
- · A command button called cmdDeleteLanguage (the lower button)
Both command buttons are disabled.
The first step was to fill the list boxes in code using a procedure called RequeryLanguageLists. The code for this procedure is shown in Listing A.
The code fills the unbound list boxes using a SQL string instead of a table or query. The first box displays the list of languages spoken by the candidate whose record is currently displayed on the form. The second box displays the languages from tblLanguage that do not appear in the first list box. Both tables referenced are compact and fast, and all the fields referenced are indexed keys.
This procedure is called automatically for each new record displayed from the Current event of the form, as shown in Listing B.
So when the record for Candidate 2 was displayed on the form, the list boxes would display the languages spoken on the left and the languages still available on the right, as shown in Figure D.
Updating information about a candidate
Let's say that a user next wanted to update the information for Candidate 2 to show that he or she also speaks Spanish. The user would click on Spanish in the Available Languages list. When the user selects the language to be added, the code in Listing C runs, changing the appearance of the screen to that shown in Figure E.
Enabling the top button, which points to the Languages Spoken list box on the left, indicates to the users that there is now something they can do with the language Spanish that they have selected in the Available Languages list box on the right. When the user clicks on the button, the code in Listing D runs, adding a new record to tblCandidateLanguage and changing the appearance of the screen to that shown in Figure F.
When the RequeryLanguageLists procedure runs again when called from the cmdAddLanguage_Click procedure, the record that was added to tblCandidateLanguages shows up in the list on the left. Since the list on the right displays only records from tblLanguage that do not appear in tblCandidateLanguage for the current candidate, the effect that the user sees is that they have "moved" the selected language from one box to the other. An additional benefit is that because languages currently spoken by a candidate are removed from the list of available languages, adding the same language twice for one candidate—a primary key violation in tblCandidateLanguage—is impossible.
Deleting a language spoken by a candidate works in much the same way.If a user wanted to correct the information for Candidate 3 to reflect the fact that the person did not, indeed, speak Italian, the user would click on Italian in the list of Languages Spoken for Candidate 3. The code in Listing E would run and change the appearance of the form to that in Figure G.
Once again, the user gets a visual clue; when the button pointing to the right-hand box becomes enabled, the user can do something with the language highlighted in the left-hand box. When the user clicks on the button, the code in Listing F runs and the form appears as shown in Figure H.
When the code finishes its job, the screen is returned to its "resting" mode, displaying the languages spoken by the candidate and the list of available languages left to choose from.
Increasing the usability of existing applications
This example uses Microsoft Access, but the basic principles span all relational databases and event-driven programming languages. The same or better results could be achieved using a variety of other tools—VB, ADO, T-SQL, OCXs—and more sophisticated methods.
The simple improvements described here went a long way toward extending the life of a reasonably sound but flawed application. Since this initial minor rework, I have done a variety of small projects for the same customer, integrating a couple of databases and eliminating a lot of paper along the way.
For consultants, the Holy Grail is often the project that we can design and control from the ground up, using our favorite technologies, for a cooperative client with unlimited resources and patience. The reality is that we often must be able to quickly analyze existing applications and correct their faults to win and retain new clients.