The Access Wizard Newsletter Tips, Tricks, and Traps for Access Users and Developers
January 2009

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.

In this Issue
  • Tip of the Month - Use Auto Numbers as Primary Keys even if You Think You Don't Need a Primary Key
  • Recap: What We Need the Application to Do
  • The Table Layout
  • The Road Forward and Some Philosophy

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

    Quick Links...

    If you can't see all the Pictures Click Here for Online Version

    Custom Software Home

    Access Wizard Archives

    Our Services

    Join our mailing list!

    Forward email

    Safe Unsubscribe
    This email was sent to jim@custom-software.biz by jim@custom-software.biz.

    Custom Software | Copyright Custom Software All Rights Reserved | Westford | MA | 01886