And now…The Layout for the Audits/Surveys
Working on converting 4 audits/surveys from paper to electronic. The field interface for the database will be iPads but additional work will be done at the user desktops. The tables are built and relationships established (see image). The only thing that is a little odd is the 1-1 relation between Audits_Common and Audits_Answers but it seems like a necessary oddity to properly "decode" the answers. The questions for all of the audits are almost ready to be loaded into the Questions table. The notion is that the user will go start on a layout that gives them various options including specifying which one of four types of audits they want to perform. The questions for each of the audit types are almost 100% unique and each audit type has a varying number of questions. Questions will be coded as belonging to a certain Audit_Type, the section of that audit they belong in (just for example say General, Equipment, Proceedures, on all of them and Materials and Facilities on only 2 of them), and their order within that section (1, 2, 3, etc). At the top of each audit will be the stuff from Audits_Common. After that will be the questions that pertain to that audit (83, 91, 45, or 25 depending on which audit).
I'm at a loss how to put it all together. Not sure if it would be better to base it off of a layout attached Audits_Common or Audits_Design or how to handle all the variables for pulling in the questions and laying them out. They will be skipping around as they do the audits so I can't just pop up a single question or section at a time.