Referential Integrity

[PhillipsAndrew]

I’m looking for some advice on Referential Integrity...

I’m designing a database to loan Equipment. The system will be barcode based, but at it’s most basic a user will scan the equipment they want to borrow, scan the department they are taking it to and then ‘scan’ Confirm.

I’ve got no problems with the barcode side (not yet anyway!).

Simplified, I have 3 tables.

tblEquipment_Available:- contains equipment available for loan
tblDepart_Name:- Department that wants to borrow equipment
tblLoans:- Records what equipment is loaned.

There is a one-many relationship between tblEquipment_Available & tblLoans
And a one-many relationship between tblDepart_Name and tblLoans.

The trouble I have is that I need to allow for a user not scanning their department.
If the Department Name is not entered then Access, as expected, throws up an error for an unknown entry in tblDepart_Name.

Is removing Referential Integrity the answer?
If not, how do I get around this?

This system will be in use 24 hours a day by loads of inexperienced users, so I must allow for the case where department is skipped and not entered.

Is error trapping the answer?

Any help much appreciated...

P

 

[Rich]

Aren't you better off leaving it and forcing borrowers to enter their dept?

 

[PhillipsAndrew]

I would like to!!

The trouble is that I'm trying to allow for every eventuality. I know that some users will come into the equipment store in the middle of the night and just scan the equipment and leave (regardless of what the screen says). I want to be able to handle this so that the system does not hang with an error message for the next user...

Any thoughts?

 

[Rich]

How will you know who has what though?

 

[PhillipsAndrew]

Sorry Rich, I'm not being very clear am I?

The scenario I'm talking about is for a hospital.... We have a 24 hour equipment store where staff (nurses & porters) can come and borrow equipment. The idea is they scan the equipment and department so that it is tracked and charged for correctly.
For the last 10 years a paper system has been operated. The users SHOULD enter on paper the equipment they've taken and which department. Needless to say, hardly anyone fills this in!!

I'm expecting that no matter how simple the system is, people may only scan equipment, or only scan departments or whatever. So yes, we won't know who has what.
The last thing I want is that the system hangs with an Error Message so that the next user can't use the system.

Does that explain things?

I wondering about creating a default "Unknown Department" in my list of departments which is automatically entered unless one is entered by the user..

Does any of this make sense?

P

 

[Kodo]

perhaps this will be the time when people are made to fill things out properly.

Why create a computerized version of a paper system and include its' faults as well. Seems like you're not moving ahead by doing this. What we're all saying is that you should MAKE them put the dept in no matter what!

If dept's get charged based on what they purchase and no one puts in the dept, then I bet the budget is pretty screwed up and fiscal would love to have accurate numbers to play with.

Why not take charge and help the whole lot out by not making it an "option" to exclude the entity that will be charged.

It all comes down to accountability.

 

[PhillipsAndrew]

I'm sure you're right... and I'll follow you're advice...