5 Replies Latest reply on Oct 25, 2013 2:19 AM by Mike_Mitchell

    Join Table or Add Fields to Existing Table?


      Forgive me if this question betrays a lack of experience:


      I have a customer database. On the customer form, there are tabs. One of those tabs will contain that customer's response to survey questions. There are quite a few questions -- say three dozen or so.


      I envision a scrollable list of responses inside the tab. I don't think this is a join table situation; the questions don't change from one customer to another. It seems that it might be easier simply to create a field in the customer table for every question, although I'm not sure how to get the responses to fit a long-narrow table in a scrollable window.



        • 1. Re: Join Table or Add Fields to Existing Table?

          What happens if you decide to change a survey question?




          In a situation like this, you're generally better served with a join table and a "library" table - a table that stores the default questions to be asked. Then, when a new survey is initiated, you import or otherwise create a new set of questions for that survey from the default set in the library table. Not only does this address the question of flexibility for future changes, it also means you can use a portal to address the scrollability issue.





          • 2. Re: Join Table or Add Fields to Existing Table?

            Some other things to keep in mind:


            If a person wants/needs to fill out those fields more than once, than the questions aren't an attribute of the person, but of the person's specific questionnaire.


            So, to extend on Mike's suggestion, you could create a Questions table with the "blueprints" and use those to create individual questionnaires, as in Person --> Questionnaire --> QuestionsQuestionnaire <-- Question.


            Also, it may be less a question of how to present the questions to the user and get your data into your fields, than a question of what you want to do with the data once it's in, i.e. how you evaluate the responses. In the above structure, QuestionsQuestionnaire is your "LineItems" table (so to speak) from which you can do all sorts of summaries.


            A simplistic example: say some of those responses are numeric, and you want to calculate the average. With internal fields, you need something like Average ( f1 ; f3 … f36), and adapt this formula whenever you add a new questions with numeric results. With a child table it's simply Average ( Questionnaire_numerics::response ), if the questions carry a numeric flag from their blueprint.


            A less simplistic example: say you want to weight some/all of the responses …

            • 3. Re: Join Table or Add Fields to Existing Table?

              Another challenge I face with questionaaire is if you allow the user to "vary" the question and answer. For example, if currently the question 3 is "Gender", so obvious answer is M/F (value list is used), what happenif the user change the question to "What is your Age?" which require a numeric field.

              • 4. Re: Join Table or Add Fields to Existing Table?

                Thanks. I'm just a bit confused on one point:


                Usually I'm accustomed to join tables being used for things like invoices, where only some items are chosen from a related table inside a portal. In this case, all items must be displayed by default. When creating a record, blank questions simply appear in the same order, all at a time. Is this problematic?

                • 5. Re: Join Table or Add Fields to Existing Table?

                  No, not at all. What you would typically do is add a field that indicates the sort order in the library table, which then carries over into each survey.