Project: Inventory Management Product | Backend Database Engineering with Generative AI | MySQL, AWS RDS

Context: Our team is developing an inventory management system for a biochemistry department, The goal is to streamline sample tracking and enable cross-institution sharing.

Client has no standardized system for organizing tens of thousands of strains (tubes), sharing and management is done via email and manual logs. The system includes a database, search, input, and labeling tools, with barcode integration under discussion. Users can search by tags and add new strains via a form that generates a unique ID and barcode. Entries require approval before being added. The MVP (minimum viable product) will have 2 core features: Input, search, output

Key Questions Resolved in Sprint 1:

• How to access Amazon RDS affordably from MySQL Workbench for larger teams and enable IPv6 addresses.
• How to automatically generate an entry in the unique_id table when creating an entity (CREATE PROCEDURE vs. CREATE TRIGGER).
• GenAI doesn’t always provide the cleanest solution and can will go down silly and complicated rabbit holes—sometimes it’s best to start over.

Next Sprint:

User story 3: When I search, I want to see an output of all relevant data, so I can know exactly where, who and what. --> Solution: Write code that designs the output of search.

User Story 4: I want to be able to add a strain to the system --> Solution: creating add tool

User Story 5: As a user, I want to be able to move a tube and update its location --> Solution: Creating move tool

Food for thought: Which GenAI model creates the best code? Github Copilot also goes down rabbit holes. Would Deepseek - which uses a different LLM be better?

After collaborating with the client and developers on the project initiation plan, including scope and WBS, we started Sprint 1.

Sprint 1: Backend & Frontend Setup

Frontend: Our front-end work focused on setting up React and creating Figma wireframes (not demonstrated here).

Backend: We defined inputs, created user stories, and prioritized database and cloud sharing setup. (Key assumptions: strains don’t expire, and tubes are the smallest unit)

1. Database Selection & Setup

Considering budget constraints, we chose MySQL Workbench and Amazon RDS for our minimum viable product (MVP).

Cross-Collaboration Challenges: With team members in different locations, we needed a single AWS RDS account. MySQL Workbench initially blocked access, requiring troubleshooting. Adjusting controls and adding IPv6 addresses resolved the issue.

2. Database Structure & Entity Relationships

User Story 1: As a user, I want to look at a tube/box/rack and know where it belongs to place it correctly.

Solution: We structured entities into tables: Room → Freezer → Shelf → Rack → Box → Tube. Each entity (e.g. room) has a unique ID and references the previous hierarchy using foreign keys.

The ON DELETE CASCADE rule ensures automatic updates, reducing manual errors. We also implemented data validation to maintain accuracy.

FOREIGN KEY (box_id) REFERENCES boxes(box_id) ON DELETE CASCADE,
CHECK (row_index >= 0), CHECK (col_index >= 0)        

3. Handling Unique IDs

User Story 2: As a user, when I search for an entity (box, tube, etc.), I should see only one unique record.

Problem: Entities (e.g. boxes) will be REUSED, and thus entity IDs (e.g., Box 1, Box 2) can be repeated, leading to confusion. We needed a system-wide unique identifier for every entity.

Solution: Unique ID should be a separate table that is referenced by everything else 

Using a quick Generative AI help, we arrived at this:

But there's a problem. This will require us to MANUALLY update the unique_id when creating a room entity.

I want it to AUTOMATICALLY create a unique_id when we create an entity.

This is what Gen AI gave us - A procedure:

It will work, but it's messy and unnecessarily complicated.

We asked it to refine it, but it just got more and more complicated, and went down a deep rabbit hole.

We decided to start over, and this time, we used CREATE TRIGGER

This automatically assigns a unique ID upon entity creation—eliminating the need for manual intervention and ensuring consistency.

Action Item:

1. Verify shelf/rack dimensions with client to update current “top, middle, bottom” or “left, middle, right” to INT.
2. Resolve duplicate entries caused by manual input errors. (see unique_id table below)

Our results!

Next Sprint:

User story 3: When I search, I want to see an output of all relevant data, so I can know exactly where, who and what.

Solution: Write code that designs the output of search.

User Story 4: I want to be able to add a strain to the system

Solution: creating add tool

User Story 5: As a user, I want to be able to move a tube and update its location

Solution: Creating move tool