The Flash Card Program Data Structure: The Success Table
We're in the heart of winter here in New England. It's
cold! This is the time of year you either bundle up and
venture out into the cold weather, or batten down the
hatches and stay inside. If you are staying inside, it's
a good time to learn new stuff. I'm not a winter person,
so I've taken on the task of learning a new language -
month, we continued the process of building a
Flash Card Program to help us learn vocabulary and
analyzed the Flash card table. This month, we
continue with our analysis of the flash card success
We're in the process of building a program to help us
learn vocabulary for the Italian language. In the last
two issues, we've laid out the requirements for the
program and have begun to analyze the tables in the
application, as shown below.
In our last Wizard, we delved deeply into the flash card
table. This month, we'll do a thorough analysis of the
flash card success table. At the end of this process,
we will have our tables understood and in place.
The flash card success table is designed to capture
each attempt at learning a word. The thought is that
when a user attempts a word, we record either
success or failure. If the user gets the word correct, it
is recorded. If the user fails to get word correct, we
record the unsuccessful response.
We get three important outcomes from this table.
First, we get an overview of how difficult each word is
Second, by analyzing each failed response, we get a
sense of the type of mistake that is being made.
Third, by analyzing incorrect responses, we can detect
patterns that, when understood and corrected, can
lead to tactics that can help our users become more
efficient at learning the language.
|The Flash Card Success Fields
There are five fields in the table.
FlashCardSuccessKey - This is our auto
number field; it is a primary key whose job is to
uniquely identify the record in the Success table.
FlashCardKey - This is the identifier of the word
being learned. It is a foreign key that hooks into the
primary key in the flash card table.
TestSet - This is a grouping mechanism; it is
used to identify which attempt the flash card
record is associated with.
AttemptDate - This is a DateTime field that
records the date and time that the user makes an
attempt. This defaults to Now() so that it automatically
will put a time stamp on the record.
Success - This is a true/false field that
indicates whether or not the particular attempt was
IncorrectResp - This field is used to record the
response that was attempted only when the response
was incorrect. There is no need to record the correct
response, because we know it matches whatever was
expected as recorded in the Flash Card table.
|Putting the Tables Together - How Do They Talk To Each Other
You'll notice that there is a link between the Flash
Card table and the Flash Card Success table. This
link is between the FlashCardKey in the two tables.
The names, by intent, are the same in both of the
tables. The idea here is to make it easy to analyze the
foreign key when you're looking at the Flash Card
You can see this link in the diagram above with the
line going from the fields FlashCardKey in both tables.
When you look at the diagram, you'll note a small digit
1 next to the Flash Card table and an infinity symbol
next to the Flash Card Success table. These indicate
that for every record in the Flash Card table there is
one or more records in the Flash Card Success table.
The LookupMaster and LookupType tables play a
supporting role. They're also joined with the same
field in each table. As I mentioned last month, these
tables, although not critical to the Flash Card
Program, are standardized in all my applications. You
could easily get to the same functionality of these
tables with individual lookup tables as needed.
|The Foundation Is Built - Time To Look Forward
At this point, all the critical pieces are in place. We
know what we want the application to do and we've
designed our tables.
Keep in mind that, even though we've spent a fair
amount of time analyzing and building the tables, in
practice we'll likely make changes to the fields and
tables as we begin to find various needs that we had
not anticipated, or as we make decisions that will help
us get greater speed in either development time or in
application response. Our goal is to discover these
changes as early as possible. Since these are
structural issues, the further downstream we are in
application, the more expensive it becomes to make a
fundamental change to our table structures.
Next month, we'll begin to design the forms with which
the users will interact. This tends to be a key part of
the application and the most fun to build.
Tip of the Month - How To Pull The Date Portion Of A Date Time Field
This month, we used a date time field to record when
a user makes an attempt at learning a word. By
default, a date time field will record both a date and a
time. For example, 11/29/2008 10:07:47 AM would be
recorded on the weekend after Thanksgiving on
Saturday morning. If we want to get just the date
portion of that into a query, report, or form, there is a
handy function called DateValue.
To use this function, type DateValue followed by the
date and time that you want to get the date for,
enclosed within parentheses.
For example, if we type DateValue(now()), it returns
just the date. If you are using it within a query, then you
just reference the field that contains the date and time
for which you want the date.
If you are trying to manipulate an actual date and time
it's just a tad trickier. Access identifies a date and time
with the pound symbol "#". Therefore, when you're
entering in an actual date and time, you have to begin
and end the date and time with #. For example, you
enter datevalue(#11/29/2008 10:07:47 AM#) and that
would resolve to November 29, 2008.