5 Replies Latest reply on Jun 30, 2014 8:54 AM by philmodjunk

    Help with setting up tables

    WIlliamCopp

      Title

      Help with setting up tables

      Post

           Ill try to keep this simple.

            

           I am creating a FM Go "app" that I can use to test out different conference rooms we have in our office - One main portion of it will be a database of all the equipment with setup info / manuals / models / ID tags / etc. That is working out ok. 

            

           The other part is being able to click on a particular room and have it bring up all eligible questions that apply to that room. 

            

           I started with a Rooms table with info like seating, Room ID (which is also related to individual equipment IDs so it can pull a list of all equipment in the room). 

           The other table would be for questions - I do not necessarily need to log the individual answers to these questions as they are really few and far between and I would just make a table to log incidents with details in there. So the table would be something like

            

           Questions

           -Question Text

           -Room ID

            

           The issue here is that one question can be in more than one room. I am trying to make a pool of about 30-40 questions that many rooms may use the same one, some may only use a few, some may use all 40. I am stumped on how to set up my relationships this way. Any help would be greatly appreciated. 

        • 1. Re: Help with setting up tables
          philmodjunk

               This sounds very similar to a questionnaire type database but with rooms taking the place of "respondents". Perhaps an adaptation of the data model outlined here can help: Need aid on generating a report from a survey layout.

          • 2. Re: Help with setting up tables
            WIlliamCopp

                 That is helpful, thank you. But the part you wrote about "You may or may not need a Surveys table. Adding one allows you to process more than one survey--each with it's own group of related records in the Questions table." doesn't elaborate much. 

                  

            The way I am thinking it in my mind is a field in the Questions table called "Rooms applicable" and then ideally it would just be 1,2,3,4,5,6 the next one could be 2,4,5,6 

                  

            then when going to a testing layout and choosing room 2, it would show both questions. Where are room 3 would only show the first question. Is it that simple? can it just search the contents of a field? Another option I was thinking was creating different fields for each room - there will probably be about 10, and just flagging Y or N for each field so when you choose a particular room it only searches for questions with Y in the Room1 field. 

                  

                  

            • 3. Re: Help with setting up tables
              philmodjunk

                   The purpose to using a Surveys table is so you can associate different groups of questions with different surveys. That sounds very much like what you need here. For a given room, you can link it to a specific "survey" record in order to select the applicable set of questions for that room.

                   The "rooms applicable" field won't work all that well. You could use a list of Room IDs separated by returns as a match field, but if you want to make the same Question record part of more than one group of questions, (a question used in more than one "survey"), it would be generally a better idea to use a join table instead of a return separated value field in order to set up the needed many to many relationship.

              • 4. Re: Help with setting up tables
                WIlliamCopp

                     I totally agree the survey table is what I am looking for, the question I have is setting it up, what would be the pk and fk fields between surveys and questions? How would I make it so one particular survey pulls "x" questions and the other pulls "y" questions. 

                      

                     Thanks for your patience, this is all new to me. 

                      

                     I would also like to add I am aiming for this to be automated. Lets say we get a new piece of equipment in a bunch of rooms and I want to add the questions "Does this device turn on and do what it is expected to do" - I then want to be able to somehow simply flag that question with the rooms it should appear in, without having to go and alter survey lists and what not. 

                      

                     Ideally I would have a layout view with each individual room - this would then have three tabs - one for equipment in that room - one for testing questions - one for incident logging. All I want the testing questions to do is to have a portal that simply lists all applicable questions, there is not really a necessary need for logging these answers right now. 

                • 5. Re: Help with setting up tables
                  philmodjunk

                       what would be the pk and fk fields between surveys and questions?

                       I believe that this first part is documented in the other thread:

                       Surveys::__pkSurveyID = Questions::_fkSurveyID  

                       That sets up a one to many relationship. If you want the same question record to be linked to more than one survey record, you can use a join table and set up this relationship:

                       Surveys::__pkSurveyID = Survey_Question::_fkSurveyID
                       Questions::__pkQuestionID = Survey_Question::_fkQuestionID

                       Typically, one uses a portal to Survey_Question on the Surveys layout to select existing question records in order to link them to the current survey record by creating new records in the Survey_Question join table.