I would like to design an audit system for our department's intranet, please keep in mind, that I am not a programmer.
How I invision it is audit templates would be created, there would be questions that are asked and will always have a response of yes / no / NA, plus a small window to type note. If there is a no, it will likely also need a corrective action. There may be 1 question or 25, each audit will be different.
Then when the audit is created I would select one of the templates and create an audit for a user to perform. The responses need to be stored electronically. Again ir there is a "no" response, it will need an entry in another table for a corrective action.
Each template will be used many times.
Using a crude method I could create a table for each audit template, but that doesn't make it a very flexible system.
How do I create the tables for something like this? I'm using MS SQL.
Thank you for any tips on getting this started.
You say, almost apologetically, that you're not a programmer. No need. Design has little to do with programming! In any case, I think you have done a good job.
One may also use natural language in design, as you have done. My only suggestion is that you put each distinct point on a separate line, thus
Create audit templates (each audit will be different)
There would be questions that are asked (up to 25)
The responses are yes / no / NA
The responses need to be stored electronically
Plus a small window to type note
If answer is a no, state the corrective action needed
Each template will be used many times.
This design is sufficient as the basis of a flexible system. Now, on to the implementation of the design. This is where programming - in a language like Coldfusion or PHP - would likely offer a good solution.
One solution, for example, is to implement the design as an HTML form plus an MS SQL database. The form is then the user interface, the means by which the user interacts with your audit application.
The user enters information in the form, which Coldfusion or PHP may store in the database. The database holds tables pertaining to the various audit types.
You could do the implementation with just database tables. However, as you yourself have said the result wont be flexible.
If you are unable to write software programs, just hand the above design to a programmer. It will cost most programmers in any language little sweat to implement your form and database.
The "not a programmer" comment was there to prevent you really smart people from asking how many cfc's I want to use (not even sure what I just said) or what type of framework I employ.
I have developed an intranet for our department, I believe it's what "real" programmers refer to as spaghetti code. Each page is individually programmed. I read all three of Ben's WACK books and developed the site from there.
I can't figure out 1. how to create the audit template so that I can have multiple templates all with different numbers of questions and 2. how to then select one of these templates to answer, and have it be reusable many times.
Guidance for that would be greatly appreciated.
It is only a good thing that you can program. Nevertheless, as I said, forget about programming for the moment. Try to first flesh out the design. That is because a good design deals in abstraction, making it applicable to any programming language.
For example, we may start with your original idea of using a database. I can think of the following entities (database tables and their columns) in creating a database for your model
audit (auditID, auditdate)
auditLine (auditLineID, auditID, questionID, responseID, noteID, actionID)
question (questionID, question)
response (responseID, response, noteID, actionID)
note (noteID, note)
action (actionID, action)
You could interpret the relationship audit <-> auditLine as analogous to the relationship order <-> orderLine.