473,837 Members | 1,431 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Lookup field vs junction table

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

--
Running MS Office 2000 Pro on Win2000
Jul 12 '06 #1
1 3645
LurfysMa wrote:
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.
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
CD********@Fort uneJames.com

Jul 12 '06 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
4066
by: mksql | last post by:
As an example, I am building an authentication mechanisim that will use data in the 3 left tables to determine rights to objects in a destination table, diagrammed below. In this structure, multiple records in the left tables will point to multiple records in the right table. Normally, I would approach this problem using junction tables (LeftID, RightID) to create many-to-many joins. However, given the structure of each table is nearly...
1
3744
by: Rene Crespo | last post by:
Hello, Sorry about the length of this explanation! I am using Access 2000 to assist an export sales department keep track of its weekly offers of products to clients. The database already keeps track of "product offers" and prepares a report that a group of Clients can receive by email or fax each week. The challenge is that not all clients are interested in all the "items" on the product offers (more than 200 items listed). While...
3
2932
by: my-wings | last post by:
I've been reading about how evil Lookup fields in tables are, but I've got to be missing something really basic. I know this subject has been covered before, because I've just spent an hour or two reading about it on google, but there is something I still don't understand, and I'm hoping someone will be willing to explain it to me in small words. Let's say I have a table for addresses, and it includes a field for state. What I would...
1
3377
by: Zachary Turner | last post by:
I want to make a Lookup Field based on another Lookup field. In other words, I have this table A with two fields: ID and Name, where ID is an Autonumber and Name is a friendly name. Then I have a table B which links to A via a Lookup Field so that I can select the friendly name from combo box. Now, I want to have a table C which links to table B via a lookup field, and only displays the items which are selected in some combo box of some...
3
10681
by: google | last post by:
I have a database with four table. In one of the tables, I use about five lookup fields to get populate their dropdown list. I have read that lookup fields are really bad and may cause problems that are hard to find. The main problem I am having right now is that I have a report that is sorted by one of these lookup fields and it only displays the record's ID number. When I add the source table to the query it makes several records...
1
1908
by: Paul H | last post by:
Say I have a table called tblPeopleInfo, one of the fields in the table is called FavouriteFruit. The FavouriteFruit field is a lookup field and will contain Apples, Oranges, Grapes etc..The list can be added to by users of the database. What is the best way to construct this lookup? Should it lookup a Table/Query or a Value List? If it looks up to a Table/Query should the lookup table just have a single "text" field or should it have...
2
3152
by: Khers | last post by:
OK, I'm pretty new to access and I have this (maybe simple) question I have to tables Employee Computer employees has a employee autonumber as primary key adn Computer has a serialnr. as the primary key I have a many-to-many relationship between these table with another
11
2664
by: Paul H | last post by:
Suppose I have a table called tblPeople and I want a field to illustrate whether each person prefers cats or dogs. I could do it one of three ways. 1. A plain text field Create a text field in tblPeople called PreferredPet. 2. A lookup field that stores text values. Create a text field in tblPeople called PreferredPetID and use it to lookup an identical text field in tblPreferredPets.
2
1814
by: kandroski | last post by:
On 2/11/08 (Post #3), ADezii helped Robert Johnson with a many-to-many table question. I have the same question - I have a table called audiovisual item (VHS tapes or DVDs - similar to a book). An audiovisual item can have multiple performers (similar to authors), and a performer can be connected with multiple audiovisual items. I created a form which has the audiovisual item details at the top, with a subform for performers at the bottom. ...
0
9842
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9682
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10874
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10566
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10623
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9398
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5668
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5848
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
3124
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.