Microsoft Access Tutorial - Defining the Scope of your Database (part 2)

In this Microsoft Access Tutorial, we will be finalising the theoretical structure of our fictitious database. At the end of this tutorial, we will have a complete structure in place. In my next post we will be (finally) opening up Microsoft Access to begin building our tables and the relationships between them.

These tutorials are very linear in nature, and I urge any first time visitors who are interested in learning how to develop a database in Microsoft Access, to go back to the first post and work your way forward. I have provided a listing of all the tutorials so far in the sidebar on the right to make this easier for you. Don't forget, if you have any questions, I will be more than happy to answer them along the way. You can post a comment at the end of each tutorial, or email me at microsoftaccesstutorials@gmail.com.

So, in our last tutorial we started defining the scope of our database. We went for a virtual tour through the Bobbles 'R' Us store, and asked Bob, the manager, questions along the way. At the end of that tutorial we had three entities (Bobbles, Customers and Staff) and one interaction (Customer Notes) listed, along with attributes for each item. After working your way through this tour, you should have a fair idea of the process behind creating these lists, so I'm not going to bore you with this process any more. At the end of our interview with Bob, the new entities and interactions we have found are as follows...

Sales
Customer
Staff
Date
Total Amount Payable
Payment Method

The Sales table is actually a three way interaction between customer, staff and product (bobble). However, a sale may consist of more than one bobble, which leaves us with a similar problem that we had when creating our Customer Notes table. The solution is also the same, we split the table into two, one that stores the general information about the sale (above), and the details of each sale item, as follows...

Sale Details
Bobble
QTY
Price
Discount
Sale Price

Then we have Suppliers and purchasing information that Bob also wants to track in the database...

Suppliers
Supplier Name
Street Number
Street Name
Suburb
State
Zip Code (Postcode)
Country
Phone
Email
Website

Purchases
Supplier
Date
Invoice #

The setup for the Purchases is similar to the Sales table, as it will probably be multiple items (bobbles) that are being delivered to the store. So once again, we split this table in two

Purchase Details
Bobble
QTY
Price
Discount
Purchase Price

Now we have a complete list of entities and interactions for our database. To recap, they are...

Bobbles
Customers
Staff
Customer Notes
Sales
Sales Details
Suppliers
Purchases
Purchase Details

The next step is to graphically represent the relationships between each of these items. You can do this "old school" by writing each item on a separate piece of paper, and then shuffling them around on your desk until they look just right, or you can use a multitude of computer software and online tools to create the same effect. The diagram shown below was created using a great free online tool called bubbl.us. The end result is called an "entity relationship daigram", and in high end databases, these can become quite a tangled web of links. In our situation, the end result is fairly simplistic...



The direction the arrows point is important in this diagram, as they specify "One to Many" relationships. For example, a customer may have more than one sale, but each sale only relates to the one customer. In some circumstances you may come across a "Many to Many" relationship. For example, the link between Suppliers and Bobbles. If we are to record a list of Bobbles that each Supplier supplies, the direct relationship between Bobbles and Suppliers would be "Many to Many". That is, a supplier supplies more than one type of bobble, and each type of bobble can be supplied by more than one supplier. To fix this issue, we create a linking table called "Supplied Bobbles". This table simply stores a link to the Supplier and a link to the Bobble, like this...



We now have a complete understanding of the structure of our database, and we are going to leave Bob and his store, go back to our computer, and start creating our table structures based around this information, in our next Microsoft Access Tutorial. Stay tuned.

Microsoft Access Tutorial - Defining the Scope of your Database

If you have been following these Microsoft Access Tutorials, you should now know how to create a database file, and have a broad understanding of good database design as it relates to table structure within Microsoft Access. We are now going to look at the theoretical design of the database that we will be working with throughout the rest of this tutorial.

Bobbles 'R' Us is the name of our fictitious company, and as you may have guessed from their name, they primarily deal in Bobbles. What are Bobbles, I hear you ask? Well, for the purposes of this tutorial, it doesn't really matter, you can imagine them however you wish, but suffice it to say they are in hot demand right now, and Bobbles 'R' Us desperately needs a database to track their business dealings.

So we set up an interview with Bob, the general manager, to start figuring out what the scope of this database will be. This is one of the most important stages of database development, and if you are serious about creating a usable application that your client / end user will be pleased with, then you need to put in the face time up front to determine what your client needs are.

