7 Replies Latest reply on May 5, 2017 3:22 AM by fmpdude

    Database modeling question


      I am a newbie so please have mercy on me.


      I want to score students' tests using FileMaker.  Here is what I envision:  Import student responses from a test with 200 questions and then compare the students' responses against the master answer list.  There are different master answer lists for different tests.


      I am assuming that a master answer list should be a record in a table of just master answer lists.  For example, if there are 15 different versions of the test, there should be 15 records in this table, each representing an answer key for a different test.


      I am also assuming that students' responses should be kept in a different table with similar fields as the table described above.


      Here is my question:  How do i compare a record from the student's response with the record from the master answer list, if the data is in different tables?

        • 1. Re: Database modeling question

          The only real difference between a questionnaire and a test is that tests have correct answers. So you can search this forum for posts on Questionnaires and Surveys to find descriptions of data models you might use. These examples have a table of questions where each record represents one question. A field in this table could record the correct answer for that question--unless you want to set up multiple choice questions where the question is the same, but the order of the possible answers differs in different versions of the test.

          I am assuming that a master answer list should be a record in a table of just master answer lists.  For example, if there are 15 different versions of the test, there should be 15 records in this table, each representing an answer key for a different test.

          Well almost. Yes you'd have that table of 15 records, but a related record would list the individual questions that link to each test and this can be either a one to many from test to questions if you never use the same question record on more than one test or it can be many to many with a join table if you want to use the same question one more than one test without duplicating the record in your table of questions.

          • 2. Re: Database modeling question

            Thanks Phil... I'm sorry but that's where newbie part comes in... I guess that is my question.  As long as the tables are related, can I compare different records in different tables? If so, how?


            For examples, I have two tables (for the simplicity, I'll assume that each test has 5 questions). The first is the table that holds the answer key for four different tests.



            The second table contains the data from students responses.  Let's assume that all the students took Test #203.


            How can I compare the responses from the tables?  Will FileMaker let me do that if the tables are related? I want to compare Q1 from the second table to Q1 in the first table from the second record. How do I do that?


            Thanks for your help...

            • 3. Re: Database modeling question

              My point is that you should not set up your tables like this. Please research the topics that I recommended.


              It's one record to a question but all linked to a parent "test" record.


              Tests----<Questions-----<StudentAnswers>-------Students   (----< means "one to many)


              Tests::__pkTestID = Questions::_fkTestID

              Questions::__pkQuestionID = StudentAnswers::_fkQuestionID

              Students::__pkStudentID = StudentAnswers::_fkStudentID


              The correct answer to each question would be a field in Questions. A student's answer would be a field in StudentAnswers. You can compare the student answer to the answer key via the QuestionID.

              • 4. Re: Database modeling question

                My *personal* first notion is to have a table for each test, with a flag field to mark whether a record in that table is the answer key. Advantage of this is you're not duplicating field sets for the different tests.


                I usually play a lot of "what if" in my planning. For example:

                - What if I'm doing manual entry? How will the scoring script trigger?

                - What if I'm importing a batch? Best scoring trigger that route?

                - Are skipped questions scored the same as an incorrect response?

                - Do you need to report on the individual right/wrong values, or only a total?

                - What happens in 6 months when they drop one question on a quiz and add two others. Will it keep the old scores static, or attempt to update them with revised values?

                - What demographics might you want to track in the Students table for cross-tabs?

                - What attributes do you need to include for the tests, and for each administration of the test? (Date, instructor, etc.)


                Try to create a detailed picture of how the system will be used both today and in two years. (You don't have to code everything for the latter, but you don't want to do something that will have to be ripped out and replaced either.)


                There are many right paths to an application, and most involve trade-offs. HTH. :-)

                • 5. Re: Database modeling question

                  I agree that you need a table of questions and answers related to a parent Test Record as philmfdjunk suggested.


                  This allows you to easily build new tests as needed and also allows you to use the same structure for tests in different subjects or semesters. No limits this way on the number of questions either. You can have a 5 question quiz or a 50 question test using the same structure.


                  Like annr suggested it is always good to start with what you want out of it in the end and work toward that.


                  How are you importing responses? How are you collecting responses?

                  • 6. Re: Database modeling question

                    Thanks Phil. I have gone through the FileMaker Basics lesson on Lynda.com but I guess I'm still struggling with the data modeling concept.  I'm still thinking of it in terms of a spreadsheet and not a database.


                    What you say makes a lot of sense. I think I need to find some more advanced videos on data mapping.


                    Thanks for for your help. I'll try what you suggested in terms of the different parent and child relationships.

                    • 7. Re: Database modeling question

                      Get ERD software, too, so you can model a database the "database way".


                      (Modeling happens before you create tables and fields in FMP.)


                      For FMP, the best tool I've found is SQL Editor. (Note: many DB tools have ERD capabilities built in.)


                      SQL Editor will let you visualize and define your relationships.


                      SQLEditor for Mac OS X - Buy


                      SQL Editor will also connect to your LIVE FileMaker database.


                      And, after you edit your ERD, SQL Editor will let you post back (update) the FileMaker database!


                      And finally, SQL Editor will work with just about ANY database, not just FMP.

                      ERD software is an essential part of creating a database, but it's really not covered in depth here.


                      Finally, an ERD is the WAY you communicate your design to other team members, to yourself, or to your customers. An ERD is always in any proposal I've ever submitted.


                      Thus, an ERD is not just a programmer's tool. Stakeholders in every realm understand them.


                      ERD Tools are  DB 101


                      Doesn't the diagram below look obvious when diagrammed?


                      1 of 1 people found this helpful