This Instructable will show you how to build an inventory management system using RFID.

The system is build upon an Arduino connected to a Raspberry Pi 3. The arduino reads RFID tags in shelves that the operator puts on the sensor itself. The ID of the shelf gets sent to the Raspberry Pi which in turn processes it and checks a database. The Raspberry Pi hosts a mySQL server which can later be accessed through a Windows forms application, on a seperate computer on the LAN.

The workflow of the system will be as follows:

The inventory operator will come over to his station and put his personal RFID tag on the scanner for logging in. He is then allowed to take a box from the inventory and put on the scanner, the computer in front of the scanner will then prompt him for giving or taking items out of the box. The operator then enters the number, takes his items out of the box, presses OK on the computer and puts the box back.

In the meantime the database will have identified him as the user, the box as the product and updated the connected stock regarding to his input, and made a log of his actions: what, when, who and how many.

Step 1: Items

Amongst the things you will need is:

An Arduino - I used an Uno, but a nano could also do the trick.
A Raspberry Pi running Raspbian and mySQL or MariaDB.
RFID reader with key cards - I used a RFID-RC522 with the included keycards.
One RGB light - just to make it look a bit fancier, and to acknowledge a successful read to the user.
A 3D printed case for the electronics.

Step 2: Raspberry Pi

For this project you need the Raspberry Pi ro run an mySQL/MariaDB server together with Node-red. This is simply done by getting the packages and installing them. Make sure your database allows outside connections as the Windows application will connect from a different ip on the LAN.

Furthermore your database needs a user that is allowed to request and update data from elsewhere than the localhost on the pi.

For Node-red you need to install the MySQL and Serial communication packages. The serial because the Arduino and Raspberry will communicate over serial.

Step 3: Database

The database can be designed just as you like it. I chose to make some relations, within the tables.

In MySQL workbench it is easy to set up the database. Make the tables and choose what content they need, and after you can drag foreign keys around, to design your database cleverly.

I chose to make 6 tables, that all have some kind of relation. These six tables are:

Employees
Users
Products
Log
Stock
Location

To make them related you make foreign keys between them, remember what the relationship is: One to many or one to one.

In my example the table "log" has a many to one relationship with both "users" and "products". This is because one user can have several log columns, both with the same product or with a different product. However the table "products" has a one-to-one relationship with "stock" because the product should only have one stock.

Step 4: Windows Forms App

For accessing the database and monitoring/changing values a windows form app has been made. This app has three different "pages" - A log-in screen: for entering the database credentials, a main screen: for monitoring the existing inventory, and displaying the current user, and a product screen: which opens automatically when a product has been registered as being active, from here you can enter wether you take from the inventory or put some in.

Whenever the app has been started and connected to the database, it checks for active users every few seconds. When a user has been activated his/hers name appears on the screen. If the person is an administrator a Admin button appears. From here the log can be accessed with information about past events.

The app has been created in C# in Visual studio.

For accessing mySQL or similar databases you need to import the MySQL library, and write the correct connection string.

Step 5: Arduino Code

--------------------------------------------------------------------------------------------------------------------
Sketch for inventory management system
--------------------------------------------------------------------------------------------------------------------
This uses a MFRC522 library example; for further details and other examples see:https://github.com/miguelbalboa/rfid
pin layout used:
------------------------------------------------------------------
			MFRC522 		Arduino
			Reader/PCD 		Uno/101
