Design theory/problems with audit db, lots of checkboxes...

[Montez659]

Well, that may sum up most of what the problem is right there, but allow me to elaborate.

I am revamping (recreating) a db that my company uses for auditing. The previous db had about 85 check boxes (Yikes!) for each record, on top of about twenty other fields for other general information.

Well, good database practice teaches us that this is horribly wrong on soooo many levels. So what's the alternative? Well, the alternative is to use Junction/Joined tables. This will turn out for the better and allow for more detailed reporting as well as being more normalized.

Here is the catch. After much discussion with the person that will be the primary user of this db, they want to keep the checkbox type interface. So I guess what I was thinking was that I will pretty much have to have countless Events triggered On Click for about 20-25 checkboxes (hopefully after the 85 get condensed) that will manually update via VBA to the Junction table.

Following that thought is, what if an item is clicked that didn't intend to be? A record would be created, but then would I need to search to delete that record? As we all know, deleting records is probably not the best way to go about this, so I am hesitant to do so.

So, in summary:
1.) What am I missing that would make all of this simpler?
2.) If the On Click really is the only way to go, would the record be deleted if it got unclicked, or should there be a field that marks it as deleted without actually deleting the record?
3.) Anything else you can think of will, as always, be quite helpful and appreciated.

 

[jackd]

There is an audit trail sample at this site (often referenced)
http://www.hitechcoach.com/index.php?option=com_docman&task=doc_details&gid=39&Itemid=19

As for deletes, I would use "Shadow delete", a field that is intended to indicate deleted-- without actually losing any data.

 

[Montez659]

Jack, Thanks for the reply. And the shadow delete was kind of what I was thinking as well. As for the audit trail link that you sent me, I believe you might have misunderstood what this db is for.

It is not so much to do with audit trail of changes within the db as much as it is an actual audit of a process within the company. Therefore, the checkboxes I was referring to were more of the "Did a, b, c, etc., get done and loaded correctly?" At which point it will spit out a report to know how in compliance we are. That kind of audit.

 

[jackd]

OK, I did misunderstand, but was thinking more of the approach to an audit trail (as compared to an actual audit). There might be techniques that would be useful to you etc.

We used shadow deletes for years with a different database package.

Good luck with your project.

 

[Montez659]

So, does anyone else have any ideas about the above? I could use a bit of input.

 

[xenou]

Well, good database practice teaches us that this is horribly wrong on soooo many levels. So what's the alternative? Well, the alternative is to use Junction/Joined tables. This will turn out for the better and allow for more detailed reporting as well as being more normalized.

Here is the catch. After much discussion with the person that will be the primary user of this db, they want to keep the checkbox type interface. So I guess what I was thinking was that I will pretty much have to have countless Events triggered On Click for about 20-25 checkboxes (hopefully after the 85 get condensed) that will manually update via VBA to the Junction table.

A junction table in and of itself is not the "answer" to a multitude of checkboxes. You would have to explain more about why you think a junction table is better, which in turn follows from the structure of the data and the purpose of the form.

ξ

 

[Montez659]

A junction table in and of itself is not the "answer" to a multitude of checkboxes. You would have to explain more about why you think a junction table is better, which in turn follows from the structure of the data and the purpose of the form.

ξ

The purpose of the form is for the auditor to review a checklist of items for whatever they are reviewing, to keep track of the exceptions, and to be able to - if there are exceptions - to print off a summary page detailing the exceptions found in that audit that need to be corrected.

As far as why do I think that a Junction table is better is more of what I have learned working with databases and not so much to do with what is easiest. I was told by board contributors that storing a multitude of checkboxes in each record is bad practice. So my only alternative that I know of is a junction table.

Maybe there is a different way to do this? If there is then I am not aware of it and would like to learn it. For sure, a junction table seems like a huge hassle when dealing with a checkbox control.

Here is a quick snapshot of some structure and relationships:
http://dl.dropbox.com/u/17000917/auditStructure.PNG

 

[xenou]

A junction table is for resolving many-to-many relationships. Use one if that's what you need, otherwise you may not need one.

I can't even see 85 fields in all of your tables combined. How does this work with the form? Where is the data?

 

[Montez659]

My bad. That is the structure of the test db that I am rebuilding. Here is a snapshot of the current form. Pretty much all there is with the exception of some patchwork reporting. The checkboxes continue on quite a bit further south of what you can see.

http://dl.dropbox.com/u/17000917/auditShot1.PNG

 

[Montez659]

Here is another shot. This is about half the fields.

http://dl.dropbox.com/u/17000917/auditShot2.PNG