Step #2: Brainstorm fields
After you have determined data entities, you need to do the nitty-gritty work of brainstorming the fields for each data entity. A field is a category of information. For example, “First Name” is a field. When you’re looking at your data in a table, a field is the same thing as a column.
It is highly recommended that you brainstorm fields with a group of other people. Everybody who is going to use the database (even people who are only going to use printed reports from the database and never actually do any data entry) should be a part of this process. Your co-workers will think of things you might miss.
Ask the staff to think about the reports they wish they could print. What data is on those reports? Any field you would like printed on a report must be stored in the database.
How many fields do you think you need just to store a contact’s name?
Perhaps you would answer that you need two: First Name and Last Name.
But think about the things you want the database to be able to print. If you want the database to print formal letters (“Dear Mr. Joe Schmoe…”) then you’ll need more fields. If you only have two fields and you later determine that you want to store a prefix (“Mr.” or “Mrs.”) for each contact, then you’ll end up storing the prefix in the First Name field, which will mess up other things in the database (like your ability to put your contacts in proper alphabetical order or print a report that contains just first and last names).
Most databases need five fields just for name: Prefix (“Mr.”, “Mrs.”, “Dr.”, etc), First Name, Middle Name/Initial, Last Name, Suffix (“Jr.”, “M.D.”, “Ph.D.”, etc).
Some databases need even more than that: Nickname, Name On Envelopes, Formal Saluation, Informal Salutation.
Here are some other questions to ask yourself that will help you brainstorm fields:
What phone numbers do you need to keep for each person in the database? For example:
• Phone, Fax
• Home Phone, Work Phone, Fax
• Day Phone, Evening Phone, Fax
• Home Phone, Work Phone, Cell Phone, Home Fax, Work Fax
• Unlimited (each contact can have an unlimited quantity of phone numbers, and the type for each phone number may be specific by the user… Direct Work Line, ISDN, Telex, Pager, etc)
Do you need to keep a single address for each person? Two addresses? An unlimited number of addresses? If you want the ability to store more than one address for each contact, which address will be used on labels?
Will all contacts have United States only addresses, or will there ever be international addresses? (International addresses require looser data validation on postal codes, and require there to be a Country field in the database.)
Would it be helpful to know the Gender and Birthdate for each contact? Birthdate can be used to calculate a contact’s age and to print a list of contact birthdays each month.
Do you need webpage and email for each contact? Do you need to store more than one email address per person?
Do you need to store information about a contact’s spouse?
“Notes” fields can be really helpful. Users can type any information they want in a notes field. Vital information, especially information that you would like to see printed in columns on reports, should not be stored in notes fields. Instead notes should be used to supplement other fields. For example, you may store a contact’s address in the Street, City, State and Zip fields, but then also have an “Address Notes” field in which you can type things like “This is a temporary address. After Mar 11th this address will no longer be valid.” What notes fields would be helpful for your database?
Particularly for contact databases it may be a good idea to have a “Categories” tab (or something to that effect) on the main form. This tab would be full of checkboxes for all the different categories in which you might wish to place a contact. Spend time brainstorming these categories. The payoff is that you’ll have more querying power on your database later. Example categories: ReceivesNewsletter, IsDonor, IsOnBoardOfDirectors, IsVendor.
Once you’ve created a list of your fields, move on to step #3.