|
tech
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Addressing items in tables with similar functions, but dissimilar fieldsworking, 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. 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. Show quote "LurfysMa" wrote: > 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. >  |
|||||||||||||||||||||||
Other interesting topics