Lesson 2 - How to design the database application

To illustrate how to use Access in a commercial application we'll use a business that almost everyone is somewhat familiar with: the local video rental store.

Let's say that the store, Mike's Video, is going to open for business in a few weeks and the owner, Mike, wants to have a database application ready to go for opening day.

You, the Analyst, will sit down with Mike and you will question him on what he wants to get from the computer application. Then you will draw the plans for the application, which we call the model, and you will check with him again to make sure you haven't forgotten anything. Only then will you actually start to write the application in Access.



Why does Mike want a database in the first place?

There are actually two main reasons.

1. This is rather obvious: he is going to be renting thousands of movies to thousands of customers. There has to be a system in place to track who has what movie, when it was rented, when it was returned, if it was late, if it was lost, who to call to get it back, etc.
   
   
2. To succeed in business you have to analyze your business: Who are your customers - men? women? old? young? What are they renting? What's selling and what isn't? What do you have on the shelves that is gathering dust? What are they asking for?
   
   

So, a well-designed database application will meet both those requirements. It will do the routine sales management and, it will allow the user to do all the sales analysis he needs to do to make the business prosper.

You have to keep both of those basic needs in mind when you work on the design.

This is the part where we identify what has to be done to make the application perform all the commercial functions it has to have.

First, a word of warning: Since you're just beginning with this, we'll keep the exercice simple. We know that there are many functions that you can do in the video store: rent DVDs, rent VHS movies, rent games, buy previously-viewed movies, buy popcorn, chips and cola, rent machines, etc. We won't cover all of those. Which is what you should be doing when you do it for real: design the core application and get it working then, add other functions to it.
Our core application is to track the rentals of movies, DVDs and games. We'll leave the popcorn and Pepsi for another session.

The first thing you will discover is that there are 2 entities that you are working with. An entity is something you keep data on, an object that acts on other objects.

In this application they are: Customers and Movies. We'll use the term "Movies" to describe our products even if they are DVDs or games or whatever.

Now, take out your pencil and paper and make a list of all the data, we call them fields, that you have to keep for each entity. You should get something like this:

       


Why do you need the customer's DOB? If you want to analyze your sales by age you have to have it. Also, maybe you'll want to send your best customers a card on their birthday. It would be a nice touch!

Same with sex. You need it to analyze by gender and maybe orient your publicity towards certain target groups.

This assumes that you can get the information.

When you ask the customer originally to fill-in a membership form you will ask for that information. If the customer refuses to give it, that's OK. You don't make a federal case out of it. But 95% of people will give you what you ask for on the form.

For the movies, you keep what might be important if it's not too much trouble to obtain it.

Maybe customers will want to know what movies starring "Tom Cruise" you have. Or Spielberg movies. Maybe there is a Film Studies program at a college nearby and they will want to know which Hitchcock movies from the 1960s you carry.

The idea of Sales Analysis is that if you know your customers, you know their needs and you give them what they want, they'll come back as customers. The more customers you have, the more money you make. Simple, isn't it!

So, the database is designed to store as much description of the customer and of the product as possible. We can then use that information to build customer profiles and to track daily or weekly or monthly sales and identify patterns. As soon as something is starting to go off-track, the manager can take corrective action.

This is just the beginning in your career path to bigger and better databases.

Right now on the market there are databases containing many terabytes of information (a terabyte = 1,000 gigs). There are applications called Data warehousing and Data mining that dig through those mountains of data looking for shopping trends, customer buying patterns, etc. This is going to be BIG!




Now we look at what we should put in so that the application works as smoothly as possible with the computer - speed, error-checking, flexibility for future growth, standard design practices.




Here are the things that you have to identify:

1. The primary key for each table: the field that will identify each individual in a unique way. Customers: First name? No - there are thousands with the same First name. Same with Last Name. And City, and State, etc. Phone number might be a candidate but, you may have 2 memberships in one family with the same phone.
   
   So we'll create a new field. We'll assign a unique ID to each customer when he registers. Since the ID doesn't have to be anything in particular we'll make it autonumber, meaning that the first customer will automatically get ID=1, the second will be ID=2, etc.
   
   Then we do the same for Movies.
   
   The prime directive for database design: Every table must have a primary key.
   
   
2. Lists: any field that contains a limited list of items. Obviously not First name or Last name. But State contains a list of 50 items.
   
   Identifying it as a List will help us down the line. It will avoid errors: with a list you select from the list instead of keying-in the state; if you want Maine, you select "ME" and you don't risk entering "MA" by mistake.
   
   And, if items are added to the list, it can be done easily. That probably won't happen too often with State (maybe Canada, eh!) but it will be more frequent with other fields.
   
   Other List fields will include: Sex, Credit card name, Movie Category, Rating, Language.
   
   The rule is: any field that can be a list, should be.
   
   
3. Default values: the most common value for a field. If you know that 70% of customers are from Springfield, make the default for City = "Springfield". When you come to enter City for the customer, 70% of the time you will just do Enter to accept the default value. It helps cut down on mistakes.
   
   
4. Naming convention: a standard format for field names in your application. For example, I use the first letter of the table name as a prefix for field name: c_custID for customer ID, c_Fname for customer first name.
   
   The reason is that eventually, when you get into many tables, you will run into duplicate field names. If you create a Supplier table, the Supplier may also have a First name. When you program the application it will be a lot easier if you can tell one from another at a glance: c_Fname will be Customer_First_name and s_Fname will be Supplier_First_name.
   
   Here, all Customers fields start with c_ and all Movies fields start with m_.

Next week: Creating the database. See you then!

---