Beefy Boxes and Bandwidth Generously Provided by pair Networks
Keep It Simple, Stupid

Re: Searching Database Question

by davido (Cardinal)
on Oct 14, 2018 at 00:01 UTC ( #1223981=note: print w/replies, xml ) Need Help??

in reply to Searching Database Question

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.


Replies are listed 'Best First'.
Re^2: Searching Database Question
by htmanning (Pilgrim) on Oct 14, 2018 at 00:19 UTC

    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.


      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.


Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1223981]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (5)
As of 2019-05-20 21:26 GMT
Find Nodes?
    Voting Booth?
    Do you enjoy 3D movies?

    Results (129 votes). Check out past polls.