Microsoft Access Tutorial - Database Design - Table Structure

In this Microsoft Access Tutorial, we are going to discuss good database design as it relates to table structure. Good database design is a many faceted beast, and later on in the tutorials, we will be talking about interface design, naming conventions, and other areas relevant to database design, but to start with, the structure of your tables is the foundation upon which you will build the rest of your database, so it is important to get this part right.

There are a lot of difficult terms that you may have heard thrown around by database theorists, like First Normal Form, Normalization, and Boyce-Codd Normal Form, which tend to scare the beginner off. The overall concept of good database design can be boiled down to these three simple rules...

One Table, One Entity (or Interaction)

One Field, One Attribute

Only Enter Data Once

One Table, One Entity (or Interaction)

Let's take a walk through your hypothetical shop. We walk through the front door and we see all different types of products for sale on the shelves. We see Judy behind the counter, and three or four customers walking around. Behind us, the Coca Cola guy has just come in to restock your fridge. As we walk in, a customer walks up the register and puts her items on the counter. When we talk about entities, we are talking about individual things in your shop. Customer is an entity, Salesperson (Judy) is an entity, Supplier (the Coca Cola man) is an entity, and Product is as well. These are all tangible entities, you can see and feel them (although that may not make the customers very happy).

Those things that you can't see or feel are interactions between entities. Everything in your shop is interlinked in some way. The Customer walked up to the Salesperson and put her Products on the counter. There are three different entities interacting with each other here, and the interaction is called a Sale. The Coca Cola man has finished stocking your fridge, and gives Judy an invoice. This interaction is called a Purchase.



For each of these entities and interactions, a separate table should be created within your Microsoft Access Database.

One Field, One Attribute

Each Entity and Interaction is made up of multiple attributes. Take your customer for example, it's easy enough to call him customer, but for good customer relations, and future sales possibilities, you are going to want more information than that. The sort of information you may want to record for this customer is First Name, Last Name, Address, Age, Phone Number, Email, etc. All this information gives you a better idea of who this customer is. Now let's say you've stored the data I just listed above in a Customer table for 1,000 customers. Now, you want to send a newsletter to all the customers in Harrisville. The problem is, you have stored the customers address in a single field, and as you look through the table, you notice that the field holds street numbers, street names, suburbs, postcode and states, making it time consuming and tedious to find all the customers in Harrisville.



When you set up a field in Microsoft Access, you need to imagine what you will be typing into that field in the future. Another example would be in the products table. Say you sell whitegoods in your shop, and your products table has Product Name, Model Number, Description and Sales Price. After a while, you start noticing entries in the description field like "Stainless Steel, 400 Litre, Freezer on top, 4 star energy rating". Now, when you want to find all your 400 litre fridges, it's going to be difficult, because you haven't provided a separate field for that attribute, and it's ended up being dumped in the description field.

You need to break each entity or interaction down into the smallest pieces possible

Only Enter Data Once

This links back to the One Table, One Entity rule, but really is an explanation of how everything links together. Imagine you are recording a sale. You don't want to be entering the customers name, address phone number, etc, everytime the customer comes in and buys something. You already have that information in the Customer table. Instead, you want to link the customer table to the sales table. This link is called a Relationship.

Each entity and interaction should have something that uniquely identifies it from the others of it's kind. For example, you may have a customer named Bob Smith, but his name itself is not unique (there are plenty of Bob Smiths in the world). He lives in Harrisville, but even that doesn't uniquely identify him, as there may be another Bob Smith in Harrisville. To take it to the extreme, Bob Smith has a son who is also called Bob Smith, and they both live in the same house and therefore have the same address and phone number. How are we going to uniquely identify them within our database? Well, the easiest way is to create a number (a Customer ID) that is automatically assigned to each customer by the database itself. Within Microsoft Access, this is called an Autonumber, and because it uniquely identifies the customer, it is also called a "Primary Key".

Now, when you enter your sale into the database, the only thing you need to record in the sales table is the Customer ID, the database can then retrieve the rest of the information (Name, Address, etc) from the customer table. The Customer ID that is stored in the Sales table is called a "Foreign Key". It doesn't uniquely identify the table that it is in, but links back to a Primary Key in another table.



By creating Relationships throughout your database, you are linking all your entities and interactions together (as they are linked in real life), and minimizing the need to enter in information more than once.

This Microsoft Access Tutorial has touched on database design and normalization, and has given you the basis that you need to start desiging your database. If you would like to explore this topic in further depth, there is an excellent explanation of the 3 Normal Forms at phlonx.com, and another great resource at databasedesign-resource.com called Database Normalization Explained.

Stay tuned to Microsoft Access Tutorial, next time we will look at laying out the design of our database for our fictitious company, Bobbles 'R' Us, using the theory that we have learned here today, and then we will be looking at putting it all together within Microsoft Access. See you again soon.