Re: Searching Database Question

[davido]

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

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

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.

[htmanning]

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>

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.

[davido]

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.