Learn to think relationally

By Allen Browne, Access MVP

A very common mistake is to create heaps of Yes/No fields in a table to store people's preferences. This article explains how and why you should use a relational design instead.

A sports teacher might set up a matrix to record students' interest in various sports like this:

Student	Basketball	Football	Baseball	Tennis
Josh	X	X
Mark		X		X
Mary-Anne				X
Olivier
Trevor	X	X	X

If the teacher knows nothing about databases, he will create a table with a Text field (for the student name) and a bunch of Yes/No fields so he can tick the sports the student enrolls in.

Paper forms are laid out like that, so lots of people make the mistake of building database tables like that too - the diseases a patient has had, the newsletters a client wants, the classes someone will attend, the answers to a survey - all built as tables with many check boxes.

Do not build a table like that! A Yes/No field for each possible choice is not the way to build a database.

Thinking relationally

A major problem with these repeating Yes/No fields is that you must redesign your database every time you add a new choice. To add Netball, the teacher must create another Yes/No field in the table. Then he must modify his queries, forms, reports, and any code or macros that handle these fields. A relational design would avoid that maintenance nightmare.

Thinking relationally, we have two things to consider: students, and sports. One student can be in many sports. One sport can have many students. Therefore we have a many-to-many relation between students and sports.

A many-to-many is resolved by using three tables:

• Student table (one record per student), with fields:

  • StudentID (AutoNumber)

  • Surname (Text)

  • FirstName (Text)

• Sport table (one record per sport), with fields:

  • SportID (AutoNumber)

  • Sport (Text)

• StudentSport table (one record per preference) with fields:

  • StudentSportID (AutoNumber)

  • StudentID Number (Relates to Student.StudentID)

  • SportID Number (Relates to Sport.SportID)

The third table holds the preferences. If Josh is interested in two sports, he has two records in the StudentSport table.

This relational structure copes with any number of sports, without needing to redesign your tables. Just add a new record to the Sport table, and the database works without changing all queries, forms, reports, macros, and code.

You can also create much more powerful queries: there is only one field you need to examine to search for the sports that match a student (i.e. the SportID field in the StudentSport table.)

Interfacing this design

Create a form bound to the Student table. It has a subform bound to the StudentSport table. The subform has a combo for selecting the sport, and you add as many rows as you need for that student's sports.

When you add a new sport to the Sport table, it turns up in the combo box automatically. You can therefore choose it without needing any changes.

Taking it further

The same principle applies to many types of database, e.g.:

• patients and diseases (sample below)

• clients and newsletters

• people and preferences

Allen Browne is a Most Valued Professional (MVP) for Access. Mr. Browne is the owner of Allen Browne's Database and Training, and he lives in Perth, Australia.