Argee's Access Blog

In the last article we looked at the relationships that exist between the various  products we will be using to create ice cream sundaes. These 'real world' relationships dictate the relationships we will need to establish between the tables of the database.

In this article we are going to look at what information we should be including in each table. Before doing that however, there is at least one more table that we should include in the database, flavors. Since each flavor can apply to many ice creams and some ice creams have multiple flavors, the relationship between ingredients and flavors is many to many. It is possible that some other ingredients may be multi-flavored as well but one multi-flavored ingredient is enough to make the relationship many to many.

It's important to define the relationships before finalizing table design because one to many and many to many relationships require different table structures. For a one to many relationship, you include one field in the 'many' table to identify the 'one' table record to which each 'many' record belongs. Many to many relationship require this information to be stored in an separate table with one field for each of the partner tables.

Guess what!! We have just have just identified the need for several additional tables in our 'simple' ice cream sundae database. We will need one table for each of the many to many relationships. One reason for doing detailed planning and design before actually beginning to build the actual database is to try to uncover and resolve any possible data management problems and to ensure that all the database will include all information necessary to fulfill the database's stated purpose.

In Know Your Data (Part 3) we defined the purpose of our ice cream sundae database:

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.

We have also defined a list of tables that we will need to manage the data:

• ingredients
• dishes (containers)
• sizes
• sundaes
• flavors

Now it is time to decide what data needs to be included in each table. Before we do that we should review some terms and principles required for good relational table design.

• Each table should contain information about only one topic or subject (formally known as an entity.
• All examples of a topic should be included in one table (if you have are dealing with people and occupations, you would have one people table, not separate tables for each occupational group, for example.)
• Each table has one or more fields that describe the subjects or topics stored in the table.
• Each table should have a primary key that uniquely identifies each record in the table. (I am going to leave for another time the discussion of the alternative types of primary keys.)
• A record  is the collection of fields that describe one of the topics of the table.
• Data should be stored only once.

There is a set of rules known as the forms of normalization that are the guiding principles for relational database design. If you a new to database design, your initial efforts will most likely run afoul of normalization so don't be surprised when the response to questions you may ask an experienced developer about designing forms or reports refers you back to normalization issues in your basic database design.

In case you haven't noticed, we have yet to do anything with Access itself. That is because knowing how to use Access is only part of the story in building a successful database for personal or small enterprise use. We will be using Access to build and run the database but we can't build it until we have a solid design.

In the next article we will (finally) design our the tables that we will have Access manage for us.

In this simple example, we have been developing a database to assist with the design of ice cream sundaes.

In the last article in this series, we developed the statement of purpose for the database:

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.

We also determined that we will need at least four tables if the database is to be properly structured:

• ingredients
• dishes (containers)
• sizes
• sundaes

I am going to save deciding the contents of each of these tables for the next article and focus now on how the tables relate to each other. (After all, we want this to be a relational database.)

The process to determine the type of relationship that exists between any two tables is quite straight-forward and perhaps a bit tedious. Try not to let the tediousness of the task of getting in the way of doing it. Correctly defining the database's relationships is absolutely essential!!

Unlike real-world relationships, in Access there are always exactly two partners in any relationship. You define each relationship by considering one pair of tables. By filling in the blanks in two simple statements, you can make a simple conclusion about the type of the relationship.

So we are going to look at pairs of tables until we have determined what the relationship is between each possible pair of tables using these three statements.

• Each record in table A can have (zero/one/many) records in table B
• Each record in table B can have (zero/one/many) records in table A
• Therefore this is a (one to many/one to one/many to many) relationship.

So here are the pairs of tables and their relationships.

With four tables there are six possible relationships but not all tables will share a relationship.

sundaes <---> dishes

(Just to be clear, the dishes table stores information about types of dishes so the relationship definition deals with how each sundae will relate to dish types.)

• Each sundae can have one dish
• Each dish can have many sundaes
• Therefore this is a one to many relationship

sundaes <---> sizes

• Each sundae can have many sizes
• Each size can have many sundaes
• Therefore this is a many to many relationship

sundaes <---> ingredients

• Each sundae can have many ingredients
• Each ingredient can be used in many sundaes
• Therefore this is a many to many relationship

ingredients <---> dishes

• Each ingredient can have no dishes
• Each dish can have no ingredients
• Therefore there is no relationship between ingredients and dishes

ingredients <---> sizes

• Each ingredient has no records in the sizes table
• Each size has no records in the ingredients table
• Therefore there is no relationship between ingredients and sizes

dishes <---> sizes

• Each dish has one size
• Each size could be applied to many dishes
• Therefore there is a one to many between sizes and dishes.

Remember that when you are describing relationships, you are not defining the relationship. A relationship is what it is; calling it by another name will not change its essential nature. Your job is to identify accurately the essential nature of each relationship in the database.

A couple of things have come to light in this relationship analysis. First, sizes are used in two somewhat different contexts. Sundae sizes refer to the size (small, medium, large) in which the sundae will be marketed. Dish sizes on the other hand refer to the capacity of the dish, how large a sundae it can comfortably contain. The two uses may or may not similar descriptions. A sundae might be described as small medium or large. So might a dish but dishes could alternatively be described in terms of units of measure (grams, ounces, etc.)

So it might be worthwhile thinking about an additional table to store the possible units of measure (metrics.) Whether this would be necessary or merely a possibly good idea would depend in part on how many possible units of measures apply.

Including a metrics table requires further relationship analysis. Before we can describe the relationship between sundaes and metrics in particular, it is important to be clear what constitutes a sundae. So far it looks as if we have defined a sundae as the combination of ingredients and a particular container. But there is one more factor that distinguishes one sundae from another: size.

So a banana split consists of vanilla, chocolate, and strawberry ice cream, banana, chocolate syrup, crushed pineapple, walnuts, whipped cream, and cherries, regardless of whether the sundae is small medium or large. For data management purposes, a small banana split is different from a medium or large banana split and it is the size that distinguishes them from each other.

metrics <---> sundaes

• Each metric applies to many sundaes
• Each sundae has one metric
• Therefore this is a one to many relationship

metrics <---> ingredients

• Each ingredient has many metrics
• Each metric apples to many ingredients
• Therefore this is a many to many relationship

metrics <---> dishes

• Each metric applies to many dishes
• Each dish has one metric
• Therefore this is a one to many relationship

metrics <---> sizes

• Each metric applies to many sizes
• Each size has one metric
• Therefore this is a one to many relationship

Now, you might be asking how can a metric apply to many dishes. That is a perfectly legitimate question simply because I haven't fully defined what I mean by a dish.

For this definition, let's look to the real world as it applies to ice cream shop. We have already seen that dishes come in several sizes. In my shop, I intend to have both edible and inedible dishes for each of the sizes. Perhaps, in the future, I might have several types of edible dishes (waffle or chocolate, for example.) It is the combination of size and dish type that distinguishes one small dish from another.

Raising that point means that we must think about yet another set of relationships. I'm going to leave that decision aside for the moment.

Next time we will take a look at table definitions, what fields each of our tables need.