Pre- Design Issues

Tuesday May 11th 1999 by Christopher Shaw

This set of Questions that I ask myself before I sit down to design a database. This includes what I look for in an interview process with the clients of the database.

The Dreaded Billing Database

Recently, I was asked to set up a database to keep track of the billing information
for the company that I work for. After I started to think about it for awhile, I
realized that I am not a bookkeeper nor an accountant. I know nothing about billing
systems, what they require, what information they need to provide, nor do I know the way
that the information needs to be formatted.

So, I sat down with the controller and asked her what information they need me to keep
track of. This is when I realized that she did not know a thing about databases. She knew
nothing about tables, indexes, or DRI and I knew nothing about aging reports or accounts
receivable. I was left with a big question. How was I going to provide what they needed
without knowing accounting.

Instead of becoming a bookkeeper or making my bookkeeper into a DBA, I tried to fill in
the gaps by asking questions, or going through an interview process. The first step that
I was going to have to take, was to meet with, or interview, the people that would be using
the system. This interview would include watching them work and getting an idea of how I
could make their life a little bit easier, by saving them time and money. In some cases I
would even want to do a little of their work for them. Maybe cut down a little on data
entry, maybe produce a few reports.

After I had done this I came to this conclusion. I needed to approach this as if I was a
journalist. I needed to know the answers of Who, Where, What, When, Why and How. This may
not work for all circumstances, but it works well in mine. It bridges the gap between two
different people. By figuring out the Who, Where, What, When, Why and how’s I had a
better understanding of the reports that they needed. I also had a better understanding of
the accounting cycle and when they needed their info, things like when the invoices needed
to be sent so that they could make it on this months books. Knowing these small things
helped me create a more valuable database for them.

The first Question that I ask is who.
Who is going to use this? Is this going to be used by a few people in one department or
many departments company wide? Is it going to be used by data entry clerks, CEO’s, or
both. (Be sure to plan for the future. I have found that once I show a company how useful
the database can be, it usually incorporates many other tasks like inventory or employee
information). What is the level of the User? Is this user going to be a person that is
knowledgeable in the SQL language to the point where they can write there own queries to
get their reports? Or, are these people going to need an extensive help file so they can
start the program? Who is going to run the reports that may be needed from the database?
Is it going to be the Administrator running reports on demand or will it create procedures
on the fly when the user needs them, can it be a stored procedure that will run every night?
There are many questions that need to be answered when it comes to Who and these are just a few.

In my scenario, here are the conclusions that I came to.

The bookkeeper and the CFO were going to use this also, quite a few data entry people.
Neither the bookkeeper or the CFO are very computer savvy, but neither of them are going
to need help turning the computer on, either. The data entry people will need some
additional training, but a help file could easily be written to ease the learning curve.
So basically, the information will have to be presented in a fashion that is easy to learn
and use due to the variety of user skills. The reports will have to be generated on the fly
for the CFO and bookkeeper, so they can have them on demand. They also need to be versatile
enough so that the reports could be built with peramaters from the user on the fly.

With this set questions answered, I listed all my facts on a white board and I came up with
30 different people that would or could be using this system. The most helpful information
that I learned from these questions being answered was that there were going to be a longer
interview process than originally though.

Second Question- Where.
This question covers a couple things. Where are the users. Are they located in the same
office that you are or in another state? Are the users connected via VPN (Virtual Private
Network)? Are they connected to the Internet? Are they working from home or in the office?
Are they on a laptop or a server? Will a sales person need to access this information in
the field. If so, how can they get the recently updated data or update the database, for
that matter, if they add info in the field? In my case, I had to look at a few different
groups of people. The first group consists of the accounting department and the second,
the data entry department.

The CFO and the Bookkeeper are both located in the same building as I am. They both are
on the same network and can use a simple ODBC connection to access the database. This is a
huge advantage. I can set them up to use any combinations of programs for them Access,
Crystal Reports, or Visual Basic to name a few. (I don’t recommend any one over the
others, although I use Access as a front end for many of my apps). My data entry people,
however, limit what I can do. I have some people 30 miles south and others 1000 miles north. The
users that are 30 miles south are on a VPN, but the ones that are north of me are not. I
still could use a reporting programs, but I am not going to need to (the data entry people
will not need to see the reports). So, I can set them up a sercured page over the Internet,
although, Security will be an even bigger issue now. Everyone, though, will be using this
system either at home or in the office on a desktop machine. No one will need to use this
in the field. The environment is very stable and, chances are, there will be few changes
to this app.

