Please critique this table design

[LurfysMa]

I have a database containing question-and-answer data for multiple
subjects. It's for an electronic flashcard program. I need to keep
track of the results (right/wrong) for every user for every question
for every subject.

The subjects themselves come in several flavors.

The simplist is a stand-alone list of questions and answers. Two
examples are the mutliplication tables and vocabulary.

Here's my table design and some sample data for these two subjects. I
have shortened the field names so they will fit.

tblTimesTables

Field Type Comments
QID Long PK, autonum
Que Text The question
Ans Text The answer

Sample data:

QID Que Ans
1 1x1 1
2 2x1 2
3 2x2 4
.. . .
66 12x12 144


tblVocabulary

Field Type Comments
QID Long PK, autonum
Que Text The question
Ans Text The answer (maybe memo)

Sample data:

QID Question Answer
1 anonymous Of unknown authorship.
2 antemeridian Before noon.
3 abase To lower in rank, prestige, or esteem.
.. . .


Assuming that's all OK, now I need a table to keep track of the
subjects. Here's what I came up with:

tblSubjects

Field Type Comments
SID Long PK, autonum
SName Text Subject name (for titles)
TblName Text The name of the subject table
IsPrivate Yes/No Yes=private, by permission only
IsEnabled Yes/No Yes=enabled

Sample data:

SID SName TblName IsPrivate IsEnabled
1 Times Tables TimesTbl No Yes
2 Vocabulary Vocab No No


Is this a decent design?

My main question has to do with the TblName field. I need way to
locate the table were the questions and answers are stored. Is there a
better way?

Thanks

 

[David F. Cox]

Unfortunately it is not the simplest example that defines the best answer,
but the most complex. In the example given you could just have one table
with a another text field to define subject type - e.g. "Vocabulary", "Math"

 

[LurfysMa]

Unfortunately it is not the simplest example that defines the best answer,
but the most complex. In the example given you could just have one table
with a another text field to define subject type - e.g. "Vocabulary", "Math"

I suppose I simplified the example too much. I was trying to eliminate
clutter that was not relevant to the question.

The vocabulary table already had 2 additional fields:

tblVocabulary

Field Type Comments
QID Long PK, autonum
Que Text The question
Ans Text The answer (maybe memo)
POS Lookup Part of speech (noun, verb, etc.)
Example Memo Example of word usage

There are many other subjects, most of which have several unique
fields. I didn't want a table design that had every field that was
needed by every subject and then have most of them unused for a
particular subject.

Do you still think all of the subjects should be in a single table?

If not, how would you handle identifying the name of the individual
tables in tblSubjects?

 

[mscertified]

I would have all the question/answer data in one table with different 'row
types'.
You could set up a query to extract the data for each 'row type'. Don't
worry about unused columns, if there is no data in there overhead is minimal
so long as the total number of columns is kept reasonable. You can even have
one column do 'double-duty' for different row types.
ID
RowType
Question
Answer

There may be a better answer but I can't think of one right nopw.

Dorian.

 

[LurfysMa]

I would have all the question/answer data in one table with different 'row
types'.
You could set up a query to extract the data for each 'row type'. Don't
worry about unused columns, if there is no data in there overhead is minimal
so long as the total number of columns is kept reasonable. You can even have
one column do 'double-duty' for different row types.
ID
RowType
Question
Answer

Again, I probably simplified the question too much.

I expect to eventually have dozens if not hundreds of subjects. Each
of them is likely to have one or more custom fields. Some of them
don't even have standard Question fields, but links to other tables.

So I would have a table with upwards of 100 fields

Are you really advocating using one field for double-duty? That
strikes me as heresy. No? Wouldn't the code become a lot less clear or
cumbersome? What would I name the field? Type_POS_Level_SomethingElse?

I actually had it with one common table and it was a mess. That's why
I am looking for another approach.

What I would really like is an answer to my main question: Is storing
the name of a table in a field a bad idea? If so, why? And, what
alternatives are there?

Thanks

 

[John Vinson]

What I would really like is an answer to my main question: Is storing
the name of a table in a field a bad idea?
If so, why? And, what alternatives are there?

I'd suggest that you have a good case for (a fairly elaborate instance
of) Subclassing here - one of the few reasons to use one-to-one
relationships. You could consider (and may be considering!) using a
master Questions table with one record for each question, related
one-to-one to many QuestionDetails tables; you'ld have a specific
table for vocabulary questions, another for math questions, and so on.
The fields common to all questions would be in the master questions
table, and you'ld use a one-to-one query join to pick up the specific
fields for each type of question.

The master table should probably have either the tablename, or
equivalently the type of question. You would need some VBA code to use
that information to dynamically create the one-to-one relationship
query though, I don't believe you could pick up the fieldname in a
query and have it automatically generate the SQL for the link.

John W. Vinson[MVP]