Skip to main content

Looking for input and suggestions for the best approach. 

I am collecting information via surveys and interviews. Records are created for each survey response based on the respondents name. 

Based on that survey, I will then be interviewing a select number of these survey respondents. Is it better to have a separate table with notes about interview on a new table, or the same table.

 

If I use a new table, what would the primary field be? I’d still want my notes associated with the interviewees name, right? I initially had a formula for the primary field that used the linked record of the survey respondent i chose for the interview. Seemed clunky..

OR

Should I just add fields for Interviews in the same table and have a separate table view when needing to add interview data?

 

Notes: I already have another table that will essentially catalog responsibilities each respondent owns. This makes more sense because the primary field here is obviously the responsbility name, linked to the contact/survey table. I also have a sub table for tasks/sub responsibilities beneath that, again links cleanly to its psuedo parent table. 

Should I just add fields for Interviews in the same table and have a separate table view when needing to add interview data?

I’d say do this and rename the ‘Survey’ table to something that makes sense to you for handling both ‘Survey’ and ‘Interview’ data, like ‘Potentials’ or ‘Respondents’ or something.  If it’s a 1:1 link I usually just default to a single table and views

Hey ​@kingkhan,

What fields are you capturing for Surveys?
What fields are you capturing for Interviews?
I guess you have a Contacts table? I’m not sure.

Whether Contacts, Surveys and interviews should be one or multiple tables could depend on different factors.
1. Do you expect to have more than one survey per contact? Yes/No
2. Do you expect to have more than one interview per survey? Yes/No

If you answered Yes to question 1 above, then you will probably want to have a Contacts table, different to a Surveys table, as you would be able to link multiple surveys to a given Contact.

If you answered Yes to question 2 above, then you will probably want to have an Interviews table, different to your Surveys table, as you would be able to link multiple interviews to a given survey.

If a given contact will have no more than one survey and one interview, then a simple architecture with only one table might suffice. However, I personally do not love this. If your workflow changes in the near future, you will stumble upon several limitations.

You could have a Contacts table and an Events table. Events could be of different types. e.g. Survey or Interview. In this way, you could scale with multiple of each per Contact. Records for both types of Events would share common fields (e.g. Date).
If different types of event are really different objects/entities on your workflow (given substantial differences on their attributes) then you’ll want to split this up in two tables. One for Surveys, one for Intervies.

This video on Database Normalization does not exactly answer your question, but I do believe that you will learn a lot from it.

If you need any help setting this up, please feel free to grab a slot using this link. I’d be happy to show you around best practices :D

Mike, Consultant @ Automatic Nation 
YouTube Channel 

Terms & ConditionsAccessibility statement