Forum Discussion
Indexing and Duplicates
I found a couple of hours finally to take a closer look. I am probably not completely right about some things because I'm not familiar enough with the work involved. That said, this diagram
represents my best understanding.
I know that you indicated the Auditor and Auditee are both related to the Audit itself. However, in the tables you had in the previous screenshot, it appeared to be the case that each subclause can be handled by a different auditor. That's how I implemented it here. If the Auditor is, indeed, assigned to an audit and must individually complete each of the subclauses personally, you can move the AuditorID up into TBLAudit.
It also looked to me to be the case that the same Evidence is applied to each subclause, regardless of the audit. If that's not the case, i.e. if each audit can attach different evidence to subclauses for different auditees, that would have to be moved down in the Audit_AuditSubClause table.
Yes, I see that and have done that now with Referential Integrity enforced. To help me understand normalising tblEvidence, I should give you further information as I cannot think how I would do that. The full audit is based upon 7 main clauses (4 to 10). Each clause has subclauses as shown below:
My original relationship of tblAudit to tblClause, I think was wrong - it should have been to tblSubClause. The tblClause requires Auditor, Auditee, Date and Comments for each clause which as you can see below needs normalising.
Again, I know it can be normalised and I'm working on how to do that now.
Your help on normalising tblEvidence would be great, I'm starting to understand normalising and where and why I should doing it, but sometimes I can't think how to do it - tblEvidence is one of those times. Fields beginning with E are for the auditor to input the evidence (or lack of it) against each subclause and there are also fields beginning with C (C4.1, C4.2, etc) which are to record the compliance status against each subclause which is drawn from a combo box (Yes: Meets the standard, No: Minor non-conformance, etc).
Thanks again.
I'm still trying to come to an understanding of the actual work flow, but my time today is fairly limited, unfortunately.
There is no one-to-many relationship directly between Audits and Clauses, at least as I understand it.
Each Clause can be used in one or more Audits. Each Audit includes one or more Clauses. Correct?
If that is correct, you have a many-to-many relationship between audits and clauses. That requires a Junction table, which includes foreign keys for the audit (AuditID) and the clause (ClauseID). There will be one record in this table for each audit and clause.
Also, Evidence doesn't have a direct relationship with the audits. Remove that Foreign Key for AuditID from tblEvidence. I also think there is no relationship between Evidence and a clause; that relationship is between Evidence and a SubClause. Remove that ClauseID foreign key from tblEvidence as well. And finally, I am fairly sure -- assuming I do understand well enough -- that the relationship between a subClause and Evidence is handled in a fourth table, not currently shown here.
I think that this is probably one of the more complex relationships and for that reason it is particularly challenging for a starting project. I'm still not sure I even understand how the workflow goes. My apologies, but I probably won't be back until late today. In the meantime, there are others reading the posts here. Hopefully there will be someone available to help as well.
I do appreciate your guidance on this George and apologies for monopolising your time. I'll spend more time learning more about normalising as when I think I've got it, you (quite rightly) point out that haven't quite got it.
This is an ISO9001 audit whose flow is like this:
Each audit consists of 7 main clauses. These will be the same for every audit.
Each main clause requires and auditor, auditee, date and comments fields. The data for these fields will be different for each audit.
Each main clause has between 3 and 5 subclauses. Again, these will be the same for every audit.
The subclauses do not require auditor, auditee, date and comments fields as these are captured by the appropriate main clause.
Each subclause requires proof or lack of evidence as a long text field.
Each subclause also requires a fixed status as described earlier - Yes: Meets the standard, No: Minor non-conformance, etc.
Hope that helps.- Thanks for the background info. It frames the project in a very useful way.
I'm sure that someone will be looking in again soon. I will try to do so later or tomorrow. It's a bit of a frantic day for me, unfortunately.I found a couple of hours finally to take a closer look. I am probably not completely right about some things because I'm not familiar enough with the work involved. That said, this diagram
represents my best understanding.
I know that you indicated the Auditor and Auditee are both related to the Audit itself. However, in the tables you had in the previous screenshot, it appeared to be the case that each subclause can be handled by a different auditor. That's how I implemented it here. If the Auditor is, indeed, assigned to an audit and must individually complete each of the subclauses personally, you can move the AuditorID up into TBLAudit.
It also looked to me to be the case that the same Evidence is applied to each subclause, regardless of the audit. If that's not the case, i.e. if each audit can attach different evidence to subclauses for different auditees, that would have to be moved down in the Audit_AuditSubClause table.