Lesson 9 · Node.js on IBM i — From RPG Developer to Modern IBM i Developer
Design the Tools Rental Data Model
Turn the tools-rental idea into three small Db2 for i tables and define the business rules before writing SQL.
Before we create tables or API routes, we need to agree on what the application is responsible for. A small data model gives the rest of the project a stable foundation.
Our first release will track tools, customers, and rentals. It will not handle payments, reservations, maintenance scheduling, or multiple store locations. Those may be reasonable future features, but adding them now would make the first version harder to understand.
The three tables
TOOLS
One row represents one physical tool:
TOOL_ID Stable numeric identifier
NAME Description shown to the user
CATEGORY Drill, saw, sander, and so on
DAILY_RATE Rental charge per day
STATUS AVAILABLE or RENTED
Two drills of the same model should have separate rows because one may be rented while the other remains available.
CUSTOMERS
For this course, a customer needs only:
CUSTOMER_ID Stable numeric identifier
NAME Customer or company name
EMAIL Contact address
ACTIVE Whether new rentals are allowed
We will not store payment information. If a real application needs payments, use a suitable payment provider rather than inventing a card-storage design.
RENTALS
A rental connects one tool to one customer for a period of time:
RENTAL_ID Stable numeric identifier
TOOL_ID Tool being rented
CUSTOMER_ID Customer renting it
RENTED_AT When the rental began
DUE_DATE Expected return date
RETURNED_AT Actual return timestamp, initially null
RETURNED_AT tells us whether the rental is still open. Keeping the dates also preserves useful history after the tool is returned.
Write the rules in ordinary language
Before writing code, state the rules plainly:
- A tool can be rented only when its status is
AVAILABLE. - An inactive customer cannot begin a new rental.
- The due date cannot be before the rental date.
- Returning a rental records the return time and makes the tool available again.
- A completed rental cannot be returned a second time.
These rules will eventually appear in more than one place. Basic constraints belong in Db2. Request validation belongs at the API boundary. The transaction that rents or returns a tool must protect the database even when two requests arrive close together.
Choose the schema name
The examples use an SQL schema named TOOLRENT:
TOOLRENT.TOOLS
TOOLRENT.CUSTOMERS
TOOLRENT.RENTALS
A real organization may use a different naming convention or a separate schema for each environment. Make the choice explicit rather than relying on a developer’s library list.
Keep the design with the source
Create an sql directory in the project:
cd "$HOME/tool-rental"
mkdir sql
Add a short sql/README.md describing the tables and rules. The SQL scripts we add next will live beside the JavaScript source and travel through Git with it.
That matters during deployment: application code and database changes are parts of the same release, not two unrelated sets of files.
Check the model with a few examples
Ask what the rows would look like for these situations:
- Two cordless drills exist, but only one is rented.
- A customer returns a tool one day late.
- A customer has three previous rentals and one current rental.
- A tool has never been rented.
The three-table model can represent all four cases without adding special columns or duplicating customer details in the rental row.
The next lesson turns this model into Db2 for i tables and loads a small set of believable test data.
References
Documentation and references used for this lesson.

Comments
Share your thoughts, questions, or real-world IBM i experiences related to this article.