The first thing you should do is get the person (or people) you are interviewing to walk you through their business, physically if possible, while you make a list of all the entities and interactions you can see (if you don't understand what I mean by entities and interactions, you need to read my post on Database Design - Table Structure). For each item on this list, you need to also list all the attributes you can find relating to that item. A good way to do this is to pretend you are explaining this item to someone who has never seen it or heard of it before.

So Bob takes us for a walk through the Bobbles 'R' Us store, and we come across a shelf full of Bobbles. You write "Bobbles" on your list. You notice that not all these Bobbles are the same, there are blue ones, red ones, green ones and yellow ones. You also notice that they vary in size and shape as well. This shelf actually has many many different types of Bobbles, there are big yellow square ones, little red triangular ones, medium sized blue round ones, and more. They also seem to range in price. So, next to the entity Bobble on your list, you write "Size", "Shape", "Color" and "Price". These are all attributes of a Bobble that seem to vary from one to the next. Then you turn to Bob and say "Bob, these Bobbles are different sizes, shapes, colors and prices. Is there anything else that makes these Bobbles different from one to the next?" and Bob says "Yes, the ones on the top shelf are made from a stronger type of plastic than the ones below, each one has it's own serial number, and some of them come from different suppliers". So you write "Material", "Serial #" and "Supplier" as other attributes for these Bobbles.

Now, I hope you don't think I'm being too patronizing in explaining these attributes to you, it may seem straight forward, but the more information you can gather at the beginning of your database development, the easier your job is going to be in the long term. The aim of the game is to break everything down into the smallest parts possible.

So after looking at the shelf full of Bobbles, we end up with the following list...

Bobbles

Size
Shape
Color
Price
Material
Serial #
Supplier

The next entity we notice is a customer. To describe a customer to someone who has never seen one before could be quite a task. We have eye color, shoe size, marital status, musical preferences, age and on and on. But it's fairly obvious that a lot of this information is not going to end up in our database, we need to ask Bob exactly what he wants to track about his customers. "Well" he says "we really want to be able to send newsletters out to them, so for that we would need an address". At this point I mention that once your customers are in a database, you could also email the newsletters to them, saving on postage and printing costs. "I also want to be able to phone them to let them know when their order has come in, and I want to keep a track of any communications my staff have had with them in the past, is that possible?" We assure him that it is, and we end up with the following list...

Customer

First Name
Surname
Address
Email
Phone
Notes

Keep in mind that these attributes need to be broken down as small as possible. In my last post, I mentioned that address is actually made up of several parts. We also have an issue with the notes field, as Bob wants a historical record of what was said, when it was said, and who said it, which means multiple notes for each customer, date and time stamped. In this situation, what do we do? We could say that there would never be any more than 100 communications with a customer, and have it set out like this...

Date 1
Staff 1
Note 1
Date 2
Staff 2
Note 2
Date 3
Staff 3
Note 3

...and so on up to 100, but that would be extremely messy. The proper way to handle this is to regard your notes as a separate interaction between staff and customers. So our modified Customer entity now looks like this...

Customer

First Name
Surname
Street Number
Street Name
Suburb
State
Zip Code (Postcode)
Country
Email
Phone

and we also have a new interaction that looks like this...

Notes

Staff
Customer
Date
Note

That's probably enough for you to digest today. We will continue our tour through the shop with Bob next time. I know that this may seem tedious, but I cannot stress enough how important it is that you get this right before dirtying your hands in Microsoft Access. It makes it so much easier to physically design a good database if your brain has a handle on the big picture. As a database developer, you will have to learn the ins and outs of each business you design a system for, it's an interesting process and it keeps you on your toes. Stay tuned, the next Microsoft Access Tutorial will not be far away.

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.

Microsoft Access Tutorial – Database Objects

In our last Microsoft Access Tutorial, we created a database file, and had a quick look at the database window, with the types of objects listed down the left hand side. Today, we are going to find out what these objects are, and the roles they play in your database.

Tables – Tables are the foundation of your database. All the data that is stored in and retrieved from your database will be stored in tables. Each table should relate to one “entity” or type of information within your database. For example, you may have a “Customers” table, or an “Appointments” table, or a “Books” table. Each table is made up of multiple fields that describe your entity; for example, “Customer Name”, “Appointment Date”, or “Book ISBN”. It is imperative when building your database, that you get your table structure right. We will be spending the next few lessons discussing just how this is done.

Queries – A query is a way of looking at the data that is stored in your table. For example, you could create a query that would find all the customers who live in Paris or all the appointments in March. These are called “Select Queries”. There is another type of query, called an “Action Query”. Action queries modify the data in your tables. For instance, they can delete particular records, add new records or update product prices. A “record”, by the way, is one row of information (one customer, one appointment).

Forms – A form is the window that you look at when entering information. A form can have text boxes, check boxes, drop down boxes, buttons, and many more elements. It is, in short, the interface to your data. What the end users of your database see are a collection of forms that are linked to the data in your tables.

Reports – This is self explanatory, a printable view of your data, with customisable headers and footers.

Pages – For the purposes of this tutorial, we will not be touching on Pages. Pages are a way of viewing your data in HTML view (like a web page), and can be used to access your data online.

Macros – A macro is a series of automated functions. For example, you could create a macro that prompts for a date range, and then prints out a report according to the criteria selected.

Modules – As Tables are the foundations of your data, Modules are the foundation of your interface. Microsoft Access (and the rest of the MS Office suite of programs), uses a programming language called “Visual Basic for Applications”, or VBA for short. With knowledge of VBA, you can make your database application do pretty much anything you want it to. We will be touching on VBA throughout these tutorials, and providing resources for further learning down the track.

That’s it for today, over the next couple of tutorials, we will be talking about database design and how to create the foundation of your database. There is still a bit of theory to go through before we start getting our hands dirty, but I’ll try and make it is short and concise as possible. Stay tuned, the next Microsoft Access Tutorial will be coming soon.