A few of our clients...

• Children's Hospital and Regional Medical Center
• Harborview Medical Center
• Town Hall, Seattle
• Lake Washington School District
• Freezer Longline Coalition Cooperative

At Cloud City Consulting, we deal with both building databases from scratch and from working on existing databases. It is very common for an employee to create their own database, which soon becomes both indespensable but needs features beyond what the employee can provide. In this case, our experience lets us analyze requirements, migrate data, and move the entire project into a sustainable database lifecycle.

Database Re-Design

Just for fun, we have included a common scenario that faces many businesses, which is "is it possible to modify our existing database to fill new requirements?" The answer is below

The following example is shows a database that was converted. The conversion helped solve many problems and made the database much more usable. Many Access databases are created by a non-programmer out of a need, and as needs grow it becomes economically viable to hire a professional programmer to help the database reach its fullest potential.

The old database in this article suffered from the common problems which occur when a small database eventually grows and becomes a big database. The large amount of growth is too much for the original database design, and upkeep becomes complex and awkward. Here were some problems:

• All data was kept in one table.

The database was originally created by a friend of mine who had never created a database before. With few exceptions, a first database for anyone consists of one table which holds all of the data. While a 'flat', or one-table database is a great place to start, it doesn't give the advantages of the nice relational-database management system (RDBMS) offered by Access. Access only offers relational database capability, it doesn't give it for free and designing one takes some work and experience.

• Basic database usage required complex tasks

The database tracked mailing list contact information and attendence to concerts and events. Each time a new event occurred, a new field has to be added to the table structure and a new control had to be added to the input form. This made it risky an difficult to add new events which should have been quick and painless, and to me is like having to go to a mechanic to get your car working on every time you need to turn right or left.

• Index numbers instead of text was stored for some items

Some data was only viewable from the data input form, because of a complex problem of storing database indexes instead of text in a person's record.

• Layout made data entry difficult

When it comes to entry, data-entry must be fast and pleasant, otherwise the chance of it being skipped is great. Layout of data entry forms, and good design practices, makes a database feel like a tool instead of a necessary evil.

The old database:

Here are the features made possible with the new database:

• A relational design was implemented

The relational database capabilites were used to create a database which is faster and stores data in multiple tables. This saves space and makes the pop-up menus, shown below, possible.

• Contrasting colors improve usability

Color and design is used to make the database easy to use for hours, and large navigation buttons make moving around easier.

• Users no longer need to edit the form design or table structure

It is no longer necessary to edit the table structure or the data entry form to add a new event or feature. New pop-up interfaces were added to make it simple to add and delete items that previously required difficult database design changes.

The new database

• Adding events a patron has seen becomes easy

Clicking the ADD button for a section of the form gives the item list for that area. The list is multi-select, letting the user add one or all items as one time.

• All items shown in the pop-up menus can be easily edited at any time

The contents of any multi-select list can be added, deleted, or edited at any time quickly and easily.

Conclusion

Converting a database early on is often a good investment. It prevents bad or redundant data, and can make the database much easier to use. Depending on the needs, a database can also be locked-down, making it all but impossible for a random user to accidentally delete or modify important records.