Audit Database Table Structure

[vapid2323]

Ok this is a simple database but I want to double check my design before I move forward.

We have a set of 'sites' that’s audited 4 ways:

· Qualification
· Surveillance
· ForCause
· Capacity

Also, please dont mind the fields in the tables, I am moving from excel to access and I need to reformat the naming.

My question relates to the 'Auditor' table and the join tables connecting it to the audit types listed above. Is this really the best way to setup my tables?

Any other tips are appreciated as I love to learn new tricks!

 

[MarkK]

Can there be many auditors for a single audit? If so this looks good.
If there can only be one auditor per audit, then this needs changes.

 

[vapid2323]

Can there be many auditors for a single audit? If so this looks good.
If there can only be one auditor per audit, then this needs changes.

Thanks for looking, you just helped me with my other question

 

[vapid2323]

Ok I have run into another issue with the same database.

I have the Qualification table where I would like to give my users the ability to add in multiple observations.

I have a observation table that has two fields:

· ObsComments
· fk_ObsClassification

What is the best way to setup the relationships so that my users can choose a Classification but enter the Comments by hand?

Please see the attached PDF for the relationships I am trying to use.

 

[MarkK]

What you propose is right if one Qualification can have many Observations and one Observation can have many qualifications. If not then not. This seems unlikely to me based on the names of the tables, but only you can say. Certainly the capacity to choose one thing and enter another is not a criteria I would use to inform me about a relationship.
Note that 'best way' is assessed in respect to a purpose, like what it the best way to Chicago. If you don't descibe where you're going or what outcome you want, then 'best way' is not defined.
Hope this helps,

 

[vapid2323]

What you propose is right if one Qualification can have many Observations and one Observation can have many qualifications. If not then not. This seems unlikely to me based on the names of the tables, but only you can say. Certainly the capacity to choose one thing and enter another is not a criteria I would use to inform me about a relationship.
Note that 'best way' is assessed in respect to a purpose, like what it the best way to Chicago. If you don't descibe where you're going or what outcome you want, then 'best way' is not defined.
Hope this helps,

This really did help, I get it now.... I hope, I will do some testing