Forum Discussion
Solved
Indexing and Duplicates
Hi
I don't need help resolving a problem on this occasion, but I would appreciate some help in understanding Indexing and Duplicates. I have a main Audits table plus 7 clause tables. They are related through the clause AuditID. The FK AuditIDs in the main table I would have thought should be indexed with no duplicates (that's what makes sense in my head). But if I do that, the query becomes not updateable.
I would have thought that having no duplicates in the main table was the correct thing to do. Or is there something I am completely missing here?
Thanks.
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.
10 Replies
There is an even more fundamental problem in this database, as shown in the screenshot.
You have replicated Excel spreadsheets rather than creating appropriate Access tables. The current problem with indexing is in addition to that flawed relational database design.
You have many "Clause" tables, each with data encoded in the table names, for example. There should be ONE Clause table, with a field in the table to indicate to which clause a record applies.
And within the tables, you have multiple Repeated Columns, again with data encoded in the table names. For example, "XXAuditID", where XX refers to a two-digit number.
Here are some links to a series of blog articles describing this, all-too-common, design flaw and how to fix it.
https://rogersaccessblog.blogspot.com/2011/03/the-problem-of-repeated-columns.htmlhttps://rogersaccessblog.blogspot.com/search/label/Problems%20With%20Repeated%20Columns
Don't worry about unique indexes until you get that sorted out.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.
