Date: Fri, 14 Aug 1992 04:08:19 GMT
Munged: news-gateway (is.morgan.com: $Revision: 1.8 $)
Newsgroups: rec.sport.disc
Organization: Morgan Stanley & Company, Inc.
Distribution: world
Subject: Re: DBMS key

>>...it sounds like the software that you are using may be causing 
>>a lot of this problem.  (Not that any other commercially 
>>available packages would be any better.)  It should not 
>>be too difficult to use any of the fields (or multiple fields) 
>>as keys.  Hence you should be able to...

Of course we'd be able to, and it's not a question of software.  We are
not trying to find simply a key that meets the minimum requirements for
being a key.  The issue is, in the real world, what is the most effective
way to serve our membership.  And the fact is, if a member were to be
able to say her UPA id number off the top of her head, an absolutely
unique, stable, and robust tag, it would be better.

You appear to assume a high degree of data integrity.  This is not
realistic.

Names and addresses change and are subject to misspellings.  This is how
people get lost in the database and don't receive their newsletters. For
example, on their first renewall, a duplicate entry is made with a
slightly different spelling; no ID was provided on the roster because the
member refused to give their SSN, the spokesperson filled it out wihtout
asking for it, etc.  On their second renewall, their original record is
updated, with an obsolete address.  They then call the 800 number and
leave their name complaining of the mixup, or refuse to pay at their next
tourney.   

We have over 7000 active members at any given time, and increasing. 
There are many cases of people with three or more records in the
database.  There are about 5 different spellings of Johnson, with
something like 60 records.  Picking out which exact records refer to an
individual involves several steps and a good dose of judgement.  Unique
names which are difficult to spell pose another problem, finding the
alternate spellings.  It takes an intelligent person lots of time to
figure out what has happened, when this is even possible.  Plus, many
members check paid when they are not, inadvertently or otherwise.

Consider what happens when a problem is found.  The member shows up on
screen, or on a list, as not paid.  The member and/or spokesperson must
be contacted to see if there's a mistake on their end, and then to
provide additional information to help clear up the problem.  This
typically involves several UPA people's time and the expense of phone
calls, faxes, letters, etc. both internally and to the member.

The UPA is a low overhead organization.  One person does data entry and
nobody checks it.  There is approximately one person (another one)
responsible for addressing all membership questions that arise.  The
headquarters receives around 40 calls per day.  Cindy puts in over 60
hours per week managing the office, of which a portion is phone response.

Consider an event with several hundred names to check, possibly on site,
and what a reduction of the number of problems (i.e. players who claim
current membership but don't show up as such in the database) by half or
more, and a reduction in the amount of time to figure out the answer
definitively to less than a minute, would mean in terms of time and
hassle saved. 

Quality data entry cannot be relied on for many reasons, including
illegible rosters/membership forms, time pressure, and human error.  We
do not have the resources to train data entry personnel or monitor them,
or to check rosters and follow up before they reach the data entry stage.

The quality of the database system is pivotal to the effectiveness of the
organization.  Some work up front will save many man hours, many dollars,
and many headaches. The UPA MIS project is a total revamping of the
database system, to include data integrity and ease of use features to
make it easier to put in data correctly, and flag you right away if you
don't.  We are intending a full on relational DBMS application with data
dictionary, flexible report generation, user interface, and maintainable
code.  That way it can evolve with the UPA and be useful for a long time.

Perhaps you can see that making the database key do a little more would
help things out significantly.  A numeric key is less subject to typos. 
An inherently unique key reduces the chances of confusing unrelated
records.  A stable key insures that the member will be trackable over
time, and increases the ease with which it is remembered.  A key that the
member can produce off the top of the head reduces the time to service
the member.  A simple key reduces data entry and record retrieval time. 
A key with embedded meaning (e.g. birthdate) can make it easier to spot
problems (e.g. can't have month > 12, which could be checked by the
system itself).

Come on, why do you think so many systems use SSN?  Because their
designers can't think of another key that meets the basic definition? 
Hardly.

These things add up to make the system robust, more functional more
generally and over longer time.  Better, in other words.  That's why I
asked.

-Frank



Distribution:
  >INTERNET:wolff@cache.crc.ricoh.com >INTERNET:wolff@cache.crc.ricoh.com
  The Net >INTERNET:ultimate-list@doe.carleton.ca
  Kevin Irlen >INTERNET:anita@world.std.com