Lesson 2 - How to design the database application
The "Video Store" applicationTo 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.
Defining the application
Why does Mike want a database in the first place?
There are actually two main reasons.
You have to keep both of those basic needs in mind when you work on the design.
Commercial requirements 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!
Technical requirements design
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:
Next week: Creating the database. See you then!