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.
George_Hepworth Thank you George. I have been trying to understand Normalisation for some time and who would have thought today would be the day the penny dropped. Roger's explanations make you wonder how on earth did I not get that - very good blogs. So, I now have this...
I don't think I can reduce the content of the Evidence table as each entry (56) is going to be unique from audit to audit.
tblEvidence is not normalized and does need further work. It is still composed of Repeating Columns in which the field name encodes data: E4-1, E4-2 and so on. Whenever you see the same name with a suffix, that's a signal it's not yet right.
Given that I don't know the subject matter, it's not all that easy to know what the various fields represent, but I'm hypothesizing that these are supposed to represent some form of evidence to support a conclusion in an audit? That should be two fields, if so.
But there are other things that need to be revised as well.
Your organization conducts audits of something. The only fields that need to be in tblAudits are characteristics of an audit. I think, based on that, tblAudit should have
AuditID (Primary Key, AutoNumber)
AuditNo -- a text value used to identify the Audit for users
AuditName -- a text value
AuditTypeID -- a Foreign Key to a table of AuditTypes
Comments -- a text value
I think, based on a superficial understanding, that neither ClauseID or EvidenceID belong in the audit table. I'm basing that on thinking that each "Clause" represents some standard measurement by which an Audit is evaluated. If there are a number of standard Clauses which can apply to one or more audits, then there is a third table required. If each Clause is unique to an Audit, the Clause table can be in a one-to-many relationship with an Audit, i.e. each Audit consists of one or more Clauses.
In that case, the Clause table would be:
ClauseID (Primary Key, AutoNumber)
AuditID -- Foreign to to the Audit for which that Clause was created
Clause
ClauseGuide
ClauseComments
ClauseAuditor, etc.
The relationship is NOT on ClauseID, because ClauseID does not belong in the Audit table. It is on AuditID (PK in tblAudit to FK in tblClause). Also, don't forget to enforce Referential Integrity on all relationships.
If there are a set of Standard Clauses which can be applied to one or more Audits, then the third table must be introduced between tblAudit and tblClause. It is usually referred to as a junction table. It contains:
AuditClauseID (Primary Key, AutoNumber)
AuditID -- Foreign Key to the Audit table
ClauseID -- Foreign Key to the Clause table
Some of the other fields now in tblClause would also move into this junction table, such as comments, auditor, auditee and date on which the clause was audited. Again, enforce Referential Integrity on the relationships.
I don't know enough to be clear on how SubClause fits in. And even less clear on what the role of evidence is. Once again, though, EvidenceID is clearly not related to the audit. It's related to the subclause, perhaps.
Keep working at it.
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.
- Whenever you see field names that consist of a term + sequence number, you know that is not normalized. i.e. Clause7Date, Clause8Date or Clause9Audtior, Clause 10Auditor.
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.