What? What does this system need to do?
This is fairly simple and most of this information is going to come from interviews. If
you have the time, have the person you are interviewing show you how they do their job.
Have them go over it until you have a good understanding as to why this system will help
them and why they need this. Remember, a long interview can be priceless when it comes to
learning what the user wants and needs.

A good portion of this question is going to be answered by design questions after you have
all the interviews completed. Here is where the designer needs to have great listening
skills. I always ask questions along the lines of what colors they like and what is easy
on their eyes. I have found that some users place a lot of importance on the colors and
other cosmetc attributes of the appl. I lets them feel that they play a bigger part in
the process.

After I had finished up with my interviews I came to the following conclusion. I had a
lot of reports with not a lot of data entry. (My database was about billing and mostly
included invoicing and aging reports). I also found need to keep track of dates. Lots of
dates. Date due, date client was first invoiced, date of second or third invoice, if any,
as well as, the date of a payment or cancellation. Knowing about all this date tracking
helps me with the aging report, in the long run. I, of course, had to keep track of who the
company was. (The name, address and phone number information.) I also needed to knoe what
they were being billed for and the cost of these service. Another thing that I picked up
in the interview, was that the prices change, depending on the ad that the client may have
infront of them. So there was going to be a need for tracking extensive price changes too.
The reports that I need included an invoice, an aging, and just a list of who has paid and
who has cancelled their order. Reports that tells totals.

It is normally the easiest question to get answered in the interview, but can be a
difficult question for the DBA to work around. When is this data is going to be used? Is
it going to be up 24/7 or is it only going to be used during normal working hours? If it
is going to only be used 8 to 5, then that leaves lots of time for maintenance, backups and
such. But if its going to be used 24/7 then you are going to have to find time or make time
to do this maintenance. If you are fortunate enough to be working on a SQL Server 7.0, then
back ups won’t cause any major problems. Although I have never used it myself, I have
seen demonstrations that prove that performance will not be an issue, with SQL Server 7.0.

The situation that I found myself in was this, I have a SQL Server 6.5 database with an
6 am to 9 pm shop. The good news to this, was that I had some time for maintenance. I would
start my maintenance process at 10 pm with a DBCC check DB, then the back up. This process
will leave me with ample time for the database to be ready at 6 am. I did, however, need to
find time to run some stored procedures that are very processor intensive and would almost
render the database useless because of the speed. To solve this I had the weekends.

I find this question helps me the most, and, when answered correctly, can be the most
satisfying part of the process. Let me explain. Why do the users need the database? Why
do the users want this database? If this question is answered well, then it will save the
user time and money. Smart work on a designer’s part, saves a lot of hard work on the
user side. The users and the management could have a lot to thank you for with this one.

When I first was told about the billing database they wanted me to design, I was told
there was some double data entry occuring on the users end. My original database design
provided only the services that the company was selling. The database also provided a means
of providing administration to the clients that we have. The database did not, however,
keep track of clients that needed to be billed nor did it create invoices. To compensate
for this the users used a separate billing program that did this for them. The problem with
this is that the users had to input all the client information again. By incorporating
billing with our current clients list, it removes the need for double data entry. In my
scenario, the question of why would be to save time, work, and money. Another problem we
had with double data entry, is that it gives the user a greater chance of mistake. It looks
really bad when you bill a client and his named is misspelled.

I use the how question to tie up all the loose ends.
When I answer the question of how, I then start to put the database components together in
my mind. I then expand my thoughts using a data model tool and a huge white board. I lay
out the tables and then test my design with as many combinations as possible. Then I return
to the same people that I originally interviewed and give them examples of what it would
look like and how it would act. I make sure that this design will meet the needs of the
users. With all additional questions answered, I have an even better understanding of what
the users need and want. I also have a better understanding of what would be the best way
to approach the design process and the programs I should use while doing it. With all
these questions being answered, the hardest part is over.

Mobile Site | Full Site