My Summer Database Job: Using Your 540 ERDs and Conceptual Models in the
Real World
By Tom Rhoades
This summer I worked as a database consultant to a mid-sized local telecommunications
company. I was fortunate to be able to do something I always had an interest
in, as well as to apply some of the skills and knowledge I gained from
LIS 540 and courses from my undergrad days. While I had worked with databases
in my previous work experience before the iSchool, this job gave me the
most in-depth experience so far with databases and how they are used
in the real world work environment. While I do not consider myself a
full-fledged database ‘jockey’ quite yet, I did learn a lot
this past summer and hope to use this article as a way of advising others
who are in similar situations. I faced some of the following issues in
my work:
Political and Work Process Problems
As a consultant, you are often viewed by some in the organization as
an outsider who doesn’t fully understand the intricacies and context
of the company you are working for, especially by veterans with decades
of work experience. Yet this is what people in our field are trained
to do—as information professionals we can work pretty much in any
environment where there are people who need to find, organize or provide
information without needing a great deal of subject knowledge. While
I did learn a great deal about the telecommunications and wireless communications
field, most of my on-the-job education was terminology-based and devoted
to deciphering lists of acronyms, finding synonymous terms and/or pieces
of data, and learning basic concepts in wireless and telecom network
engineering.
Office politics often becomes an issue when trying to determine the
best person or group to talk to about a given meaning of a particular
term, or what to include or not to include when forming your database
design. In addition, some workers have fears of databases being things
that “don’t work for us, but make us work for the database”.
This is a perplexing issue because often such people do not realize or
accept that while some work may be needed in the short run to start a
database project, in the long run it will save time, increase efficiency,
convenience and provide useful sources of information – if designed
correctly.
Knowledge of Databases
There is somewhat of a gap between the “database people” and “non-database
people”. You have to understand that the world of databases is
complex and very “back-end” or “behind-the-scenes”.
Once you begin your structural process, you have to translate it so that
the users will understand it. Some tips:
• Don’t present stark lists of tables and fields (data dictionaries).
• Don’t present ERDs (Entity Relationship Diagrams) simply
by themselves.
• Instead, give visual lists of tables that include what fields
are in each table and how they connect to one another. In this way, you
are creating a kind of huge ERD that shows each field in each table,
what the field means, and how certain tables are connected to one another.
While this may feel overwhelming, it will help people to see why your
multiple tables exist and how they eliminate redundancy. Most people
are used to “one-table” spreadsheets where information is
repeated and is unwieldy.
• Diagrams are essential. Draw it out. Use color, PowerPoint,
large sheets of paper or whatever you need to simplify it. Whatever it
takes to show how the database will work in layman’s terms, do
it!
• Additionally, people in your organization will have ideas for
a database that represent tables in their mind, yet these tables are
actually queries that your database will generate. Be careful of this
and always keep in mind that each entity should only be about one thing.
Always back up your work (of course) and keep different versions of
the database for reference. For example, say you create a set of tables
with pencil and paper (your ERD, so to speak) and incorporate them into
an Access database design. You have a rudimentary relationship diagram
and maybe even some test queries. After meeting with co-workers to review
the database’s functions you realize that additional information
about other entities is needed. At this point you should save the current
version of the Access database as a number (say 1.0), add in the other
entities and save it as version 1.1. Keep a Word document that briefly
describes what changes are made to each version of your database in order
to follow the evolution of your work and provide the ability to go back
and add in older designs if needed.
Assessing Databases Already in Place
Assess the organization’s current database resources. The problem
with many introductory database courses (although it is understandable
when trying to introduce the topic) is that the scenarios given in class,
on homework assignments, or in labs are “from scratch”. The
scenarios assume the organization you are designing a database for has
little to no database in place. These are simple scenarios – such
as the video store, the small business, college, museum, etc. One has
free reign to then assign brand new primary key auto numbers to each
videotape, piece of merchandise or art work. A system in place that users
are used to is not there. Poorly designed and/or redundant naming conventions
to be adhered to are not issues for you. Neither are large spreadsheets
that workers have used for years and are comfortable with, despite their
clunkiness and redundancies.
All of these things were very real problems I had to face at my work
this summer. Real-world, and especially large corporate databases, are
often harried, redundant, and extremely complex. Assessing the organization’s
database environment for all existing databases in place is strongly
advised as a starting point. Determine if any of your data already exists
in said databases, be it standard MS Access databases or Excel spreadsheets
on someone’s stand-alone machine. Why do this? If a database system
in Access has been established elsewhere (perhaps in a different department
or division in the company) you can link to multiple tables in that database
over the company’s network instead of arduously importing all of
their data, or worse, collecting it from scratch without knowing that
it already exists elsewhere in the organization!
Some additional tips:
• Strive to understand the meaning of each field name in your
growing (and ever changing) database. Root out all synonymous names,
acronyms, or, those tricky fields that are a combination of one or more
fields.
• Keep feasibility and usability in mind at all times. Ensure
that drop-down lists are auto-complete. Yes you need to force the user
to select one of the following CORRECT values, yet give them flexibility
to type the first few characters of that value in the drop-down list
and have the rest of the form fill-in. For queries, ensure that all the
needed fields are available.
• SQL – Don’t forget it! While the automatic query
builder usually can accomplish simple queries, you do often need to write
code. Know the joins. Opening the relationships window in Access in the
background while you form your query helps—or at least it did for
me. What was particularly troublesome for me (until this summer) was
attempting to perform a kind of “double join” between two
tables linked by a bridge or composite table. Don’t laugh, I’m
not a programmer, ok! After much struggle, I was able to write a SQL
query that retrieved the needed data. The hint – first write a
query that joins the primary key of one table to the bridge. Use this
first query in your second, and final, one.
• Keep the database simple. Try not to burden tables with excessive
fields. This presents a problem of about-ness. An abundance of fields
for a particular table could mean the table has drifted off from what
it’s really supposed to be about!
The Enormity of the Job
With limited time (less than two months) I often grew frustrated with
the process of the project, given that I was merely establishing a test
back-end prototype database. Interface issues were not breached. Neither
were many of issues involving the missing data to be imported. Additional
resources were needed. Fortunately the project had significant long-term
backing in the company. Hopefully I can pick up where I left off last
summer.
|
