I am in the process of designing a simple database for tracking our backup tapes which case they are in.
Below is the basic design, showing the primary keys and the relationships;
** = Primary Key
What I'm trying to do is track the location of the individual tape to the case it is in. Our storage facility uses the case number to locate the tape. We recycle the tapes after a certain retention period, and those tapes do not usually go back into the same case they were in to begin with.
We use a barcode scanner to enter data. Essentially what I'd like to have happen is we would pull up the case that we are inventorying, and then scan each tape and have it be updated to reflect its new case location. The problem I am running into is that since the tape barcode is the primary key, adding it to a new case creates a duplicate (which I understand), however, I'd like that tape to be automatically removed from its original case and then reflected in its new location. We have no need to record history of the tape. I know that I could go to each tape in the table and update its CASEID, but there has to be an easier way.
Please understand that if any VB code is required, I may need a little extra explanation as to where that code goes.
Thank you very much in advance!