7 Ways to Make Database Administration a Nightmare
1. Store divisible fields
A “divisible field” is a field that can be broken into smaller, more manageable parts. One of the basic rules of normalization states that every field should be “indivisible” in that it has been broken down to it’s smallest meaningful value.
For example, why would you store the first name and last name of an individual in one field? If you stored data this way you wouldn’t be able to search for someone based on last name without a lot of tinkering. It also makes it impossible to sort by last name. Many databases also have the street address and city, or city and state in the same field. By doing this you are unable to filter your records by state or by city. Storing fields that can be further broken down is a surefire way to create problems in your database.
2. Store calculations in a table
If you have a table that lists product prices, it’s very easy to create a calculation based on that product price within a query. So if you wanted to see what the product price would be with an added 10% fee, you can have the column of calculations in the query showing you everything you’d need. Don’t get carried away and use an Update, or Make Table Query to store that calculated field in the table.
There is no use whatsoever for holding a calculation within a table. Calculations need to be flexible, so if you store it, what happens if the product price was changed? Then your calculation would have to change, but if stored in a table, it won’t automatically. Stored in a query? It’s just fine.
3. Store redundant text values
This is probably the most common reason why databases return inaccurate data in queries and reports. For example, let’s say we have a school database that keeps track of names and addresses as well as classes taken. Each person entered into the database can take one or more classes. For every record, someone types in the person’s name, and a class they took.
Although this may get the job done, there are some major problems with this type of design. By typing in names over and over again, you are greatly increasing the chance of misspellings (I wouldn’t want to bet on anyone’s ability to type a last name like Wojezkowszky correctly over and over again). Every misspelling means that data will not be returned in queries if you are searching for a specific name. Queries will be inaccurate, calculations and statistics will be inaccurate, and reports will be inaccurate.
In this situation, it’s much better to have a table with the student roster that lists each student once, and merely referencing a Student ID number. This allows student names to be selected from a listbox or combobox – so the typist isn’t given the opportunity to misspell any names.
4. Never compact or repair your database
You might want to just take this very moment – as you are reading this – to quickly compact one or more of your databases. This step is important, yet a surprising number of experts and novices alike simply ignore it.
Any time you create objects in your database – queries, reports, forms – you are taking up space. When you delete an object, that space is void, but isn’t automatically freed up. It is still empty space within the Access file. Compacting and Repairing your database organizes the information, removes the void space, and keeps the ship running smoothly.
If you have a database that you haven’t compacted in quite some time, take a look at the file size before you do. It’s not at all rare to see a database that’s 15MB turn into 700K after compacted depending on the frequency of development work and contents of the tables. It only takes a few seconds!
5. Name database objects improperly
Is there anything more frustrating than opening up your Access database, clicking on the Queries tab, and seeing about 80 queries with names like:
Final Quarter 2 Sales Numbers
Pre Sales for Quarter 2
Q2 Sales
Quarter 2 Sales Numbers
REAL Sales for Q1
Sales for Quarter 1
Sales for Quarter 1 FINAL
Sales for Quarter 1 test
Poor naming makes it virtually impossible to get any worthwhile development done in forms and reports because there are so many objects with similar and confusing names – often with absolutely no difference between the queries or tables.
Name the objects properly.
Access sometimes names items automatically. For example, when creating subreports you may find a report with the same name as your query, such as, “qry_SalesDataMarch.” This is confusing when you’re on the Reports tab and you see reports that are named like queries. Take the time to make them right or your development efforts will be truly horrifying!
6. Use multiple queries as a form’s record source
If you design a database properly, and aren’t involved in an absolutely crazy project requiring extraordinarily unconventional development and design, there’s absolutely no reason to base a form’s recordsource off of a query that is holding multiple tables.
Take a step back and ask yourself why you are designing it that way. Is it because you want to associate a specific name from one table into the record of another table? Then use a combobox to show you the names, and an ID to store in the table (covered in AppTrix Volume 1 Issue 1). If it is because you need to fill in values in each, but not look anything up, then use a form and a subform – that’s what subforms are for.
What it comes down to is a mess in your database. If you find yourself situation where you are about to use multiple queries as a form’s record source, it’s almost certain that there is an easier way to accomplish whatever you’re trying to do. Check out some resources on the internet, dive into a book or two to see if you can do things differently, or just contact us and ask. The extra step now will probably save you a lot of time and headaches in the future.
7. Design forms and reports before tables and queries
A good design is the fundamental structure of your database. If your design is flawed and you build on top of it, you’ll eventually have to change things. Changing your design when all you have are a few tables is easy. Changing your design when you have a fully-functional database on the desktops of users is tragic.
We have a very basic rule when it comes to designing databases for our clients. The design is done on paper with a pencil first. Then it’s tested logically and re-designed if necessary. Tables, relationships, forms, queries – everything is done with paper and pencil and continually tested and re-vamped until the basic structure is as absolutely close to flawless as possible, taking into consideration the primary function of the database.
Your database should be completely designed before you turn on your computer to start creating it. The only exception to this rule is if you are creating a database for tracking Fluffy’s veterinarian visits that will take you about 5 minutes to complete and will be used once every few months.
By not designing your database before you start building it, you inevitably fall into traps like, “Oh, I didn’t realize...” and, “Wait, I can’t do that...”. These traps take up valuable time, and you may even run into a scenario where you simply can’t do something you should be able to accomplish if the database was designed properly. Database design is a science, not a “boring part.” Give it your full attention and your projects will be completed faster, cleaner, and allow for better upgrading and flexibility!
©2010 Blue Moose Technology, LLC
Article Publishing and Re-Print Guidelines
- The article text, resource box, URLs copyright information and these guidelines must be left intact and unchanged.
- When re-published online, all links must be live hyperlinks.
- These articles may not be distributed in any manner that does not comply with federal communication guidelines. Be Good!
- These articles must not be password protected or limited to membership or paid-only viewing. Keep 'em free, folks!
You can easily link to this article!
If you'd like to link to this article from your own website, copy and paste the following HTML code onto your page:
On your page, the code will appear like this: