|
Flash Cards 102 - Table Design - The Building Blocks of any Good Application
I just came back from a wonderful Italian
dinner at a local restaurant with my family;
we had lasagna, ravioli, and magnificent
chicken Parmesan. To enjoy this meal,
there was a significant infrastructure behind
the scenes. In addition to the physical
building, there were people who ordered the
ingredients to make the food, people who
cooked the food, people who served the
food, people who washed the dishes, and
people who managed the restaurant. As I'm
sure you can imagine, the list could go on for
quite a while.
What we experienced at the restaurant was
the end product of an infrastructure and a
foundation that allowed us to enjoy our
dinner. In a like way, for a database
application, what the user sees and
experiences is made possible only through
an infrastructure behind the scenes.
A critical piece of that infrastructure is the
layout of the tables and fields, and how they
talk to each other. That's what we database
guys like to refer to as the data model.
Last month, I began the process of
describing a flash card program I am building
to help with my efforts to learn Italian. This
month, we'll continue that process and delve
into the data tables fields and how they relate
to each other.
| Recap: What We Need the Application to Do |
 |
|
In last month's Wizard, I outlined the
major functions our Flash Card program
needs to handle:
- Allowing testing more than one language.
- Allowing testing a group of cards as a
single stack.
- Allowing a card to disappear from a stack
when it has been answered correctly, yet
continuing to show the remaining cards until
they have been answered correctly.
- Permanently removing a card from
testing once it is known.
- Being sufficiently flexible so that it can be
used for things other than learning
languages.
In addition to the above, we also want a user-friendly
program that requires very little
training. Although I have in my mind what the
interface should look like, that is not the
place for start. If you're a regular reader of
the Access Wizard, you know that designing
the tables correctly is the most critical step
in the development of any database
application.
|
| The Table Layout |
|
|
The picture below shows the working data
model for my flash card application.
As you can see, the data model is not terribly
complex. Compared to other programs, this
application is straightforward. There are two
major tables and two supporting tables in the
data model. The major tables, on the top half
of the picture, contain the critical data:
tblFlashcard and tblFlashCardSuccess. The
two tables at the bottom are supporting
tables, which are part of every application I
build.
I won't dwell on the lookup tables in the
bottom half. Know that these are part of my
regular infrastructure, and that they contain
all the lists within my applications. If you saw
my recent presentation to the Microsoft
Access Developers Group here in
Massachusetts, then you know what these
are about and how they tables work. Their
intricacies are beyond the scope of this
installment of the Access Wizard; however
at some future point, I will be discussing
these lookup tables in depth.
The tlbFlashcard table contains the most
critical data in the database; it includes the
following fields:
FlashCardKey - This is an auto
number primary key. If you are at all familiar
with my philosophy, you'll know that this is a
field that will show up in every table, and it's
job is to uniquely identify the record in the
table and to act as a link to other tables.
LanguageKey - This field
indicates
the language that is being tested with the
record.
English - This field holds the
English equivalent of the flash card being
tested.
FrnLang - This field holds the
translation of the English word.
StackKey - This field indicates
the stack to which the word belongs.
StatusKey - This field indicates
whether or not the particular word is known,
or potentially known, from one language to
another, as well as its status in the other
direction.
LastAttemptCorrect - This
date/time field indicates the last time a word
has been correctly identified.
fldNotes - This field is designed
to help with any mnemonic notes that might
be needed to learn the word.
fldSort - This field is used to
control the order in which a word is shown
within a stack.
CreatedAt - This field, which
defaults to current date and time, is designed
to keep track of when a word was added to
the table.
There are some critical assumptions I made
with this layout. The first was the name of
the field, LanguageKey. Part of my
desire for this application is for it to be
adaptable to uses beyond foreign languages;
to be used with math facts, for example. I've
struggled with whether or not to rename the
LanguageKey to something more
generic. However, I've been unable to come
up a name that captures what I really need
that field to do, yet is sufficiently
understandable when the name of the field is
viewed. I recognize that
LanguageKey is not ideal, but I've
been unable to come up with anything
better.
I have very much the same type of issue with
the two fields, English and
FrnLang. The names are less than
ideal, but I've not been able to discover
anything better. In my programming, I know
what those two fields are, and I'll be able to
program around these less-than-ideal field
names. I did not treat the naming of these
fields casually; I struggled with them for quite
some time before accepting the fact that I
could not come up with good generic names.
The StackKey field is another item
that I am wrestling. When you think of
learning foreign words, it's natural to gather
them into groups, for instance, days of the
week or numbers. That grouping is what this
field is trying to capture.
In reality, a word may fall into multiple stacks.
For example, the word 'march' could mean a
month of the year, or the verb meaning to
walk with purpose. So it would be
reasonable to allow a word to be a member
of more than one stack. In practice though,
the cost of allowing a word to be in multiple
stacks is much higher than the value that
would be gained by allowing it in only a single
stack.
The field, LastAttemptCorrect, is
designed to indicate when I was last able to
successfully translate the word, which
provides an indication as to whether or not it
should be a member of the current tested
stack.
I believe the fldNotes field has great
value. Although only a few records will have
data here, I can put a mnemonic note into
this field to help me to remember its
meaning if I am struggling.
The CreatedAt field, which I have
defaulted to "now()" is part of an auditing
mechanism as well as an easy way to
determine when words were added to the
main table.
|
| The Road Forward and Some Philosophy |
|
|
This month we have reviewed the data
model and have analyzed the flash card
table. Next month, we'll review the Flash
Card Success table and analyze how the
two tables interact with each other. After that
is complete, we'll start to design the forms
and discuss how the user is expected to
interact with the program.
Notice that we've spent all our time so far
analyzing the application functionality and
delving into designing the data model. We've
spent no time analyzing the user interface or
discussing reports.
Many times, both users and developers want
to jump right into designing the forms. This is
a big mistake. By developing forms first,
critical aspects of the infrastructure may be
missed. It's the equivalent of starting a
restaurant with designing the menu rather
than determining how the restaurant is going
to operate behind the scenes.
Starting with the data model is the right
approach. Doing this will increase the
application's flexibility. Although it can be
frustrating, we end up building a more
adaptable application that will be able to grow
and change as our needs evolve.
|
|
Tip of the Month - Use Auto Numbers as Primary Keys even if You Think You Don't Need a Primary Key |
|
|
|
Every single table you create in an Access
database should have a primary key. This
primary key should be an auto number.
Keep these two rules in mind and you'll avoid
unnecessary reworking of your applications.
As I mentioned in the main article, and many
times in the past Wizards, the
primary key is a critical to any table; an
AutoNumber is the best field type to use.
There is no question that a primary key
should have no meaning to human beings. If
it does have meaning, at some point
someone will want to change it. This is highly
undesirable since changing the value of
primary key can result in unexpected
consequences. Don't let it happen. If
someone needs an identifier that makes
sense to them, give them a field that is
dedicated to that, but don't use it as your
primary key.
Even tables that are relatively trivial should
have a primary key. One of the most
important things that you'll discover as you
build databases is that needs change over
time. A table that has been relatively trivial
may be called upon to do more than you had
originally expected.
If you have already used an auto number as
a primary key, life will be considerably easier
than if you have to go back and rework the
table. The cost of an auto number primary
key is negligible, the potential benefit is
significant.
|
|
