Lookup field vs junction table

  • Thread starter Thread starter LurfysMa
  • Start date Start date
L

LurfysMa

I am working on an electronic flashcard program. Most of the subjects
are simple lists of questions and answers. Those seem to be working.

Some of the "subjects" have "categories" of questions.



One example is a kind of trivia subject. There are several
"categories" such as "geography", "history", etc., like in the Trivial
Pursuit game. Each card has a question in each category.

Since the categories really don't factor into the question other than
being a title, my plan was to define a lookup field in the table for
the categories:

Field Data Type Description
QuestionID Long (4) Primary key. Autonum.
Category Lookup Trivia categories.
Question Memo The trivia question.
Answer Memo The answer.

Is that a reasonable table design for this application?



A second example involves a more complicated use of categories. It's a
database about various information about the 50 US states. For each
state, I neeed to store information about the capitol city, the date
it was admitted to the union, the size, etc.

For this application, I think I need two tables (States and
Categories) with a junction table to effect a many-to-many
relationship:

tblStates:

Field Data Type Description
StateID Long (4) Primary key. Autonum.
State Text (20) Name of the state.

tblCategories:

Field Data Type Description
CatID Long (4) Primary key. Autonum.
CatTitle Text (20) Name of the state.
CatQuestion Memo The question.

tblAnswer

Field Data Type Description
StateID Long (4) Link to tblStates.
CatID Long (4) Link to tblCategories.
Answer Memo The answer.

Here's some sample data:

tblStates:

StateID State
1 Alabama
2 Alaska
3 Arizona

tblCategories:

CatID State Question
1 Capitol City What is the capitol city?
2 Date Admitted When was the state admitted to the Union?

tblAnswers:

StateID CatID Answer
1 1 Montgomery
1 2 December 14, 1819
2 1 Juneau
2 2 January 3, 1959
3 1 Phoenix
3 2 February 14, 1912

Is this a good database design for this application?



Thanks
 
LurfysMa said:
Since the categories really don't factor into the question other than
being a title, my plan was to define a lookup field in the table for
the categories:

Field Data Type Description
QuestionID Long (4) Primary key. Autonum.
Category Lookup Trivia categories.
Question Memo The trivia question.
Answer Memo The answer.

Is that a reasonable table design for this application?

For this application, I think I need two tables (States and
Categories) with a junction table to effect a many-to-many
relationship:

tblStates:

Field Data Type Description
StateID Long (4) Primary key. Autonum.
State Text (20) Name of the state.

tblCategories:

Field Data Type Description
CatID Long (4) Primary key. Autonum.
CatTitle Text (20) Name of the state.
CatQuestion Memo The question.

tblAnswer

Field Data Type Description
StateID Long (4) Link to tblStates.
CatID Long (4) Link to tblCategories.
Answer Memo The answer.
Click to expand...

Murfy's La',

Don't use a lookup field to edit the data directly from the table -- at
least not until Access 2007 :-). Use a form to edit the questions
using a combobox to get the possible categories.

For your states example, it depends on how you're going to use the
data. For example, are you going to show the user their overall score
when they're done? A separate tblAnswers might be easier to compute
this. I haven't actually tried storing the answers both ways, nor have
I tried to compare the resulting SQL statements for that calculation.
I suggest that you actually do that comparison to see which schema
works best for your initial uses of the database. Also, are you
limiting yourself to general questions that can be asked of any state?
It looks like you are forcing each question to have a general category.
How would a question like "In what year was the Alamo constructed?" fit
into a category? I still have not thought much on what a great schema
would be for trivia or survey questions. I used to work for a company
that composed, administered and analyzed surveys for business marketing
so an ideal schema for that purpose would be one that allows me to
create, edit, administer and analyze surveys easily. I would want to
have a schema that allows for as many kinds of question formats as
possible as early as possible since a new question format seems likely
to be the first hurdle thrown (!) at a trivia or survey schema. What
you have seems normalized enough. So if it is allowing you to drill
questions and to get the feedback you need I would say that your schema
is adequate for now, but try to anticipate likely future requirements
if possible.

As far as which of the schemata you have shown have the most merit,
your observation that the Category can be modelled as a kind of
optional attribute to a question seems much more flexible and useful.
In your case the Category/CID is only used as a means to group
questions. Of course, if you plan to break down the answer percentages
by category also you would have to look at the resulting SQL again to
see if something like "GROUP BY CID" will work easily. Don't force a
Category junction table. You're likely to need a many-to-many soon
enough anyway.

One thing you don't show is your table for storing a particular
"survey." For Trivial Pursuit, it's not that important to keep a
history of responses. For a survey, it is. You also need a way to
determine if an answer is correct. Is "Statue of Liberty" going to be
marked as incorrect if the correct answer is "The Statue of Liberty?"
If you use a Memo field you may also need to hire a grader :-). For
your personal training you can answer the question, click a command
button to show the answer, then decide for yourself if 'X gets the
square,' bzzzzt... wrong game. The first schema needs one ID to look
up the correct answer and the second needs two ID's to look up the
correct answer so there's not that much difference in looking up an
answer (perhaps by using DLookup).

You've got to take your best guess at a schema, write the SQL you'll
need to get what you'll want, then fine tune the schema until the SQL
you need is as simple as you can make it. A lot of this is an art, but
it's not unfocused.

Finally, to make it really effective you need to hook up a relay to the
serial port. You decide for yourself how many volts to use when you
get an answer incorrect. That'll add a bit of pizzazz to your study
efforts.

Happy pursuits,

James A. Fortune
(e-mail address removed)
 
Back
Top