Imagine this table format, and let's kind of go with the notion that these are records for actual keys:

Table: key
Fields:
    id - Unique identifier for each key.
    opens - id of device the key opens.  We're assuming a many-to-one
            relationship but that could be many to many by adding anot
+her
            table mapping which targets share the same key.
    available - 0 or 1.
    date_created
    checkout_date
    user_id
    due_date
[download]

Next you would have a table, key_history:

Table: key_history
    id - unique history id.
    key_id - maps to the id of the key.
    action - created, checked-out, checked-in, destroyed.
    date_added
    user_id
[download]

Now whenever someone needs a key you can start a transaction, find a key that matches available=1, and opens=$target, and update that key to have the correct current user id, available=0, checkout_date = now, due_date = now + 7d. And at the same time make an entry in the history table. Use a transaction to assure that nobody else grabs the same key at the same time, and to allow for rollback if it turns out that the key has become unavailable.

Check-in should fail loudly if it tries to happen to a key id that already is checked in. Also, in this model each key has a unit of one. If you need more than one key that opens the same door, you would have multiple rows. And each user either tracks which key he has, or the application tracks it for him.

Dave,

Thanks so much. Your layout is much better than mine. I'm doing it in one database.

ID - database ID<br>
key_number - unique number for each key<br>
no_on_hand - number of keys we're holding.<br>
action - check in, check out, or register (inventory record)<br>
dateadded<br>
user<br>
[download]

I search and sort based mostly on the action field. For example, to get an inventory of keys I search for all fields where action=register. That shows me what we're supposed to have.

The big problem is I've already allowed them to have more than one key on hand. It would be much simpler if everyone could only have one key. That way, if the number of keys is < 0 or >1 that would mean there is a problem.

I have written error routines to catch and future entries where a check out does not have a corresponding check in before the next check out, but I'm trying to figure out a way to create a new audit routine where it would show me all keys < 0 or >2, OR keys that have two check ins in a row or two check outs in a row.

I suppose there is no way to do that.

Thanks.

It's really not a problem. Your application would just need to keep track of who has which keys, and with this approach it already does. When a user attempts to check-in a key, you validate against the user_id, so 'select id from key where user_id=? and opens=? and checked_out=1', and then let them check-in the first row that returns from that query. If no rows appear, they cannot check a key in, because it either means they already checked it in, or never had one checked out to begin with.




Dave

htmanning:

Since you haven't said, I'll go ahead and assume you've got an SQL database.

For that case, I'd suggest using some constraints to prevent people from checking in a key that's already checked in, or checking a key out that's already checked out.

If you can provide some detail on your database, I might be able to help a little.

...roboticus

When your only tool is a hammer, all problems look like your thumb.

It looks likely your table structure is not ideal but if you are stuck with it you can use user defined variables to hold previous record values

SELECT key_number, dateadded,
  IF (action=@last_action AND key_number=@last_key,"ERROR","OK"), 
  @last_action := action, @last_key := key_number
  FROM ( SELECT *
    FROM keysafe
    ORDER BY key_number, dateadded
  ) as x
[download]

Initialise the variables before use with

$dbh->do('SET @last_action := "UNKNOWN",@last_key="UNKNOWN"');
[download]

alternatively use perl variables

my $sql = 'SELECT key_number,action,dateadded
           FROM keysafe
           ORDER BY key_number, dateadded';
my $sth = $dbh->prepare($sql);
$sth->execute();
my ($last_key,$last_action);
while (my ($key,$action,$date) = $sth->fetchrow_array){
  my $status = 'OK';
  if (($key eq $last_key) && ($action eq $last_action)){
    $status = 'ERROR';
  };
  print "$key,$action,$date,$status\n";
  $last_key    = $key;
  $last_action = $action;
}
[download]