Finally, unless you’re creating the database yourself, you need to communicate the specification to the developer.

You will meet with the developer at least once to discuss the specification. Be prepared to modify the specification, as the meeting may result in new insights on database structure and field choices. You may need follow-up meetings once development has begun in order to review and clarify the specification, and to make sure that development is on track.

If you start your database project with a clear and complete specification, you have a much better chance of getting a good product that really meets your needs. The only thing we haven’t covered here is data migration, but that’s a subject for another post!

There are a number of other considerations with new database projects…

Security

Do you want to restrict usage of the database? You can password protect a database to keep unwanted visitors from looking at your data.

You may need to go beyond simple password protection, however. Access has a feature called user level security. Each user logs into the database with their own user name and password (similar to the way you may have to log on to your network). Different users may be granted different access rights. Some staff may only be to read data, others may be able to enter new data but not delete anything, and others may be given full data access.

---

User Tracking

If you go with user level security, then you can also add another neat feature as well: usage tracking. You may print a report whenever you want that will reveal who has been using the database, what time of day a user logged in and out, and even which records a user modified. This can be extremely useful in discovering how employees interact with the database.

---

Network

Do you intend to put the database on your network? How many people do you want to be able to access the database from their computers? How many users do you expect will have the database open on their desktops simultaneously? (Microsoft Access has limitations on the number of concurrent users that may open a single database file. Once too many people open the database at the same time, performance will begin to suffer.)

---

Direct Access

Do you want the ability to work directly with database backend objects to create custom queries and reports on-the-fly? Some users like to have a “database play area”, where they can interact directly with data while still being sure they won’t accidentally change table designs.

---

Backups

If the database is placed on the network and the network is already backed up on a regular basis, then backing up the database is not a worry. However, are you sure backups are consistently done on schedule? Are backup tapes taken offsite?

Backup procedures may be in place at your organization, but frequently it so happens that backup procedures are not followed on a daily basis as they should be. Performing backups can be time consuming and monotonous, and busy employees may let backups slide in favor of more pressing tasks.  Even when backups are done on schedule, it sometimes happens that backups are not done properly and the errors are never discovered because backup checks were not established as a part of the backup procedure.

Even if your network is being backed up in a reliable way, it is still worthwhile to occasionally make backups of your own. Each backup is a snapshot of your data at a particular point in time. Sometimes data is changed and it is later discovered that the changes were erroneous. Having backups allows you to rewind the clock on the data.

If it seems desirable, a backup system may be included with the database. The database can even warn you when it’s time to make a new backup.

---

Now you’re ready for step #6, the final step…

In step #4, you get specific with your reports.

Think about what output you want from the system. Consult other staff to hear what reports they might find useful.

As you brainstorm reports, write down exactly which fields should go on a given report. Unless you truly don’t care what a report looks like, spend a moment to sketch it out on paper. If you create a sample page of a report manually in Microsoft Word, the consultant will have a very clear idea of what you want and will be able to design the report more quickly in Access, and you’ll be more satisfied with the end result.

Most clients need their databases to print mailing labels. What size labels do you use? (Get the brand and ID number for your labels from the front of a box. Example: Avery 5162).

New reports may always be added later without too much trouble as long as all the required fields are already in the database.

Now you’re ready to move on to step #5.

In step #3, you sketch out how you want your forms to look. You will view and enter data on the computer using forms. Generally you have one form per data entity, although in some cases you might need multiple forms for one entity.

The term “form” is a bit misleading, because often times people use that word to mean something printed on a piece of paper. We use form to mean an on-screen tool used for data entry. Forms are how data gets into the database.

The general design of forms and the layout of fields upon a form may be left up to the consultant, but you have some choices to make.

A common way of designing a form that must contain many fields is to use tabs across the top of the form. Click on a tab and a new page of fields pops up on the screen. You should decide what tabs your form should have, and which fields should go with each tab.

You may also have some special needs for your forms. For example, perhaps a certain staff worker does a great deal of data entry using printed forms that are filled out by hand (like questionnaires). It might be helpful to create a special data-entry form on the computer for this employee that corresponds to the printed form.

Draw your forms on paper. Then move on to step #4.

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.

If you’re tasked with helping design a new database for your organization, it’s important that you create a good specification. If you’re working with an outside developer, the specification you’ve created will be the blueprint for the end product.

This post is the first in a series on how to design a specification.

Step #1: Think about your data entities

What is a “data entity”?

It’s the type of person or thing that your database tracks. For example, if you work for a hospital then the data entity for your database is probably patients. If you work for a non-profit organization, the data entity may be members.

Now ask yourself this: Is the data entity a person, organization, or thing? Here are examples of each type:

PERSON
• Members
• Clients
• Staff
• Patients
• Students
• Customers
• Donors

ORGANIZATION
• Corporate Contributors
• Restaurants
• Clubhouses
• Youth Organizations
• Homeless Shelters

THING
• Books
• Videos
• Equipment

Most databases will have a primary data entity. This is the one data entity that the database is geared to track first and foremost. A hospital database tracks patients, a donations database tracks donors, a contact database tracks contacts, and a grant management system tracks grants.

However, databases will often track other data entities as well. For example, the hospital database is meant to store information about patients, but it probably also stores some information about doctors. The donations database tracks donors, but it certainly also tracks their donations (donations are a different data entity than donors), and it may also need to store information about events. A school database may have many data entities: students, teachers, classes, enrollment.

Determine the data entities you wish your database to track. Then think about the relationships these data entities have with each other.

There are 3 types of relationships:
o One-to-one
o One-to-many
o Many-to-many

Donors and donations have a one-to-many relationship. One donor can give many donations.

Donors and Board-of-Directors (BOD) have a one-to-one relationship. One donor can hold a single position on the BOD.

Products and orders have a many-to-many relationship. One product may appear on several orders, and one order may contain several products.

Many databases will need to store information about both people and their organizations. If this is the case with your database, then you need to answer some important questions:

Which is the primary data entity of your database, people or organizations?

In many cases the primary data entity is people, but for each person in the database a field called “Organization” is used to store the name of their employer.

If it turns out that the primary data entity for your database is organizations, then the next thing you need to decide is what kind of contact information you need to keep about people within each organization. Does each organization have a single contact person, or do you need to store contact information for many employees within each organization?

Once you’ve answered these question, you’re ready to move on to step #2.

So you’re switching your company to new database software…

That’s great, but…

Your staff got used to working with your the old system – they were able to quickly and easily enter and extract data (not necessarily because it was a good system, but just because they knew it so well). When the new system is implemented, they will probably have trouble adjusting to it. They won’t be able to enter or extract data as easily at first, just because they don’t know how the new system works.

Provide training for staff on the new system. This will help them overcome “database shock”. Insist that they use only the new system. In time they will learn to love it.

If your organization is a nonprofit (and even if it’s not) you might wish to check out the website TechSoup.org to read some really great articles on databases and on technology in general. There you can read about the pros and cons of different kinds of databases, which may help you find the product that’s right for your organization.

SpringLight specializes in creating customized, user-friendly, form-driven databases in Microsoft Access, starting with a modular template that already contains many great features, and then tailoring it to your exact needs. This approach combines many of the benefits found in other kinds of database products.

But if you’d prefer to use a web-based or off-the-shelf product, SpringLight can help you choose the right software, set it up properly, and migrate your old data into the new system.