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.