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 to learn.

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.

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 successful.

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.

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 Success table.

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.

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.