OfficeTipsAndMethods

Posts Tagged ‘Table Design’

>

In the last installment we explored the many to many relationships that exist between our various table. At that point we had not yet created the tables. This article will deal with the nuts and bolts of actually creating a table.

A word about names

The names you use when you create your database objects are important and should follow certain guidelines. Access reserves certain words for its own use. Logically these words are known as reserved words. Unfortunately some of these words are the first that comes to mind when you are trying to name fields for example. In this article I discussed at a conceptual lever, the tables we need in the Ice Cream Parlour database. If you look back to that article you will see names like ‘Name”, “Description”, and “Type”. These words all have special meaning within Access and should not be used to name Access objects.

So you will notice that the field names in the relationship diagrams in my last article are different from those I used in the conceptual diagrams. So, for example I have a field ‘strSundaeName’ rather than simply ‘Name’. What about the ‘str.’ That part of the name comes from a convention I follow to add a prefix to the name that indicates the datatype of the field. Many Access developers do not use prefixes for field names. So SundaeName would be perfectly acceptable and, in fact, preferred by many developers.

On the other hand, many developers do follow a naming convention that prefixes the names of Access objects like tables, queries, forms and reports, with three characters to signify the type of object to which the name pertains. So, for example, the Sundaes table is actually called ‘tblSundaes’.

Notice also that tables are usually plural. That is because the table contains records where each record is one example (or instance) of the subject of the table. So, tblSundaes has fields with names like strSundaeName (or SundaeName, if you prefer.)

Creating a table

There are several ways to create a table in Access. Although I don’t recommend it, you can design a table by entering data into a datasheet. Access will determine the data type for each field based on the data you enter. And unless you are careful to name each field, Access will assign such helpful field names as Field1, Field2, etc.

In Access versions prior to 2007 there are table creation wizards and in 2007 there are table templates to help you out. Unfortunately, tables created by wizards or from Access 2007 templates have field names that do not follow accepted naming standards. They include spaces in the field names. Now, while this may seem perfectly natural to you if you are an Access beginner, those spaces will come back to haunt you when you decide to enhance your work using advanced features like Visual Basic for Applications (VBA).

I prefer to create all tables using the Table Design View. Before we go there, however, we should map out the fields we will need and determine what their respective data types should be. For the sake of illustration I am going to deal with creating one of the tables we have decided we need. If you are following along and creating your own ice cream parlour database, the best way for you to learn will be for you to create the other tables on your own. I will be providing a link to my version of the database with all tables created.

Before we look at the table designer, let’s summarize the information we will need for each table. I am indicating two possible names for each field. The first name follows the naming convention I prefer to use. The second is a valid name in a format that omits the data type prefix. Whichever style you choose, it is important to be consistent throughout the database.

Sundaes – tblSundaes

Dishes – tblDishes

Sizes – tblSizes

Flavors – tblFlavors

Ingredients – tblIngredients

Those are the basic definitions of our five central table. As already noted, we will need additional tables in the database to allow us to connect these central tables together so that the database reflects the one to many relationships between them. We will also need a lookup table for types the type descriptions used by tblDishes, tblSizes, and tblIngredients.

Coming next: Using the table designer.

>

Just as a real ice cream parlor needs tables where our patrons can sit and enjoy out products, our database model of ice cream sundaes will need tables in which to store information about our products.

In the previous installment of this series, we fleshed out the list of tables database will need. These are our main tables:

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

Because we will be combining ingredients in various ways with various container sizes and ingredients actually come in several flavors, we will need some additional tables to help us model the combinations. These tables will include:

• sundae ingredients
• ingredient flavors
• sundae sizes
• container sizes
• sundae containers

Now it is time to determine what fields to include in each table. You may be starting to find this list just a little overwhelming. Try not to panic. It is really not all that bad as long as you tackle things a little bit at a time. So we will move ahead, slowly but confidently, one table at a time. For now, let’s focus on the five central tables mentioned above.

Table definition refers to identifying the fields that are needed to describe the subject (or domain) of each table. So when I think about a table, I start to think about the kinds of information I need to describe the subject of the table.

We will keep these basic guidelines in mind:

• each table describes only a single subject (entity)
• the fields included in each table should describe the subject of the table (attributes).
• data will be subdivided across multiple tables in order to
  • avoid duplication of data, and
  • avoid repeating groups of columns

I find that, in a database as focused on a single business solution as the ice cream sundae database is, a single table emerges as the central table at the heart of the application. Other tables in the database are needed only because they will have information that pertains to the central table.

So, for us, the central table appears to be the sundaes table. Let’s start there. What information do I need to store to describe each sundae? Obviously each sundae needs a name. It will also need ingredients and, since I plan to offer each sundae type in a variety of sizes, it will need size information.

However, storing size and ingredient data in the main sundaes table will lead to immediate design problems. While I might be certain ahead of time exactly how many sizes I will be offering and the maximum number of flavors might be used in a sundae, including that information directly in the sundaes table will break one of our basic guidelines. I would have to have fields like flavor1, flavor2, flavor3, ingredient1, ingredient2, ingredient3. In database terms these would be examples of repeating columns.

So, what’s the problem? Although not apparent at the outset of planning, repeating columns make retrieving useful information from stored data, unnecessarily complex. Even more important, repeating columns put serious limits on the future use of the database. No matter how certain you are ahead of time that you are describing how something is now and will ever remain the same, it is just not that easy to predict the future. If I "know" that I will only ever offer three sizes of sundae, small, medium, and large, the database (if I have included three size fields in the sundaes table) would not be able to handle changes I might have to make in order to remain competitive. Say, for example, that upstart competitor down the street starts stealing my customers because she has added a fourth or fifth size.

I have tried to describe the process by which I have determined what fields belong in the sundaes table. The process for deciding what fields to include each of the other main tables is the same. The following table shows the results of this analysis:

You may find that the list of fields in each table is somewhat short. Each table includes a name field. The sundaes and flavors tables don’t have any other fields. That’s because most of how we describe sundaes, for example, is stored in other tables. Flavor names, on the other hand are pretty much self-descriptive.

The dishes, sizes and ingredients tables have type fields. We will use these fields to categorize dishes, sizes and ingredients, respectively. Dishes may be either edible, disposable (plastic for takeout) or reusable (glass or metal for eat-in.) Sizes may refer, for example to quantity when referring to ingredients, to capacity when referring to dishes, or to the end-product size description (small, medium, large, for example) when referring to sundaes. Ingredients may be principal ingredients, toppings, or garnishes, for example.

Now we have a working definition of our main tables. In the next article we will define the additional tables we will need to flesh out the connections needed between the main tables.

>

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.

>

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.

>

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.