Checking Board Games In/Out With A USB Scanner — Part 1

I am working on a project that will allow me to use the existing barcodes on board games to catalog them into a personal database of games that I own.  You can read up on that project here.

That project has more or less wrapped up.  The next step is a related but different project: creating a check in/out system.  This system will be closely tied to our inventory system, so it will be in the same database.

I run an annual board game themed fundraiser called Play It Forward.  This year, we are going to try to have a system that will allow players to check out games from our library.  Each participant at the event will have a badge with a unique barcode.  When they want a game, a librarian will first scan their barcode and then the barcode of the game they want.  This will update the game in the database as being checked out.  When the person is done with the game, they will return it to the librarian who will then scan the barcode of the game.  The database will then check the game in.

The Schema

Again, the schema for this project is pretty easy.  I’m building this within the inventory database, so the inventory table shown below already exists (except I added the num_checkedout column).  The relevant schema is:

checking (6)

A player can have multiple games checked out.  Because we have multiple copies of some games, some games could potentially be checked out by multiple people.  This means the inventory and players tables have a many-to-many relationship.

Also, since we own multiple copies of some games, we can’t simply have a boolean (yes/no) column for each game for being checked out or checked in.  When a game gets checked out, the num_checkedout column will increment.  Within the Python script, we are going to have to make sure the num_checkedout does not exceed the number in the owned column.

The Code

I added three functions: checkincheckoutchecking, and numcheckedout.  The checkin function checks a game in to a specific player, the checkout function checks the game out to a function, and the checking function checks to see if the game should be checked out or checked in.  Finally, the numcheckedout function checks to see how many copies of a specific game are checked out.  The functions are below:

There is still quite a bit I’d like to do to make this better.  Most of these are noted in the “TO DO” comments in the code above.  However, the script is functioning as is!

Have questions or suggestions?  Please feel free to comment below or contact me.

Leave a Reply

Your email address will not be published. Required fields are marked *