How to organize orthogonal and non-orthogonal subsets?

[LurfysMa]

I could use some help with a table design problem.

I have an electronic flashcard program. Actually, several of them.
They each rely on a utility program to keep track of the usage
statistics. After each practice session, the utility program tells the
flashcard program which items were learned and whihc need more
practice.

The drill stats are trracked by 3 indices: user, subject, and item.

For example, one of the subjects is the US States, which has
"categories" (subsets) for state capitals, date of admission to the
union, land area, etc. These subsets are orthogonal. That is, each
item is in exactly one subset.

Another subject is vocabulary words. The words are grouped according
to the test the user is studying for: SAT, GRE, GED, LSAT, etc. These
subsets are NOT orthogonal. Most of the words in the GRE subset are
also in the SAT subset.

The subjects with orthogonal subsets are no problem. I just assign
each subset a unique SubjectID and pass the tracking utility a list of
the subjects selected by the user.

The vocabulary subject, with its non-orthogonal subsets has me
stumped.

1. I cannot make eash subset into a subject and assign a unique
SubjectID because the utility program will track the same work in more
than onie place, which will screw up the statistics.

2. I cannot make them all part of the larger group because them the
utility program will not be able to tell them apart. If the user logs
on and asks to study the GRE set, having previously studied the GED
set, when the utility program reports back on which words need study,
it will get them from both groups.

I think I need to make the orthogonal subsets into subjects with
unique SubjectIDs, but call the non-orthogonal subsets something else
(logical subjects) and assign them a unique ID in a different address
space. When I report the results, I use the SubjectID. When I ask for
items that need to be redrilled, I use the other ID field.

Comments or suggestions?

Thanks

 

[Larry Daugherty]

A reply from tablesdesign couldn't find comp.databases.ms-access

To my Access centric thinking, your schema sounds correct. Assuming
that the other program is cast in concrete, you have to find a way to
get information to the other program in such a was that it will
interpret it and respond to with information you can correctly
interpret.

Please post back with more details:

List your relevant tables and fields.

It is of particular interest how you "assign each subset a unique
SubjectID and pass the tracking utility a list of the subjects
selected by the user".

Exactly what information is returned to you? How is it derived? Do
you have any control over it? What relevant configuration options do
you have in the other program (I really don't want to get too much
into analyzing that other program but put in everything you think is
relevant).

By the way, have you contacted the manufacturers of that other program
(give us its name) of the grief its limitations cause you. Maybe they
have some sort of "modal" switch you can pass it. If they put a
disclaimer in their product literature about this issue you are SOL
there.

Your solution may lie in the data you put together to send in order to
generate the correct results. If your schema is already correct it's
unlikely to involve altering the schema.

HTH
--
-Larry-
--

I could use some help with a table design problem.

I have an electronic flashcard program. Actually, several of them.
They each rely on a utility program to keep track of the usage
statistics. After each practice session, the utility program tells the
flashcard program which items were learned and whihc need more
practice.

The drill stats are trracked by 3 indices: user, subject, and item.

For example, one of the subjects is the US States, which has
"categories" (subsets) for state capitals, date of admission to the

union, land area, etc. These subsets are orthogonal. That is, each
item is in exactly one subset.

Another subject is vocabulary words. The words are grouped according
to the test the user is studying for: SAT, GRE, GED, LSAT, etc. These
subsets are NOT orthogonal. Most of the words in the GRE subset are
also in the SAT subset.

The subjects with orthogonal subsets are no problem. I just assign
each subset a unique SubjectID and pass the tracking utility a list of
the subjects selected by the user.

The vocabulary subject, with its non-orthogonal subsets has me
stumped.

1. I cannot make eash subset into a subject and assign a unique
SubjectID because the utility program will track the same work in more
than onie place, which will screw up the statistics.

2. I cannot make them all part of the larger group because them the
utility program will not be able to tell them apart. If the user logs
on and asks to study the GRE set, having previously studied the GED
set, when the utility program reports back on which words need study,
it will get them from both groups.

I think I need to make the orthogonal subsets into subjects with
unique SubjectIDs, but call the non-orthogonal subsets something else
(logical subjects) and assign them a unique ID in a different address
space. When I report the results, I use the SubjectID. When I ask for
items that need to be redrilled, I use the other ID field.

Comments or suggestions?

Thanks

 

[Larry Daugherty]

To my Access centric thinking, your schema sounds correct. Assuming
that the other program is cast in concrete, you have to find a way to
get information to the other program in such a was that it will
interpret it and respond to with information you can correctly
interpret.

Please post back with more details:

List your relevant tables and fields.

It is of particular interest how you "assign each subset a unique
SubjectID and pass the tracking utility a list of the subjects
selected by the user".

Exactly what information is returned to you? How is it derived? Do
you have any control over it? What relevant configuration options do
you have in the other program (I really don't want to get too much
into analyzing that other program but put in everything you think is
relevant).

By the way, have you contacted the manufacturers of that other program
(give us its name) of the grief its limitations cause you. Maybe they
have some sort of "modal" switch you can pass it. If they put a
disclaimer in their product literature about this issue you are SOL
there.

Your solution may lie in the data you put together to send in order to
generate the correct results. If your schema is already correct it's
unlikely to involve altering the schema.

HTH

 

[LurfysMa]

To my Access centric thinking, your schema sounds correct. Assuming
that the other program is cast in concrete, you have to find a way to
get information to the other program in such a was that it will
interpret it and respond to with information you can correctly
interpret.

Please post back with more details:

List your relevant tables and fields.

I don't have the tables worked out just yet. I'll work on it and post
them.

It is of particular interest how you "assign each subset a unique
SubjectID and pass the tracking utility a list of the subjects
selected by the user".

There are two databases: (1) the subjects database that has all of the
study material. (2) the tracking database used by the tracking
utility. In the subjects database, there is a master subjects table
with an autonum primary key. That's the unique subjectid. That id (or
list of ids) is passed to the tracking utility.

Exactly what information is returned to you?

The tracking utility returns a list of the items that need to be
redrilled.

How is it derived?

Simple statistics: number of tries, number right.

Do you have any control over it?

Yes, I wrote it. I have total control.

What relevant configuration options do
you have in the other program (I really don't want to get too much
into analyzing that other program but put in everything you think is
relevant).

I can change the tracking utility any way I want, but the interface it
the way I want it to be for other reasons.

 

[Larry Daugherty]

You need to be able to pass another term for the subsets you are
trying to isolate and the Utility side needs to receive it and may
need to respond with sufficient identifiers that the item report is
unambiguous. Those SAT, GRE, etc. subsets represent a very real part
of the hierarchy of your data.

The long and the short of it is that you need to be able to pass a
term for each level of the hierarchy on the sending side that you
intend to track. The receiving side needs to be able to handle them
all and to respond in kind or at least in a way that is unambiguous to
the database you are creating.

All the rest is the details of getting it done. Implicitly, the bits
of the solution lie on each side of the current interface. I don't
know your complete application nor your reasons for wanting to leave
the existing interface alone.

Please post back with what you decide. If it's a long time in the
future, decode my email address and drop me a line. I am interested.
As I write this I should be busy instead working on a Test Generator
application for my daughter.

HTH