This is for an educational application. The database has a bunch of
"subjects" such as vocabulary, geography, history, etc. Each subject
has a list of "facts" the user wants to learn.

Each subject may contain more data than a particular user wants to
study. The vocabulary subject may contains words for grades 6-12. The
user may want to study just the grade 9 words. The history subject may
contain items from 1800 to the present. A user may want to study just
1950 to 1960. So I need a way to create useful subsets of the
subjects.

A user may also want to combine subjects and study some vocabulary
words and some history facts at the same time. So I need a way to
create dynamic composite subjects.

The program needs to keep track of usage data for each item for each
user (#drills, #correct, etc.). For that, I need a unique id for each
item.

Here's my proposed design. I would appreciate any suggestions or
comments.

Define two kinds of subjects: physical and logical.

The physical subjects will contain the actual items. Each item will
exist in exactly one physical subject. Each physcial subject will be
given a unique ID and each item in each physcial subject will be given
a unique ID within that physcial subject. This gives me a unique ID
(SubjectID + ItemID) for every item in the entire database. When pared
with the UserID (UserID + SubjectID + ItemID), I get a unique ID for
tracking each item for each user.

The logical subjects will contain pointers to the items in the
physical subjects. For vocabulary, the physical subject might contain
7,000 words -- 1,000 for each grade from 6-12. I would then create 7
logical subjects -- one for each grade. The Grade 6 logical subject
would contain pointers to the 1,000 grade 6 words in the physical
subject.

The logical subjects might overlap. There might be an SAT logical
subject containing pointers to words that are also in the Grade 9, 10,
11, & 12 logical subjects.

Allow the user to select from a list of logical subjects. The user may
choose more than one logical subject. I would then create a dynamic
recordset containing all of the items (SubjectID + ItemID) from all of
the selected subjects (eliminating duplicates). This recordset would
exist for as long as the user continued studying.

I plan to allow the user to create their own logical subjects and save
them for future use. I would allow them to combine them however they
want to. This would result in a tree structure of logical subjects. If
a user selects a logical subject that is really a list of other
logical subjects, I would have the code chase those links and build
the dynamic recordset until the entire hierarchy is exhausted. The end
result would be a list of IDs (SubjectID + ItemID) with duplicates
removed. This wold be used to select the next item to study and to
keep track of the results.




There is one processing problem. The program will redrill words that
were missed. It knows this from the usage data which is stored under
(SubjectID + ItemID + UserID). If the user had previously studied the
Grade 8 words, but is now studying the Grade 9 words, we do not want
to select any words from the Grade 8 set unless they are also in the
Grade 9 set. My plan is to generate the intersection of the cueeent
logical subject and the usage database and use that recordset to
seklect the next item to drill. I think this will work, but am worried
about performance and memory usage. The logical recordset could be a
few thousand records.


I hope this is clear.



--