Newsletter of the Association of Library and Information Science Students (ALISS)

October 2003

Vol VII Issue IV

Silverfish Left Navigation Bar

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.





Tom Rhoades is a second year student in the day MLIS program.