Argee's Access Blog

After an unintentional hiatus, I'm back with part three of this discussion of Access database design considerations.

The first two articles (Know Your Data!) discussed planning for the data you need your database to manage. The third essential component in knowing your data, is understanding how data in the various tables of the database relate to data in other tables (that's why we call it a relational database.)

However, it's a bit premature to talk about relationships. First we need to have a simple database example and an understanding of the tables it requires. Once we have an understanding of the tables, we can then look at their relationships.

I'm going to build this example around a simple single purpose database but, regardless of the size and complexity of the database the same basic design principles apply .

Let's say, for the sake of discussion that I own an ice cream shop.I have decided that I want to use a database to help me design ice cream sundaes that will show me the kinds and quantities of ice cream and other ingredients each particular sundae type requires. This information will help me determine how much of each to stock when I start to sell each sundae.

The first step in designing any database is to devise a simple yet clear 'statement of purpose' for it. As you build the database keeping the statement of purpose in mind will keep you on track so that the end product will do everything it was supposed to do while, at the same time, does not just grow beyond recognition to do things you did not originally intend. In other words, the statement of purpose helps you focus your work and keep it 'in scope.'

I like to play a little trick with language when I write a statement of purpose by stating it as if the database already exists. So the statement of purpose for my ice cream sundae database might go something like this: This database stores information about the quantity of contents used to make ice cream sundaes. It assists with the design of new sundaes and provides reports of the ingredients required to make each type of sundae.

Now I can use that simple statement of purpose to begin to decide exactly what kind of data I will need to include in the database. Here's a simple 'brain dump' of what goes to make up an ice cream sundae:

While thinking about this list, I realize that I need to include the possibility of having various sizes of sundaes. Since a database is really just a model of the real world, I will need to include sizes in the basic information about each sundae I design. Even though I intend to have a number of sizes, I realize that to keep sundae management simple, I should limit the number of sizes available to three or four.

I'm now at the third stage of my design where I need to decide on what tables I need. Since I want to restrict the variety of sizes I sell, a table that describes each size might be useful. A table of dishes or containers might also be useful. And what about a table for ice creams and one for toppings.

Now that just might be one table too many. Aren't ice creams and topping just different ingredients? So let's just have a single table for ingredients. Here is my initial table list:

• ingredients
• dishes
• sizes

One important table is missing however. That is the sundaes table that brings together all of the information that describes an ice cream sundae, our final product. So we will add one more table:

• sundaes

In the next article, we refine the database design by determining the relationships between the tables.