Signal 		Pin 				Pin
------------------------------------------------------------------
RST/Reset	RST 			9
SPI SS 		SDA(SS) 		10
SPI MOSI 	MOSI 			11 / ICSP-4
SPI MISO 	MISO 			12 / ICSP-1
SPI SCK 		SCK 			13 / ICSP-3 */</p><p>#include <SPI.h>
#include <MFRC522.h>
#define SS_PIN 10
#define RST_PIN 9
#define R_PIN 3
#define G_PIN 5
#define B_PIN 6
int r = 0;
int g = 0;
int b = 0;
int rIncValue = 1;
int gIncValue = 1;
int bIncValue = 1;</p><p>MFRC522 rfid(SS_PIN, RST_PIN); // Instance of the class
MFRC522::MIFARE_Key key;</p><p>// Init array that will store new NUID
byte nuidPICC[4];</p><p>void setup() {
	Serial.begin(9600);
	SPI.begin(); // Init SPI bus
	rfid.PCD_Init(); // Init MFRC522
	for (byte i = 0; i < 6; i++) {
		key.keyByte[i] = 0xFF;
	}
	pinMode(R_PIN, OUTPUT);
	pinMode(G_PIN, OUTPUT);
	pinMode(B_PIN, OUTPUT);
}</p><p>void loop() {
	LedRun();
	// Look for new cards
	if ( ! rfid.PICC_IsNewCardPresent())
	return;</p><p style="margin-left: 20px;">   // Verify if the NUID has been read
   if ( ! rfid.PICC_ReadCardSerial())
  return;</p><p style="margin-left: 20px;">//Get the PICC type
MFRC522::PICC_Type piccType = rfid.PICC_GetType(rfid.uid.sak);</p><p style="margin-left: 20px;">// Check is the PICC of Classic MIFARE type
if (piccType != MFRC522::PICC_TYPE_MIFARE_MINI
&& piccType != MFRC522::PICC_TYPE_MIFARE_1K
&& piccType != MFRC522::PICC_TYPE_MIFARE_4K) {
	Serial.println("INVALID");
	FlashLed(R_PIN);
	return;
}</p><p style="margin-left: 20px;">// Store NUID into nuidPICC array
for (byte i = 0; i < 4; i++) {
	nuidPICC[i] = rfid.uid.uidByte[i];
}</p><p style="margin-left: 20px;">//Print the hex value of the rfid
printHex(rfid.uid.uidByte, rfid.uid.size);
Serial.println();</p><p style="margin-left: 20px;">//Flash green - Success!
FlashLed(G_PIN);</p><p style="margin-left: 20px;">// Halt PICC
rfid.PICC_HaltA();</p><p style="margin-left: 20px;">// Stop encryption on PCD
rfid.PCD_StopCrypto1();
}</p><p style="margin-left: 20px;"></p><p>/** Helper routine to dump a byte array as hex values to Serial. */
void printHex(byte *buffer, byte bufferSize) {</p><p style="margin-left: 20px;">for (byte i = 0; i < bufferSize; i++) {
	Serial.print(buffer[i] < 0x10 ? " 0" : " ");
Serial.print(buffer[i], HEX);
}</p><p> }</p><p>
void FlashLed(int pinNumber) {
	// A routine for flashing the LED with a certain color
	digitalWrite(R_PIN, LOW);
	digitalWrite(G_PIN, LOW);
	digitalWrite(B_PIN, LOW);
	digitalWrite(pinNumber, LOW);
	delay(500);
	digitalWrite(pinNumber, HIGH);
	delay(500);
	digitalWrite(pinNumber, LOW);
	delay(500);
	digitalWrite(pinNumber, HIGH);
	delay(500);
	digitalWrite(pinNumber, LOW);
}</p><p>void LedRun() {
// A routine for cycling the LED through colors</p><p style="margin-left: 20px;">analogWrite(R_PIN, r);
analogWrite(G_PIN, g);
analogWrite(B_PIN, b);
if (rIncValue == 1 && r < 255 && g == 0 && b == 0) {
	r++;
}
if (gIncValue == 1 && g < 255 && r == 255 && b == 0) {
	g++;
}
if (bIncValue == 1 && b < 255 && r == 255 && g == 255) {
	b++;
}
if (r == 255 && g == 255 && b == 255)
	rIncValue = (-1);
if (g == 255 && r == 0 && b == 255)
	gIncValue = (-1);
if (b == 255 && r == 0 && g == 0)
	bIncValue = (-1);</p><p style="margin-left: 20px;">if (rIncValue == (-1)) {
	r--;
if (r == 0)
	rIncValue = 1;
}
if (gIncValue == (-1)) {
	g--;
if (g == 0)
	gIncValue = 1;
}
if (bIncValue == (-1)) {
	b--;
if (b == 0)
	bIncValue = 1;
}</p><p>}</p>

Step 6: Node-Red

For handling the Arduino's signals connected to the Raspberry Pi over a serial port, we have to make a flow in Node-red.

This flow needs to handle the read RFID's and request the database whether the RFID is connected to a User or a Product, afterwards the flow makes a query to the database that updates the connected user/product with a 'true' in the active column.

The 'flow' flows in the following order:

Read the Serial port for incoming strings.
Process the incoming string and remove excess whitespace.
Request the database for a user with the read RFID.
Process the databases response - If the database returns an array longer than 0 the RFID is a user. Otherwise the RFID is a product.
Request the database to make all active users/products inactive (following the response from the database, so an active user won't be disabled if we're scanning a product) and update the user/product with the connected RFID to be active.
For simplicity, debug has been enabled on some nodes.

This happens sequential, which means that the first node must be 'active' before the following node will activate.

The flow has been added as a .txt file, open up node red and import the file to access the flow.

In Error cases, where the read RFID doesn't belong to either a user or product, the flow will log out current products, if one is active, and disregard the read RFID. In most cases this will end up as a successful read by the Arduino, but nothing else will happen: it would be wrong if the user could take items out of a box that doesn't exist, or log in as a user without having permission!

Node-Red-Flow.txt
Download

Step 7: PHP Inventory Management

For administrative reasons a simple PHP page has been added to make inventory management more manageable and simple.

This PHP page shows the current inventory of every item currently in the "warehouse".

This webpage can be accessed without user login and off-site, as long as you are on the same subnet as the raspberry pi. The raspberry pi will only need a static IP, so the php won't try to connect to the wrong IP.

The PHP_inventorymanager.txt file can be downloaded and opened in a text editor. Fill in the connection string in the top with the credentials of your mySQL server, and maybe add the filepath to a different image than my "InManSys.png". When edited simply save the file as .php and open through a server, for example a local APACHE server.

The PHP script will then connect to the database and make the HTML that will be shown to the users internet browser. If the PHP script can't connect to the IP several errors will pup up on the screen telling you that it was unable to connect to the database. And you should therefore check the database status as well as the connection string in the PHP.