Here is a list of common mistakes designers often procure due to ignorance until they become more experienced
in Microsoft Access. Sometimes its OK to break a rule, but you should know the consequences of your
actions. Bottom line is if you don't know the side effects of breaking the rules then don't break them.
Database design should not be left to a novice who just took a couple of
Microsoft Access Training courses. Business managers or owners routinely send an employee
to a training course or two and think the employee is going solve all
of their data headaches. Wrong! We have redesigned more Microsoft
Access databases here in Phoenix AZ than I'd like to count just because of this short sightedness. For
the most part these lightweight training courses are your major nightmare of ill-guided
productivity, not the employee.
Your employee most likely has good intentions of providing an excellent
solution in solving your data inefficiencies. But if the employee is being
handed garbage, so the old saying garbage in - garbage out never rang so
true.
With all of the lackluster training courses out there, IntelliRel is going to give you 10 of the
biggest tips to help your company avoid the most common database blunders. Some of the bad design
guidelines are even stressed as a practical design to follow. So please heed caution and ask your
employee some of these questions to see if they are putting your database at risk for costly
mistakes.
These tips can literally save you $1000, $5000 or even $10,000 dollars or more in the near future.
Take careful notes now, so you don't run into the same commmon pitfalls made by inexperienced designers.
Here they are in design order:
Mistake No.
Mistake
1
Normalization. This subject is probably
the most subjective of all mistakes. What is acceptable in one database
may not be acceptable in another. Like I said this is very subjective,
but some simple guidelines can go a long way here.
We will use two types of data to illustrate our point. Addresses and Children.
Addresses have a standard form which you usually create 1 to 3 fields (excluding city, state & zip)
in a database to encapsulate them. For the most part its all the address you'll need to send out
correspondence and it will get to the destination just fine.
Children on the other hand can be tricky. Why? Because you can't anticipate
the total number of children a family may have. Some have none, 1, 2.3 and others have somehow
managed over 10.
What's the guiding light in trying to figure this out? If you can't anticipate
the occurrences of an entity or process, then this is a good candidate to
place in its own table linked to its parent record. Remember this is only a safe rule of thumb
to go by and not the gossip truth in all instances.
2
Table Names.
No wildcard characters or spaces. Do not name a table 'tbl2004', because the data is in 2004.
At some point you will want to query out data for 5, 10 or 15 years to see trends on what customers
are buying. So having a table for each year is not good design. Naming a table should be based on
a logical entity or process such as a Customer, Order or Shipment etc so that it is self
documenting. We also do not advocate putting spaces in table names. Use proper case and name a table
'OrderDetail' not 'Order Detail'.
3
Field Names. No wildcard characters
or spaces (Alphanumeric only). Use proper case instead of spaces between
names. Use 'LastName' instead of 'Last Name'. Why? If you get too much data
at some future point that Access can't handle, you'll want as seamless transition
to Sql Server as possible. Having field names without wildcard characters
and spaces will be foresight you wish you had when you first designed your
database.
4
Field Data Types. Overuse of text
fields. If you need to store pricing data, use the currency or a number type field.
If the number only needs to be a whole number (without decimals), use an
integer or long data type. If your data answers to a simple yes or no question , off or on then
use the boolean data type.
Proper data typing will help users from making unnecessary errors.
If you're adding numbers and you have the possibility to enter text, guess
what will happen? At some point text will be entered and your calculations
will start speaking greek to you %#$^$&$. So only allow the proper data
to be entered in the first place.
5
Blobs (Binary Large Objects). Don't
do it. Your database will bloat to no end. Compacting and backing up the database will
take forever. Database performance will suffer immensely. Use a point reference
to the file location instead.
6
Autonumber Primary Identifier Key.
For each table only use one field as your primary key and linking field to other tables.
This will become the unique identifier for every record and the only linking field to other tables.
Never use your own company data as the primary key.
Companies usually use a company created ID to link their relatinal data together. This is not
the way to set up a database. Just as a reference, a table linking
field should never change. I have seen companies want to change their ID's when the company grows.
At first it seemed OK, but as the company grow they realized their ID system was
outdated.
By now the company had millions of records in active and history tables to change. Because
this type of domestic ID was used as a linking field, then all the data with that ID needed to
change also. With millions of records, this was no easy task to change, plus they had
code all over the place referencing these domestic ID's to do some type of special formatting
based on the them.
Instead a company should use the autonumber primary key to identify and link. This way they would
not need to worry about circumstances like this. Again costing the company thousands of dollars that
could be prevented by using a foreign key such as the autonumber feature.
7
Indexing.
Don't over use and don't under use this feature. Indexing should be used for common searches performed
by user or by the program to link records to each other. If you need to find customer information
in a hurry, then index those fields so you can quickly retrieve this information. Know what
types of information you need in a hurry. What do you need know about the customer so you can
locate these types records it in a heartbeat. Also when running reports an index is effective on
date fields.
What are the common everyday tasks that you interact with that you need
quickly. These are the fields you need index. Not a birthday or salutation
field, but a last name or date field. Date fields are common query fields
you need to report on for daily activities to accomplish. You have to figure
out what are the main fields that really run your business on a hour by
hour or minute by minute basis and only index them.
8
Referential Integrity.
Always use the built in referential integrity if all possible to keep your data clean. This always
amazes me to see this oversight. I have had two companies that have paid over $1000 each
to clean up their data before we could even work on their real database problem. So when I say these
tips could save you $1000, $5000, $10,000 or more, believe me it could easily happen to you.
Preventing duplicate values goes a long way in helping out here too. Our FREE software package
called MyMASS 2004 (My Microsoft Access Structural Status) can help find out if you have integrity
problems.
Cascading Deletes. Do not use cascading
deletes when setting up your referential integrity unless you do warn the
user the consequences of what they are deleting. We had a company setup
their database with this feature turned on. The user who deleted the top
level lookup record did not realize all the records that were linked
to its status would also be deleted. This meant orders, shipments, payments
and the whole nine yards were gone. They deleted it because the status was
no longer needed.
Instead the database should be setup with field to state its status as active or
inactive. Then changing the status to inactive. This feature can be very destructive in the wrong
hands. Our FREE software package
called MyMASS 2004 (My Microsoft Access Structural Status) can help find out if you have cascading
problems.
Splitting Data and Application. Split
up your database into two files. One for the application (cosmetic components)
and one for the data (storage component). Your cosmetic components are the
components that display your storage components. Cosmetic components are
a form, report or query. Your storage component is your precious table data. And
if you're really up to the task of making a robust program, create a startup/maintenance
component. All of our applications have this added maintence component so we can react to user
demands very quickly.
It is crucial to split up your application from your data. First a novice will not
know that Access is not a bug proof program and tends to go a little haywire sometimes. This freaky
activity usually causes a lot of corrupt databases to come out of the woodwork. So having your own
application to develop with is an invaluable asset. Also not all reports are known at the time of
development and users will come with buckets full of them as the program becomes more useful
and starts to render its full purpose. So having a methodology to make quick updates is a must
if your're to keep up with the daily database grind.
If you need a ad-hoc report writer to help keep up with reporting request, check out our
award winning product Drill-Down Tally 2007
to get accurate on-demand reporting now.
Not only do you want to split up the app and the data but you also want each user to have their
own application pointing back to the central database. I have seem all kinds of database corruption
problems when many users use the same application. The architecture you need to keep your database
running as smoothly as possible is to have each user with their own application pointing to the
central database file. Basically it's a client/server operation that will give you the best
results for staying up and running.
So what do all of these mistakes have in common? In one word - Inexperience. Most design
a database for what they know now, without taking into consideration the common growth that
usually happens when a database evolves. That's why it is so important to get this valuable insight
now. You could be saving your company $1000, $5000 or even $10,000 or more in the near future. These
are common mistakes that can be overcome with a little knowledge.
If you're having problems with your database, it's most likely due to one
or more of the mistakes listed above. Because IntelliRel excels in this arena, we'll
be more than glad to help you out with your current siutation. We have helped many businesses in
Phoenix Arizona before and we will no doubt do it again. Why? Because bad database design is so
prevalent. Don't let it happen to you! See our testimonials.
