|
There are two types of database
formats…a flat-file database and a relational database. You can think of Excel
as being able to create a flat-file database. In this type, you have to supply
a field for each type of data you’ll need to acquire. So in other words,
if you were collecting family data, where the family name would be in the main
slot and across the row you’d list all the names of each family member, you’d
need several slots to make sure you have room for larger families.
With a relational database, you
can have one table for the family name and a separate table for family members.
Then you can link the two tables together so that each member is linked to
their family name. This would not only save you space in your database…and
computer memory…but it would make it easier to make changes, if necessary.
A relational database is a much
better type of database to use because it is more efficient. One problem I’ve
often seen, however, is that users open a relational database program like
Access (we’ll be using
Access 2003 for this article’s screenshots and steps) and then use it like a
flat-file database…entering fields for all the info they need to enter and then
ending up typing in the same information over and over. (I’ll explain this
process more as we go along, so don’t worry if you didn’t quite understand now
how that happens.)
If you are typing the same
information into a field over and over…you are not using your database
in an efficient manner. Think of it this way…using that family example. If you
are typing say Chapman, Dian and Chapman, Greg into Last Name and First Name
fields…that’s not very efficient. A more efficient way would be to make a table
for Last Names and enter Chapman into that table. Then enter Dian and Greg into
a First Name table. Now link Dian to Chapman and Greg to Chapman. It may sound
silly for this example…but you’ll see more advantages later in this article.
And if you want better details about
how relational databases work, be sure to check out the TechTrax archives where
you’ll find additional articles by Microsoft MVP, Beth Melton.
In this article, I’ll focus on
linking tables so that data entry is more efficient, but along that trail, you
should also get a clue as to why this method is much better than allowing users
to type away entering data over and over in flat-file databases.
I’ve created an employee database
for our sample. In it is a table called EmployeeNames, where the names
of our employees are entered.
The second table lists the names
of each Department in our company.
Why didn’t I just provide a
department field in the employee table, as I’ve seen so many people do? Well,
because that would mean there would be too many chances for typos! In the image
below, you can see that none of the departments are properly typed. Granted, I
doubt I’d hire a typist this bad, but it does show the potential for the types
of mistakes that could be made.
I don’t want my users to have the
ability to enter data in whatever format they choose. Just as with states…the
proper way to enter this information for Illinois would be using the two state
abbreviation. But how many times have you seen all these formats: Illinois, IL,
ILL, Ill.
So to ensure that the data is
entered in the proper manner…and spelled correctly…I’ve created a separate
table to house the department names and then I’ll link the two tables, so that
users can only choose the department name from a predefined list. This
will link each employee to their department.
To do this, I open my EmployeeNames table. I add a new field called Dept. In the Data Type field, I choose Lookup
Wizard.
The wizard opens. I have two
choices. I can choose to link this field to a table or query that contains the
information I need. Or I can choose to now type in a predefined list of names
from which the users will choose when this field displays. Since I already have
a table created with department names, I choose the first option…to look up the
values from another table.
The next panel in the wizard will
display all the tables or queries I have in my database. Since I only have one
other table, that’s the only one displayed here. I select it and move on.
Next I see all the fields in the
selected table or query. Since I have only the one main field, I choose
that…the Department Name field…and click the > button to toss
that field over into the Selected Fields side of the wizard.
As you can see, it now shows that
I have selected the DeptName field to use for this Look Up.
The next option allows me to assemble
the selected field(s) in alphabetical order. This is a good idea, particularly
if your list of department names (or whatever you’re listing) is long. It will
save your users time from searching through a mess of names.
And as you can see below, the list
is now in order.
Finally, I need to name this new
field. Since I already typed a name before choosing the wizard, this is the
name that is offered up to me now. I don’t want to change it, so I just click
the Finish button.
After that, you’ll be advised that
you’ll need to save this new table format…so choose Yes to resave the new
layout.
When users enter data into my
employee database, they can now only choose a department name from the
list I’ve provided. No more typos. They will enter the first name, the last
name and then tab to the Dept field, click the drop down and choose the proper
department for each employee.
If you’re more of a keyboardist
than a mouser, you can just start typing the characters for the department and
Access will jump to the word that matches the letters you’re typing. Once you
see the name you need, hit the tab key to accept it and move on to the next
record so you can start entering the next employee data.
Granted, I could have just entered
the department names into the field by choosing to type them into the list
manually (as shown previously…the second choice in the wizard). This would have
provided the same type of predefined list that would avoid typos, but you
wouldn’t have a second table. It will depend on your needs as to whether you
really do need a second table. I prefer to keep much of my data in separate tables
and link them with relationships so my database can more easily be expanded in
the future, since all the data is already divided up. Your needs may
vary. (This chopping up of data is essentially called normalization. See
those Beth Melton articles for more details on this, too.)
But if you do link tables through
a relationship, you can further modify that relationship by clicking the Relationship icon, as shown below.
Here you can now see that a link
has been established between the Department table and Dept field within the
EmployeeName table.
Now some of you may notice that I
actually chose to use the DeptName field from the Department table, but here
the link is connected to the ID within the Department table. We will see
the name of the department, but the software needs to actually use the ID for
that record…the primary key. Therefore, that’s the link that you see here. It’s
a bit technical...so not worrying about this process is the best advice. (If
you’re a real geek who needs to know the reasoning…grab a 4,000 page
database book and start reading!)
To adjust how the relationship
works, you can just double click on the linking line itself and that will open
the Edit Relationship dialog box. From there you can choose to Enforce
Referential Integrity and also choose to make sure that if a linked field
is deleted in a table that all that data is removed from subsequent tables with
that relationship. Again…this discussion moves beyond the scope of this
article, so you’ll want to dig deeper into database design if you want more
details.
But I do suggest you choose these
options in most cases so at least you’ll be warned if you attempt to do
something that might mess up your relationships.
Designing databases is a huge subject.
This article has given you a little information about how to better design your
basic databases so that you can avoid most errors and help to ensure that the
data being entered is correct. It also makes data entry go faster. And by
chopping up your repeated data, you have setup your database…at the start…in a
format that will more easily allow you to expand it in the future.
Finally, if things need changes in
the future, i.e., the Design Dept changes to Graphics, you can very easily
update your database. If you typed in the names manually into each record, you
would have to update possibly hundreds of records. Additionally, if you typed
the list manually in the same table, you would still have to go into the wizard
again, or at least the design table, to modify the list. But if you used a
separate table for your department names, this change would be very easy
because all you’d need to do is open the Department table, change Design to
Graphics and save it. With the proper integrity setting applied, all those from
the Design department will be updated into the Graphics department. A five
second change versus possibly hours of database editing and potential errors!
|
