How to database "tick all that apply" fields?

[pompeyjon]

Hi all,

Hope someone can offer a suggestion with this one. I have been tasked with designing a database from a paper-based form. However, the paper-form has not been designed with databases in mind.

Specifically, I have one question on the form as follows:

Tick all that apply (then lists 15 choices)

Now I don't want to create 15 separate fields for the 15 possible ticks. A colleague has suggested storing the possible combinations of ticks as a binary number, but seeing as how an admin person will be doing the data entry, I don't think this is feasible.

Also, I would rather avoid normalising unecessarily - the design of the form lends itself to a flat-file structure rather than relational tables.

I guess someone will say "store it in a spreadsheet" and to be honest that may well be the best solution, but I thought I'd canvass opinion first.

Regards,

Jon Moore

 

[willir]

Hello Jon

First, it would be to your advantage if you read some background material on databases. A common mistake when designing a database is to design it like a spreadsheet, or make it idetical to a "paper" system. Here are some references...

Fundamentals of Relational Database Design
Download document
Read on-line (HTML)

Micro$oft's answer...
283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database

...Moving on
The use of a boolean yes/no field is common type of data field when designing a table. But you have to be careful when using them to make sure your design is adequately "normalized".

Say you have a Contact database, and you need to flag a contact as "Active" or "InActive" -- perfect time to use a boolean yes/no or true/false field called "Active".

Now let's say you want to show that the contact is a member of various groups, say a non-profit organization. A common approach would to have the groups listed...
- Voluteer - yes/no
- BoardMember - yes/no
- FinanceCommittee - yes/no

In a "normalized" database, this is not the best way to design the database. If you think about it, the above design would become a nightmare to manage. Everytime a new group was created, you would have to add the field to the table design, add proper control fields to the forms and reports (and make sure the RecordSource was correct), and update your queries.... This design also makes it real tough to run various day-to-day operations such as sending out a mail-merge.

A better, "normalized" design would be to have three tables -- one for contact, one for "groups" and the third "joiner" table depicting what groups a contact belongs to. Now if you need a new group added, you just add a record to the groups table and create record in the "joiner" table for contacts that belong to the group.

...Moving on
You indicated "tick all that apply"....
- When you design a table, you can create a default value. This is a great way to ease up on the data entry.
- How will computer or the data-entry person know "all that apply". "Smarts" can be built into a form using some programming, but there has to be some guidelines to make it possible to deduce what is "checked-off"

Richard