Argee's Access Blog

In my last article, I discussed the idea of recognizing 'things' that are similar (and therefore require their own specific table. The second aspect of database design involves distinguishing things from values that describe things. This part of the analysis helps you decide what fields each table will require.

After you have made extensive lists of what you want and need to include in your database, the basic question to ask about each item  one your lists is, "Is this a thing, or the description of a thing?" Just to keep the whole process interesting, many of the descriptions will have their own lists of values which (can you guess?) belong in their own table.

Back to the ice cream, meat, and potatoes analogy, food groups are one of the characteristics that can be used to describe foods. Food groups are categories to which each food belongs. I am not a nutritionist or dietitian so at this point I am assuming that each particular food belongs to a specific food group and that no food can belong to more than one food group.

If I were designing an actual database of course I would have to verify with a food expert or other research whether my assumption is correct. Is there any food that belongs to more than one food group? The answer to that question is at the heart of the next stage of design, determining the relationships between tables.

Staying with determining the values that describe things for the moment (technically these values are known as 'attributes' or 'properties'), clearly understanding and grouping these descriptions is one of the key points in making your database as powerful and flexible as you want and need it to be.

For example, if the furniture table described included the name of each type of furniture in the store, you would only be able to search for tables, chairs, sofas, beds, etc. Adding fields to the furniture table that describe each furniture piece will make the database a far more useful tool in the business.

So you might include fields like typical room assignment, color, price range, style, and so on. Keep in mind that this understanding is vital to your coming up with a solid design and a database that will stand the test of time.

At this point, you might be asking yourself, 'If I need all this information to know my data, where and how do I learn it?' Sure, you may have some general knowledge about the subject of the database is supposed to be dealing with, but how do you ever get to know enough so you can get on with building the database?

I gave a clue to how you go about it a few paragraphs back. Ask the experts. The people for whom you are creating the database most likely do not understand data structuring (that's your job and should be your expertise) they do understand their business and what they want to do with the data associated with the business.

Whether you are working as an independent database developer or are creating databases as part of your day to day employment, the person or people who requested the database or who will be working with it after you build it are your clients. Recognize that your clients are the subject matter experts for the database. They know the 'what', what the data is about and what they want to do with the data.

Your job as the database designer is to come up with the 'how.' How can all this data be best organized so that mountains of raw data can be turned into gems of information that will help your clients manage and grow their business. So look to you clients for the 'what' but do not let them dictate the 'how.'

Data in its raw state is essentially useless. Distilling and refining data by organizing it into a database is a key step in turning raw data into a wealth of business information. Understanding the data opens the door to getting it organized.

The next article will discuss the third aspect in understanding your data from a relational point of view: determining the relationships.

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.