Addressing items in tables with similar functions, but dissimilar fields

[LurfysMa]

I am workng on a little database application. I have Version 1
working, but ran into a problem. I think I have a solution, but a
friend, who is also more or less a beginner with databases, disagrees
with me on one of the design considerations. I would appreciate the
opinions of more experienced database engineers.



I am going to start with a little background. My question is at the
bottom of the post. I felt that the background was important to show
how I got to where I am now. I hope I didn't provide too much
information.

In this post, I am using the terms "record" and "row" interchangeably
and also "column" and "field".

The application is an electronic flashcard program. The plan is to
have a collection of "subjects" like State Capitals, State Flags,
Trivia, Multiplication Tables, Vocabulary, etc. Each subject is a
collection of "items" (the flashcards).

The user can select which subjects they want to study. The program
will drill the items in those subjects and keep track of how each user
does (#tries, #right). It will keep drilling the items until the user
gets to some level of competence (90%, 95%, 99%).



Version 1: My original plan was to have 4 tables: a Subjects table, an
Items table, a Users table, and a Stats table.

The Subjects table (tblSubjects) would have one record for each
subject. It might look like this:

tblSubjects
Field Datatype Description
SubjectID Long Primary key (autonum)
Name Text Subject name to show the user
IsReversible Yes/No Yes = items can be drilled in reverse
??? ??? Any additional fields as may be needed

An example is:

tblSubjects
SubjectID Name IsReversible
1 State Capitals Yes
2 Trivia No
3 Vocabulary No
4 Multiplication Tables No

The Items table would have one record for each item (in any subject).
It might look something like this:

tblItems
Field Datatype Description
ItemID Long Primary key (autonum)
SubjectID Long Link to tblSubjects
Question Text The question side of the flashcard
Answer Text The answer side of the flashcard

An example is:

tblItems
ItemID SubjectID Question Answer
1 1 Alabama Montgomery
2 1 Alaska Juneau
3 3 inveigh To verbally attack
4 3 mettlesome Courageous
5 4 3x4 12
6 4 7x8 56

The Users table would have one record for each user and might look
something like this:

tblUsers
Field Datatype Description
UserID Long Primary key (autonum)
Name Text The user's name and logon ID
Password Text The user's logon password

An example is:

tblUsers
UserID Name Password
1 Alex Trebek ********
2 Ken Jenings ********
3 Herb Stempel ********
4 Charles Van Doren ********

The Stats table would keep track of the drill results and would look
like this:

tblStats
Field Datatype Description
UserID Long Link to tblUsers
ItemID Long Link to tblItems
NumDrills Number Number of drills for this user for this item
NumRight Number Number right

An example is:

tblStats
UserID ItemID NumDrills NumRight
1 1 5 4
1 7 2 0
2 1 1 1
3 9 2 2
3 11 7 8

This worked fairly well. I had a few simple subjects with a few items
in each. Then I started expanding the data and adding subjects and ran
into problems with the design. Please feel free to comment on the
above design, but I am more interested in the Version 2 design, below.



Version 2: I am looking at a different database design.

For the Vocabulary subject, I wanted to add fields for part of speech,
usage examples, pronunciation guide, origin of the word, synonyms, and
antonyms. For the State Capitals subject, I wanted to add a comments
field giving the history of the city and any previous capitals.

Then I needed different data types. For the multiplication tables
subject, I wanted to replace the question and answer fields with 2
multiplicand fields and make them numeric so I could ask the question
in different ways (3x4 v 4x3). For Vocabulary, I wanted to replace the
part of speech field with a lookup table and the pronunciation guide
with an audio clip actually speaking the word. When I added the State
Flags subject, I wanted to insert a graphic image.

It became clear to me that one Items table for all of the subjects
would not work. I would need way too many fields and most of them
would not be used by any one subject.

I decided to give each subject its own Items table. The individual
Items tables could be custom designed for that subject. If two or more
subjects happened to have very similar formats, we could combine them
later.

The problem, now, is how to address the items. The program still needs
to keep track of the drill stats for each item for each user. Now that
the items are in separate Items tables, they no longer have a unique
ItemID. Since they do have a unique ItemID within each Items table and
each subject has a unique SubjectID, the solution seemed to be to use
a 2-field ID: SubjectID + ItemID. The drill stats table now needs 3
fields to track each item for each user: UserID + SubjectID + ItemID:

tblStats
Field Datatype Description
UserID Long Link to tblUsers
SubjectID Long Link to tblSubjects
ItemID Long Link to tblItems
NumDrills Number Number of drills for this user for this item
NumRight Number Number right

An example is:

tblStats
UserID SubjectID ItemID NumDrills NumRight
1 3 1 5 4
1 2 1 2 0
2 5 1 1 1
3 1 9 2 2
3 1 11 7 8

Question #1: How do I associate the items with the subjects?

In Version 1, there was just one Items table and it had a SubjectID
field to associate each item with a subject.

In Version 2, every subject has its own Items table. Every item in the
table belongs to the same subject. It seems redundant to me to retain
the SubjectID field. It would have the same value for every item in
the table.

My solution is to add a field to the Subjects table for the name of
the associated Items table.

For example:

tblSubjects
Field Datatype Description
SubjectID Long Primary key (autonum)
Name Text Subject name to show the user
ItemsTable Text The name of the Items table
IsReversible Yes/No Yes = items can be drilled in reverse
??? ??? Any additional fields as may be needed

An example is:

tblSubjects
SubjectID Name ItemsTable IsReversible
1 State Capitals tblStateCapItems Yes
2 State Flags tblStateFlagItems Yes
3 Trivia tblTriviaItems No
4 Vocabulary tblVocabIgtems No
5 Multiplication Tables tblMultTablesItems No

This solution requires that some of the queries be generated
dynamically, but it seems like a reasonable solution. If anyone can
see some reason why this won't work or has a suggestion for a better
approach, I would appreciate hearing it.

Each Items table can now be customised for that subject. Here are some
examples:

tblStateCapItems
Field Datatype Description
ItemID Long Primary key (autonum)
State Text Name of the state
Capital Text Name of the capital city

tblTriviaItems
Field Datatype Description
ItemID Long Primary key (autonum)
Question Text The trivia question text
Answer Text The answer text

tblMultTablesItems
Field Datatype Description
ItemID Long Primary key (autonum)
Factor1 Number One of the factors
Factor2 Number The other factor

tblVocabItems
Field Datatype Description
ItemID Long Primary key (autonum)
Word Text The vocabulary word
Definition Text The definition
POS Number Link to POS table
ProGuide Text Pronunciation guide
ProAudio Text Fileid of audio clip
Origin Memo Origin and history of the word
Usage Memo Usage examples

tblStateFlagItems
Field Datatype Description
ItemID Long Primary key (autonum)
State Text Name of the state
Flag Text Name of the image file

The Stats table needs the SubjectID field to track the usage stats. It
would look like this:

tblStats
Field Datatype Description
UserID Long Link to tblUsers
SubjectID Long Link to tblSubjects
ItemID Long Link to tblItems
NumDrills Number Number of drills for this user for this item
NumRight Number Number right



Question #2: Do I need a SubjectID field in each Items table?

My friend agrees with my TableName field idea, but still wants to keep
the SubjectID field in every Items table. He keeps saying that we will
need it if we ever need to join the Items table with the Subjects
table, but I cannot see why we would ever need to do that. In any
case, we could easily add it if we do. To me, it is redundant. In each
table, the SubjectID field would have the same value for every item. I
cannot see how that adds anything.



Again, I am sorry for the length of this post.

I would very much appreciate any comments or suggestions.

 

[Cheese_whiz]

Hi LurfysMa,

Since no one else jumped on this, I'll at least off a little feedback....

You seem to be thinking in terms of the data about each subject being stored
in the same table(s) as the questions/answers used for the 'quiz' portion of
the application. I'd suggest you at least think about that a little more.

It seems to me that storing the data itself is a very different goal than
creating questions about the data, and probably shouldn't be accomplished via
the same tables. I could easily see all the questions and answers stored in
the same table (one table for all subjects) which would include the subjectID
as a field to be used to relate the questions to subjects. That would allow
you to break down right/wrong answers by subject in reports, for example.

How you would go about storing the actual data about each subject is an
entirely different matter.

What I would REALLY suggest is you do a little googling on POWERPOINT flash
cards. I think Powerpoint might give you a much better platform to focus on,
possibly with the inclusion of a simple excel sheet or two.

I would never go so far as to say what you are trying to accomplish can't be
done in Access. I'm a firm believer in the ability of Access to accomplish
virtually any goal to some degree or another when it comes to storing and
manipulating data. That certainly doesn't mean it's the best tool for the
job, though.

Bottom line: I'd look into powerpoint for this project, but I certainly
could be wrong.

 

[LurfysMa]

Hi LurfysMa,

Since no one else jumped on this, I'll at least off a little feedback....

You seem to be thinking in terms of the data about each subject being stored
in the same table(s) as the questions/answers used for the 'quiz' portion of
the application. I'd suggest you at least think about that a little more.

Let's make sure are talking about the same thing. I have two tpes of
data: (1) the question-and-answer data (the flashcards) and (2) the
usage data (the number of right & wrong answers).

These two types of data will be kept in completely separate tables. In
the current implementation, they are in separate databases and are
managed by separate VB6 applications.

It seems to me that storing the data itself is a very different goal than
creating questions about the data, and probably shouldn't be accomplished via
the same tables.

I could easily see all the questions and answers stored in
the same table (one table for all subjects) which would include the subjectID
as a field to be used to relate the questions to subjects. That would allow
you to break down right/wrong answers by subject in reports, for example.

I tried to do this in Version 1 and failed. As I tried to point out in
my examples, the data formats vary widely from subject to subject.
Putting all of the Q&A data in one table is not feasible.

How you would go about storing the actual data about each subject is an
entirely different matter.

Storing the usage data (right/wrong) is fairly easy. My main question
was about the need for the SubjectID in the Items tables if every
subject has its own Items table.

What I would REALLY suggest is you do a little googling on POWERPOINT flash
cards. I think Powerpoint might give you a much better platform to focus on,
possibly with the inclusion of a simple excel sheet or two.

Powerpoint? You're joking, right?

First of all, Powerpoint flashcards are meant to be printed. The whole
point of my project is for the flashcards to be digital. Even if I
keep them in Powerpoint, there is no good way to access them randomly.

I would never go so far as to say what you are trying to accomplish can't be
done in Access. I'm a firm believer in the ability of Access to accomplish
virtually any goal to some degree or another when it comes to storing and
manipulating data. That certainly doesn't mean it's the best tool for the
job, though.

I may not end up using Access, but I am pretty sure this is a database
application, not a Powerpoint application.

 

[Cheese_whiz]

Hi LurfysMa,

I realize my previous answer wasn't exactly what you were looking for in
response. That's why I waited to post it.

Specifically to your second question about whether or not you need to
include the subjectID in the records of the items table even if there are
separate items' tables for each subject, the answer is YES.

It's not a matter of relating one table to the other, even though that's how
we talk about it. It's a matter of relating the records of one table to the
records of the other table, which means you have to have some common field
between the two. In a 'one-to-many' relationship, it's usually a matter of
adding the primary key of the 'one' side of the relationship as a 'foreign
key' (just a term) field in the 'many' side of the relationship. In other
words, you add the subjectID field to the items table and that's how Access
knows which items go with which records in the subject table.

Am I serious about PowerPoint as an alternative? Yes, I am. I think if you
did a little looking around via google, you'd get serious about it too. In
about a minute's worth of research I found NUMEROUS sites with information
about creating PP flash cards including a site with multiple examples of how
to randomize slide order.

If I'm not mistaken, I have used commercial software for review exams in the
form of powerpoint presentations. That I wouldn't swear to, but I do know
that it's out there (whether my course used it or not).

So, yes, I'm serious about it.
CW