Argee's Access Blog

As a first article in my Access blog, what better place to start than at what should be the beginning step of any Access database design: understanding your data?

You might ask, with all the data I have to work with, how can I possibly 'know' it all? I have thousands of customers. How can I know the details about every one of them?

Let's start then, with understanding what the challenge is and what it is not. The challenge to 'Know your Data!' is in no way a suggestion that you memorize the details of all the data you plan to manage with Access. On the other hand, it is very much about understanding the kinds of data with which you are planning to work.

Kinds of data? The beginning of understanding data starts with recognizing that things (the 'stuff' of data) come in different flavors, if I may be permitted a food analogy. For example, you might have chocolate ice cream, vanilla ice cream, strawberry ice cream, and many, many more variations on the theme. It should be fairly easy to recognize that all of these concoctions have at least one thing in common. They are all forms of ice cream. The name gives away an underlying commonality.

Unfortunately, the problem is not quite as simply solved as that. 'Things' that are variations of the same theme do not always share similar names. Forgive me for continuing the food analogy a bit more. When you sit down to eat, your meal may consist of meat, potatoes, corn, squash, and, of course, ice cream for dessert. Are each of these things you are about to eat essentially different or do they possibly have something in common? They look and taste differently from each other.

Certainly, one's sense of taste would have to be impaired to confuse ice cream and potatoes. Think for a minute, however. Aren't ice cream and potatoes just different types of foods? Nutritionists would say that they belong to different food groups. That definition immediately underscores the point I am trying to make that potatoes, ice cream and the many other things that people eat are all simply various types of food.

Let's expand the analogy beyond foods for a minute. When we eat, many of us sit on a chair, in front of a table. After eating we may go to another room and sit in a more comfortable chair or sofa and ultimately go to sleep in a bed. Hmmm, tables, chairs, sofas, and beds. At first glance, they don't appear to have much in common. It is pretty difficult to confuse a bed with a chair, but aren't they just different types of furniture?

By now, you may be asking, "What does all this talk of food and furniture have to do with understanding my data?" That is a fair question whose answer is also quite simple. Understanding your data has everything to do with recognizing things that belong to the same category and things that do not.

You may even be asking, and I hope you are, "What does recognizing that potatoes and ice cream are both foods have to do with designing a database?" If you are talking about a relational database, the kind Access is designed to work with, the understanding and recognition of things that are similar is a vital part of the design process.

It is this 'recognition of things that are similar' that helps you determine what tables you require. It is a fundamental principle of relational database design that things that are are similar belong in the same table. In other words, if you were designing a database for an ice cream parlour, you would not have one table for each type of ice cream; you would have one table where each record would represent one flavor of ice cream.

Similarly, if you are developing a database for a furniture store, you would not have a sofas table, a chairs table, a tables table, and so on. You would have a single furniture table. That table would be a list of each possible type of thing that belongs to the category of 'things' known as 'furniture.'

The usefulness of an Access database directly depends on the care and thought you put into designing it. Get the design right and your database will be a useful tool for managing your business. It will help you keep your business on track, isolate problems, and recognize opportunities for growth and improvement. Get the design wrong and your database will at best be an on-going source of problems, not a key tool in their solution, a flawed tool, at worst a totally useless waste of time and money.

In the next article, I will discuss the second aspect of the initial database design phase, distinguishing things from values that describe things.