Provided by Allen Browne. Created: July 2007. Last update: August 2008.
A 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 Mark Mary-Anne Olivier Trevor
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 enrols 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.
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 the queries, forms, reports, and any code or macros that handle these fields. A relational design would avoid this 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:
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 the 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 to examine to find the sports that match a student (i.e. the SportID field in the StudentSport table.)
Create a form bound the 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.
The same principle applies to many types of database, e.g.:
Questionnaires are another area where this applies. Duane Hookom has a sample database called At Your Survey to illustrate now to handle those.
Suggestions for enhancing the usability of this kind of database:
For further reading see Normalizing Data (PDF 90kb) or Database Design 101 links.
|Home||Index of tips||Top|