Menu

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.

IBM iNode.jsDb2 for iData ModelingTools Rental

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:

  1. A tool can be rented only when its status is AVAILABLE.
  2. An inactive customer cannot begin a new rental.
  3. The due date cannot be before the rental date.
  4. Returning a rental records the return time and makes the tool available again.
  5. 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:

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.

← Return to Node.js on IBM i

Comments

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