Wednesday, April 6, 2011

Things to do before you get building

I recently started doing some work with Filemaker Pro as a database development tool. For those of us who are "born and bred" Access developers, this is like turning to the dark side. However, software prejudices aside, I was struck by how the stuff you do before you get into the actual building of a database are very much the same. The logic of a well designed relational database goes deeper than the software used to develop it. So, what are some of these steps?

1. Think about the data: What things will the database be about? A good way to start is simply to write a list in good old English (or any other language you happen to be fluent in).

  • Watch for the nouns you use: Clients, employees, invoices, appointments. These are the data entities your database will incorporate.

  • Ask yourself what you want to know about these things. Sometimes these bits of information will be the attributes (aka fields) describing the data entity (e.g. name of client, date of invoice) but sometimes they may give rise to a new entity. A good example of this would be the payment of an invoice. If you simply want to indicate whether an invoice has been paid (a verb), you can simply attach an attribute to the entity "invoice" (which will probably become a "yes/no" field in your invoice table). But if there are things you want to know about THE PAYMENT (a noun), such as the amount, date, and method of payment, then the payment itself becomes a new data entity that will have, as one of its attributes, a reference to the invoice entity.

  • Create a simple Entity diagram: As you turn your narrative into a graphich representation, you will clarify the entitites involved and their relationships to each other. There are many free tools available for download to assist in this process.

2. What does the system need to do or produce?

  • Initially, try to limit this to the core functions: those things that are the main reasons for the database.

  • Look for any gaps in your data entity analysis. When thinking about a function that needs to take place, or a report that needs to be produced, we often discover that we have not properly included the necessary data in our design.

  • Start to envision what forms will need to be developed. Create a list of forms with key functions for each (Record retrieval, data addition/edit/deletion)

3. Consider the installation environment and constraints:

  • If this is to be an Access databasse, does the intended user have Access installed? If so, what version? If not, how many user licenses are needed? (Developers can also consider packaging a solution as a runtime version to avoid the need to purchase Access. However, this introduces other issues.)

  • Where will the database reside: on a file server or a computer?

  • Will it be shared? If so, by how many people and how will they connect to the host location?

  • Are all users the same? Will you need to restrict certain data or functions for different users?

  • How secure is the environment?

4. Develop a naming convention: This may seem superficial but having a logical way of naming your database objects will make it easier to follow the flow of the system and to make modifications later on. Although Access has gotten better at updating references to tables and queries if you change the name later on, problems still come up when you do this. Experienced users/developers probably have a system that works well for them. We have used some of the following conventions:

  • The names of tables that relate to data entities start with "T_" and names for those that are used to look-up values used in data entry begin with "LU_"

  • Don't leave spaces in the names of objects. For readability, use underscores or hyphens. This is more important if you intend to do programming in VBA.

  • Try to group related objects by using with similiar names; employee_bio, employee_addresses, employee_names.

As with building a house, working out the design concepts is the first step in assuring a successful outcome.