Design the RED Network System
Design a full Oracle SQL schema and Express.js REST API for a donation platform.
Introduction
30 Second Summary
When a community wants to share resources, donate goods, and exchange items, the hard part is not the generosity. It is designing a system that tracks who owns what, who needs what, and how every transaction connects.
In this project, you will design the complete database schema and API structure for the RED Network, a Resource Exchange and Donation platform. You will write Oracle SQL table definitions, build an Express.js project with organized route files, and create a mapping document that ties it all together.
What You'll Build
When you finish, you can start the server, hit any endpoint in your browser, and open the schema to trace a complete chain from a donation request all the way back to the user who created it.
By the end of this project, you'll have:
- A complete Oracle SQL schema where every foreign key and named constraint enforces real business rules, like requiring a donation to reference both a real user and a real resource.
- A running Express.js server you can start and test in your browser, where every endpoint responds with its module and action.
- A DESIGN.md mapping document that traces each of the six modules to its tables and API routes, giving you a single place to verify the whole system is consistent.
- An optional Secret Mission to push your skills further.
Are there any prerequisites?
None. This project includes setup instructions for every tool you need, including VS Code, Node.js, and npm. No prior experience with Oracle SQL or Express.js is required.
Before We Start
Before we dive in, let's lock in what you're about to build and why it matters.
Set Up Your Project Environment
Before you can design database schemas or API routes, you need a working development environment. Without Node.js installed, you cannot verify that your Express.js route files are valid JavaScript.
In this step, you will install VS Code and Node.js, create the project folder with Express.js, and run a minimal server to prove everything works.
In this step, get ready to:
- Install VS Code and Node.js v24 LTS.
- Create the red-network project folder with Express.js.
- Run a minimal Express server and confirm it responds.
Install VS Code and Node.js
VS Code is the code editor you will use throughout this project. Node.js is the JavaScript runtime that lets you run server-side code.
- Open your browser and go to https://code.visualstudio.com.
- Click the download button for your operating system and run the installer.
- Next, go to https://nodejs.org in your browser.
- Download the LTS version (labeled v24 Krypton).
- Run the installer and accept all default options.
What is npm?
The Node.js installer bundles npm (Node Package Manager) automatically. npm lets you install, manage, and share JavaScript packages. You will use it to install Express.js in the next substep.
- Press Cmd+Space (macOS) or the Windows key (Windows) to open your search bar.
- Type VS Code and press Enter to open it.
- Open the integrated terminal by pressing Ctrl+` (backtick key, top-left of your keyboard).
- Check that Node.js is installed correctly by running this command:
node --version✔️ I see version 24 or higher
Node.js is ready to go.
- Confirm npm is also installed by running:
npm --versionYou should see a version number printed. Both tools are now set up.
ⓧ I see an older version
You have Node.js installed, but the project requires v24 LTS.
- Go to https://nodejs.org and download the LTS version (v24 Krypton).
- Run the installer to upgrade your existing Node.js installation.
- Close and reopen the VS Code terminal, then run node --version again to confirm it shows v24.
ⓧ Command not found
Node.js is not installed yet.
- Go to https://nodejs.org and download the LTS version (v24 Krypton).
- Run the installer and accept all default options. The installer adds Node.js and npm to your system path.
- Close and reopen the VS Code terminal, then run node --version to confirm it shows v24.
Create the project folder and install Express
Now that your tools are ready, you will create the project folder and install Express.js. This framework powers the RED Network API server.
- Move to your Desktop by running this command:
cd ~/Desktop- Create your project folder and move into it by running these commands:
mkdir red-network
cd red-network- Initialize a new Node.js project by running:
npm init -yYou should see the contents of a new package.json file printed in the terminal.
What does npm init -y do?
This command creates a package.json file with default settings. The -y flag accepts all defaults automatically so you skip the interactive prompts.
package.json tracks your project's name, version, and dependencies. Every Node.js project needs one.
- Install Express.js by running:
npm install express@5You should see output confirming that express has been added as a dependency.
Why Express 5?
Express 5 is the current Active LTS line, tagged as the latest version on npm since March 2025. Express 4 entered Maintenance in April 2025. For a new project, Express 5 is the right choice.
- Create the project's folder structure by running:
mkdir routes database docs- Confirm everything was created by running:
lsYou should see database, docs, node_modules, package-lock.json, package.json, and routes listed in the output.
Don't see the folders?
- Make sure you ran the mkdir command from inside the red-network folder.
- Run pwd (macOS/Linux) or cd (Windows) to check your current directory. It should end with red-network.
- help me debug my folder structure
Create the server and verify it runs
With the folder structure in place, you will create a minimal Express server. This confirms that Node.js and Express are working together before you start designing the system.
- Open package.json in the VS Code Explorer panel on the left by clicking on the file name.
- Select all the existing content and replace it with this customized version:
{
"name": "red-network",
"version": "1.0.0",
"description": "RED Network - Resource Exchange and Donation Network",
"main": "server.js",
"scripts": {
"start": "node server.js"
},
"keywords": ["red-network", "resource-exchange", "donation"],
"author": "",
"license": "ISC",
"dependencies": {
"express": "5.2.1"
}
}What did you change?
- "main": "server.js" tells Node.js which file to run as the project's entry point.
- "start": "node server.js" creates a start script so you can later run npm start instead of typing the full command.
- "express": "5.2.1" pins the exact Express version so every developer on the project uses the same one.
- Save package.json by pressing Cmd+S (macOS) or Ctrl+S (Windows).
- Check that VS Code does not show any red squiggly underlines in the file. Red underlines indicate a JSON syntax error.
- Create a new file called server.js by right-clicking in the Explorer panel and selecting New File.
- Add the following code to server.js:
const express = require('express');
const app = express();
const PORT = 3000;
app.use(express.json());
// Root endpoint
app.get('/', (req, res) => {
res.send('RED Network API is running');
});
app.listen(PORT, () => {
console.log(`RED Network server running on http://localhost:${PORT}`);
});What does this code do?
- require('express') loads the Express framework you installed earlier.
- express() creates a new Express application instance.
- app.use(express.json()) enables the server to parse incoming JSON request bodies.
- app.get('/') defines a route that responds to GET requests at the root URL with a confirmation message.
- app.listen(PORT) starts the server on port 3000 and logs the URL.
- Save server.js.
✔️ Awesome, I've got everything!
Make sure you have saved both package.json and server.js before continuing.
ⓧ I'd like to double check the full code
Here is the complete server.js file:
const express = require('express');
const app = express();
const PORT = 3000;
app.use(express.json());
// Root endpoint
app.get('/', (req, res) => {
res.send('RED Network API is running');
});
app.listen(PORT, () => {
console.log(`RED Network server running on http://localhost:${PORT}`);
});And the complete package.json file:
{
"name": "red-network",
"version": "1.0.0",
"description": "RED Network - Resource Exchange and Donation Network",
"main": "server.js",
"scripts": {
"start": "node server.js"
},
"keywords": ["red-network", "resource-exchange", "donation"],
"author": "",
"license": "ISC",
"dependencies": {
"express": "5.2.1"
}
}Before you run the server, what do you think you will see printed in the terminal?
- Start the server by running this command:
node server.jsYou should see RED Network server running on http://localhost:3000 printed in the terminal.
- Open your browser and visit http://localhost:3000.
You should see the text "RED Network API is running" displayed in the browser. Your development environment is fully set up and your server is live.
Server not starting?
- Check that you saved server.js before running the command.
- Make sure you are running the command from inside the red-network folder. Run pwd to check your current directory.
- If you see Cannot find module 'express', run npm install express@5 again and then restart the server.
- If port 3000 is already in use, stop any other running processes and try again.
- help me debug my Express server startup error
Your environment is ready and your server is responding. Next up, you will design the database schema for the User and Administration modules.
Design the User and Administration Module Schema
Your project environment is ready and the server is responding. Now it is time to design the data layer that every other module will depend on.
Every action in the RED Network is performed by a user. Creating a resource, requesting a donation, posting in a community. Without a Users table, none of those modules can store who did what. The Administration module is tightly coupled to Users because admins are simply users with elevated roles. By designing both together, you create the foundation that every future foreign key will point back to.
In this step, get ready to:
- Write Oracle SQL DDL for the Users and Roles tables with named constraints.
- Add a junction table and an admin audit log with foreign key relationships.
- Create Express.js route stubs for user and admin endpoints and wire them into the server.
Create the schema file with sequences
In Oracle SQL, sequences generate unique IDs for each row you insert. You define them once, and Oracle increments them automatically. This is how you avoid duplicate primary keys without managing counters in your application code.
- In VS Code, create a new file called schema.sql inside the database/ folder.
- Add the file header and three sequences by pasting this code:
-- ============================================================
-- RED Network Database Schema
-- Resource Exchange and Donation Network
-- Oracle SQL DDL
-- ============================================================
-- ============================================================
-- SEQUENCES
-- ============================================================
CREATE SEQUENCE seq_user_id START WITH 1 INCREMENT BY 1 NOCACHE;
CREATE SEQUENCE seq_role_id START WITH 1 INCREMENT BY 1 NOCACHE;
CREATE SEQUENCE seq_log_id START WITH 1 INCREMENT BY 1 NOCACHE;What do these sequences do?
Each CREATE SEQUENCE statement defines an auto-incrementing counter. When you later insert a row into RED_USERS, you call seq_user_id.NEXTVAL to get the next available ID.
NOCACHE tells Oracle not to pre-allocate IDs in memory. For a development schema, this keeps the IDs predictable and sequential.
- Save schema.sql.
- Confirm the file was created by running this in your terminal:
ls database/You should see schema.sql in the output.
Add the RED_USERS table
The RED_USERS table is the central table in your schema. Every other module will reference it through foreign keys. Oracle requires that you name every constraint explicitly using the CONSTRAINT keyword, which makes debugging much easier when a constraint is violated.
- In schema.sql, add the RED_USERS table below the sequences by pasting this code:
-- ============================================================
-- MODULE 1: USER MODULE
-- ============================================================
CREATE TABLE RED_USERS (
user_id NUMBER(10) CONSTRAINT pk_users PRIMARY KEY,
username VARCHAR2(50) NOT NULL
CONSTRAINT uk_users_username UNIQUE,
email VARCHAR2(100) NOT NULL
CONSTRAINT uk_users_email UNIQUE,
password_hash VARCHAR2(255) NOT NULL,
full_name VARCHAR2(100) NOT NULL,
phone VARCHAR2(20),
user_type VARCHAR2(20) NOT NULL
CONSTRAINT ck_users_type
CHECK (user_type IN ('individual', 'ngo', 'institution', 'volunteer')),
status VARCHAR2(20) DEFAULT 'active'
CONSTRAINT ck_users_status
CHECK (status IN ('active', 'inactive', 'suspended')),
created_at DATE DEFAULT SYSDATE,
updated_at DATE DEFAULT SYSDATE
);What does this table do?
Each column has a named constraint. pk_users makes user_id the primary key. uk_users_username and uk_users_email prevent duplicate usernames and emails.
The CHECK constraints on user_type and status restrict values to a fixed list. If application code tries to insert a user_type of 'company', Oracle will reject it. This enforces business rules at the database level.
Why VARCHAR2 instead of VARCHAR?
Oracle documentation states that VARCHAR is scheduled to be redefined with different comparison semantics. Always use VARCHAR2 in Oracle SQL to avoid unexpected behavior in future database versions.
- Save schema.sql.
- Scroll through the file. You should see the ck_users_type CHECK constraint listing four values: 'individual', 'ngo', 'institution', and 'volunteer'.
Seeing red squiggly lines in the SQL file?
VS Code may show syntax warnings if it does not have an Oracle SQL extension. These are cosmetic and do not affect your schema. The file is a design artifact that is not being run against a database right now.
If you see any actual typos, double-check that every CONSTRAINT keyword is followed by a name (like pk_users) and that parentheses are balanced.
Still having trouble? Help me check my RED_USERS table for syntax errors
- Add the RED_ROLES table below the RED_USERS table in schema.sql by pasting this code:
CREATE TABLE RED_ROLES (
role_id NUMBER(5) CONSTRAINT pk_roles PRIMARY KEY,
role_name VARCHAR2(30) NOT NULL
CONSTRAINT uk_roles_name UNIQUE,
description VARCHAR2(200)
);What does this table do?
RED_ROLES stores the available roles in the system (like 'admin', 'moderator', 'user'). The uk_roles_name UNIQUE constraint prevents creating two roles with the same name. You will connect users to roles through a junction table in the next substep.
- Save schema.sql.
- You should now see two CREATE TABLE statements below the sequences: RED_USERS and RED_ROLES.
Add the junction table and admin log
A single user can have multiple roles, and a single role can belong to multiple users. This many-to-many relationship needs a junction table that connects the two. The admin log table records every administrative action for audit purposes.
- Add the RED_USER_ROLES junction table below RED_ROLES in schema.sql by pasting this code:
CREATE TABLE RED_USER_ROLES (
user_id NUMBER(10) NOT NULL
CONSTRAINT fk_uroles_user
REFERENCES RED_USERS (user_id),
role_id NUMBER(5) NOT NULL
CONSTRAINT fk_uroles_role
REFERENCES RED_ROLES (role_id),
assigned_at DATE DEFAULT SYSDATE,
CONSTRAINT pk_user_roles PRIMARY KEY (user_id, role_id)
);What makes this a junction table?
RED_USER_ROLES has no ID column of its own. Instead, it uses a composite primary key made from user_id and role_id together. This means the same user can never be assigned the same role twice.
The REFERENCES keyword creates a foreign key. If you try to insert a row with a user_id that does not exist in RED_USERS, Oracle will reject the insert.
- Save schema.sql and confirm the composite primary key line reads CONSTRAINT pk_user_roles PRIMARY KEY (user_id, role_id).
- Now add the Administration module section and the RED_ADMIN_LOGS table below RED_USER_ROLES by pasting this code:
-- ============================================================
-- MODULE 6: ADMINISTRATION MODULE
-- ============================================================
CREATE TABLE RED_ADMIN_LOGS (
log_id NUMBER(10) CONSTRAINT pk_admin_logs PRIMARY KEY,
admin_id NUMBER(10) NOT NULL
CONSTRAINT fk_alogs_admin
REFERENCES RED_USERS (user_id),
action VARCHAR2(50) NOT NULL,
target_table VARCHAR2(50),
target_id NUMBER(10),
details VARCHAR2(500),
created_at DATE DEFAULT SYSDATE
);What does this table do?
RED_ADMIN_LOGS records which admin performed what action, on which table, and on which row. The admin_id column has a foreign key pointing back to RED_USERS. This enforces that only a real user can be recorded as the admin who took an action.
- Save schema.sql.
- Count the CREATE TABLE statements in the file. You should have exactly 4: RED_USERS, RED_ROLES, RED_USER_ROLES, and RED_ADMIN_LOGS.
Missing a table or seeing only 3?
Make sure you pasted each table after the previous one, not inside it. Each CREATE TABLE block must end with ); before the next one begins.
Still having trouble? Help me check my schema.sql file structure
✔️ Awesome, I've got everything!
Great. Double check you have saved schema.sql.
ⓧ I'd like to double check the full code
Since the full file is long, scroll up through schema.sql and confirm these sections appear in order:
- The file header comment block (RED Network Database Schema).
- Three CREATE SEQUENCE statements (seq_user_id, seq_role_id, seq_log_id).
- MODULE 1: USER MODULE section with RED_USERS, RED_ROLES, and RED_USER_ROLES tables.
- MODULE 6: ADMINISTRATION MODULE section with RED_ADMIN_LOGS table.
Create the user and admin route files
Your database schema defines what data exists. Now you need to define how the application accesses that data. Express.js route files map HTTP methods and URL paths to handler functions. For now, each handler returns a placeholder response so you can verify the routing structure works.
- Create a new file called userRoutes.js inside the routes/ folder.
- Add the user route stubs by pasting this code:
const express = require('express');
const router = express.Router();
// POST /api/users/register
router.post('/register', (req, res) => {
res.status(201).json({ message: 'User registration - not yet implemented' });
});
// POST /api/users/login
router.post('/login', (req, res) => {
res.json({ message: 'User login - not yet implemented' });
});
// GET /api/users/:id
router.get('/:id', (req, res) => {
res.json({ message: `Get user ${req.params.id} - not yet implemented` });
});
// PUT /api/users/:id
router.put('/:id', (req, res) => {
res.json({ message: `Update user ${req.params.id} - not yet implemented` });
});
// DELETE /api/users/:id
router.delete('/:id', (req, res) => {
res.json({ message: `Delete user ${req.params.id} - not yet implemented` });
});
module.exports = router;What does this code do?
express.Router() creates a mini-app that handles routes for one module. Each route defines an HTTP method (GET, POST, PUT, DELETE) and a path. The handler sends back a JSON placeholder so you can test that each route is reachable.
The :id syntax is a route parameter. When someone visits /api/users/42, Express captures 42 into req.params.id.
- Save userRoutes.js.
- Create another new file called adminRoutes.js inside the routes/ folder.
- Add the admin route stubs by pasting this code:
const express = require('express');
const router = express.Router();
// GET /api/admin/logs
router.get('/logs', (req, res) => {
res.json({ message: 'Get admin logs - not yet implemented' });
});
// GET /api/admin/users
router.get('/users', (req, res) => {
res.json({ message: 'Get all users (admin) - not yet implemented' });
});
// PUT /api/admin/users/:id/status
router.put('/users/:id/status', (req, res) => {
res.json({ message: `Update user ${req.params.id} status - not yet implemented` });
});
module.exports = router;What does this code do?
The admin routes are smaller. They cover viewing audit logs, listing all users from an admin perspective, and toggling a user's status (active, inactive, suspended). The PUT route at /users/:id/status maps directly to the status column and its CHECK constraint in RED_USERS.
- Save adminRoutes.js.
- Confirm both route files exist by running this in your terminal:
ls routes/You should see adminRoutes.js and userRoutes.js in the output.
The route files exist, but the server does not know about them yet. You need to import and mount them in server.js so Express connects each URL prefix to the right router.
- Open server.js in VS Code.
- Find the line that says app.use(express.json());.
- Add the following code directly below that line to import and mount the route modules:
// Import route modules
const userRoutes = require('./routes/userRoutes');
const adminRoutes = require('./routes/adminRoutes');
// Mount routes
app.use('/api/users', userRoutes);
app.use('/api/admin', adminRoutes);What does mounting a route do?
app.use('/api/users', userRoutes) tells Express to hand any request starting with /api/users to the userRoutes router. Inside userRoutes.js, the path /register becomes /api/users/register automatically.
- Save server.js.
✔️ Awesome, I've got everything!
Great. Double check you have saved server.js.
ⓧ I'd like to double check the full code
Here is the complete server.js at this point:
const express = require('express');
const app = express();
const PORT = 3000;
app.use(express.json());
// Import route modules
const userRoutes = require('./routes/userRoutes');
const adminRoutes = require('./routes/adminRoutes');
// Mount routes
app.use('/api/users', userRoutes);
app.use('/api/admin', adminRoutes);
// Root endpoint
app.get('/', (req, res) => {
res.send('RED Network API is running');
});
app.listen(PORT, () => {
console.log(`RED Network server running on http://localhost:${PORT}`);
});Now it is time to test the complete setup. Before you run this, what do you think will happen if one of the route file paths in the require() calls is wrong?
- If your server is still running from the previous step, stop it by pressing Ctrl+C in the terminal.
- Start the server again by running:
node server.jsYou should see RED Network server running on http://localhost:3000 with no errors. If the require() path was wrong, Node.js would crash with a MODULE_NOT_FOUND error instead.
- Open your browser and visit http://localhost:3000/api/users/1.
You should see a JSON response: {"message":"Get user 1 - not yet implemented"}. This proves the route is mounted correctly and the parameter :id captured the value 1.
Server crashed on startup?
If you see an error about a missing module, check that the file paths in server.js match the exact filenames in your routes/ folder. The names are case-sensitive.
If you see an error about the port already being in use, another process is still running on port 3000. Press Ctrl+C in every open terminal, then try node server.js again.
Still stuck? Help me fix my server.js route wiring
Your user and admin foundation is in place. You have 4 tables with named constraints, foreign key relationships, and route stubs that prove the server can load every module. Next up, you will build on this foundation by adding the resource and community tables that give the RED Network its purpose.
Design the Resource and Community Module Schema
In the last step, you built the foundation for the RED Network: users who can register, log in, and be tracked by admins. Right now, your system is an authentication shell with nothing for those users to actually do.
This step gives the platform its purpose. You will design the tables that let users list shareable resources and form communities around them. Every table you create here will link back to RED_USERS through foreign key constraints, enforcing the rule that only real users can own resources or join communities.
In this step, get ready to:
- Add resource module tables (RED_CATEGORIES, RED_RESOURCES, RED_RESOURCE_IMAGES) with foreign keys and constraints to schema.sql.
- Add community module tables (RED_COMMUNITIES, RED_COMMUNITY_MEMBERS, RED_POSTS, RED_COMMENTS) to schema.sql.
- Create route stub files for both modules and wire them into server.js.
Add the resource module sequences and categories table
Before you can create the resource tables, you need sequences to generate unique IDs for each new row. You also need a categories table so resources can be organized by type.
- In database/schema.sql, find the SEQUENCES section at the top of the file. Add these six new sequences below the existing three (seq_log_id is the last one):
CREATE SEQUENCE seq_category_id START WITH 1 INCREMENT BY 1 NOCACHE;
CREATE SEQUENCE seq_resource_id START WITH 1 INCREMENT BY 1 NOCACHE;
CREATE SEQUENCE seq_image_id START WITH 1 INCREMENT BY 1 NOCACHE;
CREATE SEQUENCE seq_community_id START WITH 1 INCREMENT BY 1 NOCACHE;
CREATE SEQUENCE seq_post_id START WITH 1 INCREMENT BY 1 NOCACHE;
CREATE SEQUENCE seq_comment_id START WITH 1 INCREMENT BY 1 NOCACHE;- Now scroll to the very bottom of the file, below the RED_ADMIN_LOGS table. Add the Resource Module section header and the RED_CATEGORIES table by pasting this code:
-- ============================================================
-- MODULE 2: RESOURCE MODULE
-- ============================================================
CREATE TABLE RED_CATEGORIES (
category_id NUMBER(5) CONSTRAINT pk_categories PRIMARY KEY,
name VARCHAR2(50) NOT NULL
CONSTRAINT uk_categories_name UNIQUE,
description VARCHAR2(200)
);What does this code do?
The six new sequences will auto-generate unique IDs for categories, resources, images, communities, posts, and comments. Each one mirrors the pattern you already used for seq_user_id.
RED_CATEGORIES is a lookup table. Resources will reference it through a foreign key so every resource belongs to exactly one category. The UNIQUE constraint on name prevents duplicate category names.
- Save schema.sql.
- Now add the main RED_RESOURCES table directly below RED_CATEGORIES by pasting this code:
CREATE TABLE RED_RESOURCES (
resource_id NUMBER(10) CONSTRAINT pk_resources PRIMARY KEY,
owner_id NUMBER(10) NOT NULL
CONSTRAINT fk_resources_owner
REFERENCES RED_USERS (user_id),
category_id NUMBER(5) NOT NULL
CONSTRAINT fk_resources_category
REFERENCES RED_CATEGORIES (category_id),
title VARCHAR2(100) NOT NULL,
description CLOB,
condition VARCHAR2(10) NOT NULL
CONSTRAINT ck_resources_condition
CHECK (condition IN ('new', 'good', 'fair', 'poor')),
quantity NUMBER(5) DEFAULT 1
CONSTRAINT ck_resources_qty
CHECK (quantity > 0),
location VARCHAR2(200),
availability_status VARCHAR2(20) DEFAULT 'available'
CONSTRAINT ck_resources_avail
CHECK (availability_status IN ('available', 'reserved', 'donated', 'exchanged')),
created_at DATE DEFAULT SYSDATE,
updated_at DATE DEFAULT SYSDATE
);What do these constraints enforce?
- fk_resources_owner links every resource to a real user in RED_USERS. No orphaned resources.
- ck_resources_condition restricts the condition column to exactly four valid values. The database rejects anything else.
- description uses the CLOB type instead of VARCHAR2 because resource descriptions can exceed the 4000-character limit.
- ck_resources_qty ensures quantity is always greater than zero. A resource that exists must have at least one unit.
- Save schema.sql. Scroll through the file and count your CREATE TABLE statements. You should see 6 tables total: the 4 from Step 2 plus RED_CATEGORIES and RED_RESOURCES.
Only counting 5 tables?
Make sure you pasted RED_CATEGORIES before RED_RESOURCES. The foreign key fk_resources_category references RED_CATEGORIES, so it must exist first in the file.
Also check that each table ends with ); on its own line. A missing closing parenthesis or semicolon will break the statement.
Help me check my schema.sql for errors
- Complete the resource module by adding the RED_RESOURCE_IMAGES table directly below RED_RESOURCES:
CREATE TABLE RED_RESOURCE_IMAGES (
image_id NUMBER(10) CONSTRAINT pk_res_images PRIMARY KEY,
resource_id NUMBER(10) NOT NULL
CONSTRAINT fk_images_resource
REFERENCES RED_RESOURCES (resource_id) ON DELETE CASCADE,
image_url VARCHAR2(500) NOT NULL,
is_primary NUMBER(1) DEFAULT 0
CONSTRAINT ck_images_primary
CHECK (is_primary IN (0, 1))
);Why ON DELETE CASCADE here?
When a resource is deleted, its images have no reason to exist. ON DELETE CASCADE tells Oracle to automatically remove all image rows that reference the deleted resource.
The is_primary column uses NUMBER(1) with a CHECK constraint instead of a BOOLEAN because Oracle does not have a native BOOLEAN type for table columns.
- Save schema.sql. You should now see 7 CREATE TABLE statements in the file.
Add the community module tables
Communities give users a place to connect beyond just swapping resources. The tables here follow a chain: a community has members, members create posts, and posts have comments.
- Below RED_RESOURCE_IMAGES, add the Community Module section header, the RED_COMMUNITIES table, and the RED_COMMUNITY_MEMBERS junction table:
-- ============================================================
-- MODULE 4: COMMUNITY MODULE
-- ============================================================
CREATE TABLE RED_COMMUNITIES (
community_id NUMBER(10) CONSTRAINT pk_communities PRIMARY KEY,
name VARCHAR2(100) NOT NULL,
description VARCHAR2(500),
created_by NUMBER(10) NOT NULL
CONSTRAINT fk_communities_creator
REFERENCES RED_USERS (user_id),
created_at DATE DEFAULT SYSDATE
);
CREATE TABLE RED_COMMUNITY_MEMBERS (
community_id NUMBER(10) NOT NULL
CONSTRAINT fk_cmembers_community
REFERENCES RED_COMMUNITIES (community_id),
user_id NUMBER(10) NOT NULL
CONSTRAINT fk_cmembers_user
REFERENCES RED_USERS (user_id),
role VARCHAR2(20) DEFAULT 'member'
CONSTRAINT ck_cmembers_role
CHECK (role IN ('admin', 'moderator', 'member')),
joined_at DATE DEFAULT SYSDATE,
CONSTRAINT pk_community_members PRIMARY KEY (community_id, user_id)
);What is a composite primary key?
RED_COMMUNITY_MEMBERS uses a composite primary key: PRIMARY KEY (community_id, user_id). This means the combination of both columns must be unique. A user can belong to many communities, and a community can have many users, but the same user cannot join the same community twice.
This is the same pattern you used for RED_USER_ROLES in Step 2. It is a standard way to model many-to-many relationships in a relational database.
- Save schema.sql. You should see 9 tables so far.
- Add the RED_POSTS and RED_COMMENTS tables below RED_COMMUNITY_MEMBERS to complete the community module:
CREATE TABLE RED_POSTS (
post_id NUMBER(10) CONSTRAINT pk_posts PRIMARY KEY,
community_id NUMBER(10) NOT NULL
CONSTRAINT fk_posts_community
REFERENCES RED_COMMUNITIES (community_id),
author_id NUMBER(10) NOT NULL
CONSTRAINT fk_posts_author
REFERENCES RED_USERS (user_id),
title VARCHAR2(200) NOT NULL,
content CLOB,
created_at DATE DEFAULT SYSDATE
);
CREATE TABLE RED_COMMENTS (
comment_id NUMBER(10) CONSTRAINT pk_comments PRIMARY KEY,
post_id NUMBER(10) NOT NULL
CONSTRAINT fk_comments_post
REFERENCES RED_POSTS (post_id) ON DELETE CASCADE,
author_id NUMBER(10) NOT NULL
CONSTRAINT fk_comments_author
REFERENCES RED_USERS (user_id),
content VARCHAR2(1000) NOT NULL,
created_at DATE DEFAULT SYSDATE
);What does this code do?
- RED_POSTS has two foreign keys: one to RED_COMMUNITIES (which community the post belongs to) and one to RED_USERS (who wrote it). Both are NOT NULL because a post must belong to a community and have an author.
- RED_COMMENTS uses ON DELETE CASCADE on fk_comments_post. If a post is deleted, all its comments are automatically removed too.
- content in RED_POSTS is a CLOB (same as resource descriptions) while content in RED_COMMENTS is a VARCHAR2(1000) because comments are typically shorter.
- Save schema.sql. Count your tables one more time. You should have 11 CREATE TABLE statements: 4 from the User and Admin modules, 3 from the Resource module, and 4 from the Community module.
Table count not adding up?
Use Ctrl+F (Windows) or Cmd+F (macOS) and search for CREATE TABLE to get an exact count. Make sure each section comment (MODULE 1, MODULE 6, MODULE 2, MODULE 4) appears only once.
Help me find what is missing from my schema.sql
✔️ Awesome, I've got everything!
Great. Make sure you have saved schema.sql.
ⓧ I'd like to double check the full code
Your schema.sql is too long to show inline. Scroll through the file and confirm these sections appear in order:
- SEQUENCES section with 9 total CREATE SEQUENCE statements (seq_user_id through seq_comment_id).
- MODULE 1: USER MODULE with RED_USERS, RED_ROLES, RED_USER_ROLES.
- MODULE 6: ADMINISTRATION MODULE with RED_ADMIN_LOGS.
- MODULE 2: RESOURCE MODULE with RED_CATEGORIES, RED_RESOURCES, RED_RESOURCE_IMAGES.
- MODULE 4: COMMUNITY MODULE with RED_COMMUNITIES, RED_COMMUNITY_MEMBERS, RED_POSTS, RED_COMMENTS.
Create route stubs for resources and communities
Each module in the schema needs a matching set of API routes. These are stubs for now. They define the HTTP methods and paths but return placeholder responses until you implement the real logic later.
- Create a new file called resourceRoutes.js inside the routes/ folder. Add the resource CRUD and category-filter routes by pasting this code:
const express = require('express');
const router = express.Router();
// List all resources
router.get('/', (req, res) => {
res.json({ message: 'GET all resources - not yet implemented' });
});
// Create a new resource
router.post('/', (req, res) => {
res.status(201).json({ message: 'Create resource - not yet implemented' });
});
// Get a single resource by ID
router.get('/:id', (req, res) => {
res.json({ message: `Get resource ${req.params.id} - not yet implemented` });
});
// Update a resource
router.put('/:id', (req, res) => {
res.json({ message: `Update resource ${req.params.id} - not yet implemented` });
});
// Delete a resource
router.delete('/:id', (req, res) => {
res.json({ message: `Delete resource ${req.params.id} - not yet implemented` });
});
// Filter resources by category
router.get('/category/:categoryId', (req, res) => {
res.json({ message: `Get resources in category ${req.params.categoryId} - not yet implemented` });
});
module.exports = router;What does this code do?
This file defines six route stubs covering the standard CRUD operations (create, read, update, delete) plus a category filter. Each handler sends back a JSON response confirming which route was hit. The :id and :categoryId segments are Express route parameters that capture values from the URL.
- Save routes/resourceRoutes.js.
- Create another new file called communityRoutes.js inside the routes/ folder. Add the community, post, and comment routes by pasting this code:
const express = require('express');
const router = express.Router();
// List all communities
router.get('/', (req, res) => {
res.json({ message: 'GET all communities - not yet implemented' });
});
// Create a community
router.post('/', (req, res) => {
res.status(201).json({ message: 'Create community - not yet implemented' });
});
// Get a single community
router.get('/:id', (req, res) => {
res.json({ message: `Get community ${req.params.id} - not yet implemented` });
});
// Join a community
router.post('/:id/join', (req, res) => {
res.json({ message: `Join community ${req.params.id} - not yet implemented` });
});
// Create a post in a community
router.post('/:id/posts', (req, res) => {
res.status(201).json({ message: `Create post in community ${req.params.id} - not yet implemented` });
});
// List posts in a community
router.get('/:id/posts', (req, res) => {
res.json({ message: `Get posts in community ${req.params.id} - not yet implemented` });
});
// Comment on a post
router.post('/posts/:postId/comments', (req, res) => {
res.status(201).json({ message: `Comment on post ${req.params.postId} - not yet implemented` });
});
module.exports = router;What does this code do?
This file covers the full community workflow: creating and listing communities, joining them, creating posts within a community, listing those posts, and commenting on posts. Notice how the routes nest naturally. /:id/posts scopes posts to a specific community, and /posts/:postId/comments scopes comments to a specific post.
- Save routes/communityRoutes.js.
- Confirm both route files exist by running this in your terminal:
ls routes/You should see four files: adminRoutes.js, communityRoutes.js, resourceRoutes.js, and userRoutes.js.
Wire the new routes into the server
The route files exist, but server.js does not know about them yet. You need to import them and mount them at their base paths.
- In server.js, find these two import lines near the top:
const userRoutes = require('./routes/userRoutes');
const adminRoutes = require('./routes/adminRoutes');- Add these two lines directly below them:
const resourceRoutes = require('./routes/resourceRoutes');
const communityRoutes = require('./routes/communityRoutes');- Now find the two mount lines:
app.use('/api/users', userRoutes);
app.use('/api/admin', adminRoutes);- Add these two lines directly below them:
app.use('/api/resources', resourceRoutes);
app.use('/api/communities', communityRoutes);- Save server.js.
Before you run the server, what do you think will happen when you visit /api/resources in your browser? Will you see an error, or a response?
- If the server is still running from earlier, stop it by pressing Ctrl+C in the terminal.
- Start the server by running:
node server.js- Open your browser and visit http://localhost:3000/api/resources.
You should see the placeholder JSON response:
{"message": "GET all resources - not yet implemented"}That confirms resourceRoutes.js is correctly wired into the server. The route stub is receiving requests and responding.
- Try http://localhost:3000/api/communities in your browser to confirm the community routes are working too. You should see {"message": "GET all communities - not yet implemented"}.
Seeing an error when you start the server?
- Check that your require paths match the file names exactly: ./routes/resourceRoutes and ./routes/communityRoutes. The file names are case-sensitive.
- Make sure each route file ends with module.exports = router;. Without this line, the server cannot import the router.
- Confirm that the import lines and mount lines are in separate places in server.js. The require calls go near the top, and the app.use calls go below them.
Help me debug my server.js wiring
- Open schema.sql and scan through the new tables. Confirm that every FOREIGN KEY constraint in the resource and community tables references a column that exists in a parent table. For example, fk_resources_owner should point to RED_USERS (user_id), and fk_posts_community should point to RED_COMMUNITIES (community_id).
✔️ Awesome, I've got everything!
Great. Make sure all your files are saved.
ⓧ I'd like to double check the full code
Here is the full server.js with all four route modules wired in:
const express = require('express');
const app = express();
const PORT = 3000;
app.use(express.json());
const userRoutes = require('./routes/userRoutes');
const adminRoutes = require('./routes/adminRoutes');
const resourceRoutes = require('./routes/resourceRoutes');
const communityRoutes = require('./routes/communityRoutes');
app.use('/api/users', userRoutes);
app.use('/api/admin', adminRoutes);
app.use('/api/resources', resourceRoutes);
app.use('/api/communities', communityRoutes);
app.get('/', (req, res) => {
res.send('RED Network API is running');
});
app.listen(PORT, () => {
console.log(`RED Network server running on http://localhost:${PORT}`);
});Here is the full routes/resourceRoutes.js:
const express = require('express');
const router = express.Router();
router.get('/', (req, res) => {
res.json({ message: 'GET all resources - not yet implemented' });
});
router.post('/', (req, res) => {
res.status(201).json({ message: 'Create resource - not yet implemented' });
});
router.get('/:id', (req, res) => {
res.json({ message: `Get resource ${req.params.id} - not yet implemented` });
});
router.put('/:id', (req, res) => {
res.json({ message: `Update resource ${req.params.id} - not yet implemented` });
});
router.delete('/:id', (req, res) => {
res.json({ message: `Delete resource ${req.params.id} - not yet implemented` });
});
router.get('/category/:categoryId', (req, res) => {
res.json({ message: `Get resources in category ${req.params.categoryId} - not yet implemented` });
});
module.exports = router;Here is the full routes/communityRoutes.js:
const express = require('express');
const router = express.Router();
router.get('/', (req, res) => {
res.json({ message: 'GET all communities - not yet implemented' });
});
router.post('/', (req, res) => {
res.status(201).json({ message: 'Create community - not yet implemented' });
});
router.get('/:id', (req, res) => {
res.json({ message: `Get community ${req.params.id} - not yet implemented` });
});
router.post('/:id/join', (req, res) => {
res.json({ message: `Join community ${req.params.id} - not yet implemented` });
});
router.post('/:id/posts', (req, res) => {
res.status(201).json({ message: `Create post in community ${req.params.id} - not yet implemented` });
});
router.get('/:id/posts', (req, res) => {
res.json({ message: `Get posts in community ${req.params.id} - not yet implemented` });
});
router.post('/posts/:postId/comments', (req, res) => {
res.status(201).json({ message: `Comment on post ${req.params.postId} - not yet implemented` });
});
module.exports = router;Your schema now has 11 tables and your API has four route modules responding to requests. Next up, you will tackle the trickiest part of the design: exchanges, donations, and notifications. These modules introduce relational patterns that a simple flat table cannot handle.
Design the Exchange, Donation, Search, and Notification Module Schema
You now have users who can own resources and participate in communities. Those tables set the foundation, but the RED Network exists so people can actually exchange and donate those resources to each other.
Modeling an exchange is trickier than it looks. A swap involves resources from both sides: User A offers a textbook, User B offers a laptop. How do you represent both sides of that trade in a relational database? This step forces you to solve that design problem before moving on to the search and notification tables that round out the full schema.
In this step, get ready to:
- Design exchange and donation tables using a parent-child table pattern.
- Add search logging and notification tables to complete the schema.
- Create four new route files, wire them into the server, and draft a design mapping document.
Design the exchange and donation tables
Picture this scenario: User A wants to trade their textbook for User B's laptop. You might think a single RED_EXCHANGES row with columns like resource_offered_id and resource_requested_id would do the job.
Before you write any SQL, think about what happens when User A wants to offer TWO items for User B's laptop. Where does the second resource go?
You run out of columns. A flat, single-row design breaks the moment either side offers more than one item. You would need resource_1_id, resource_2_id, resource_3_id and so on, with no clear limit. That is the gap a parent-child table pattern closes.
Why a parent-child table pattern?
Instead of cramming both sides of a swap into one row, you split the design into two tables. A parent table (RED_EXCHANGES) stores the exchange status and who is involved. A child table (RED_EXCHANGE_ITEMS) stores each individual resource being offered, with a foreign key back to the parent.
Each side can offer as many items as they want because each item is its own row. This header-detail pattern is one of the most common relational design patterns in production systems.
With that design decision made, start by adding the new sequences your exchange, donation, search, and notification tables will need.
- In database/schema.sql, scroll to the SEQUENCES section at the top of the file.
- Find the last sequence line:
CREATE SEQUENCE seq_comment_id START WITH 1 INCREMENT BY 1 NOCACHE;- Add these five new sequences directly below it:
CREATE SEQUENCE seq_exchange_id START WITH 1 INCREMENT BY 1 NOCACHE;
CREATE SEQUENCE seq_exchange_item_id START WITH 1 INCREMENT BY 1 NOCACHE;
CREATE SEQUENCE seq_donation_req_id START WITH 1 INCREMENT BY 1 NOCACHE;
CREATE SEQUENCE seq_search_id START WITH 1 INCREMENT BY 1 NOCACHE;
CREATE SEQUENCE seq_notification_id START WITH 1 INCREMENT BY 1 NOCACHE;What do these sequences do?
Each sequence generates unique IDs for one table. Oracle SQL does not have auto-increment columns like some other databases. Sequences are Oracle's way of producing incrementing numeric values for primary keys.
Now build the parent table for the exchange module. This table tracks who is exchanging with whom and the overall status of the swap.
- Scroll to the bottom of schema.sql, below the RED_COMMENTS table.
- Add the Exchange and Donation module section with the RED_EXCHANGES table:
-- ============================================================
-- MODULE 3: EXCHANGE AND DONATION MODULE
-- ============================================================
CREATE TABLE RED_EXCHANGES (
exchange_id NUMBER(10) CONSTRAINT pk_exchanges PRIMARY KEY,
initiator_id NUMBER(10) NOT NULL
CONSTRAINT fk_exchanges_initiator
REFERENCES RED_USERS (user_id),
receiver_id NUMBER(10) NOT NULL
CONSTRAINT fk_exchanges_receiver
REFERENCES RED_USERS (user_id),
status VARCHAR2(20) DEFAULT 'proposed'
CONSTRAINT ck_exchanges_status
CHECK (status IN ('proposed', 'accepted', 'completed', 'cancelled')),
message VARCHAR2(500),
created_at DATE DEFAULT SYSDATE,
updated_at DATE DEFAULT SYSDATE
);What does this table do?
- initiator_id and receiver_id both reference RED_USERS. Two foreign keys to the same parent table let the database enforce that both participants are real users.
- The status CHECK constraint locks the column to four valid states, preventing invalid strings from slipping in.
- This is the parent row. It says nothing about which resources are being exchanged. That is the child table's job.
Now add the child table that solves the single-row problem you identified earlier. Each row in this table represents one resource offered by one user as part of an exchange.
- Add RED_EXCHANGE_ITEMS directly below RED_EXCHANGES:
CREATE TABLE RED_EXCHANGE_ITEMS (
item_id NUMBER(10) CONSTRAINT pk_exchange_items PRIMARY KEY,
exchange_id NUMBER(10) NOT NULL
CONSTRAINT fk_eitems_exchange
REFERENCES RED_EXCHANGES (exchange_id) ON DELETE CASCADE,
resource_id NUMBER(10) NOT NULL
CONSTRAINT fk_eitems_resource
REFERENCES RED_RESOURCES (resource_id),
offered_by NUMBER(10) NOT NULL
CONSTRAINT fk_eitems_offerer
REFERENCES RED_USERS (user_id)
);How does the child table fix the problem?
Each item row has a foreign key (exchange_id) pointing back to its parent exchange. User A can add 1 item, 3 items, or 10 items to the same exchange. Each becomes its own row.
ON DELETE CASCADE means that if the parent exchange is deleted, all its child item rows are automatically removed. No orphaned rows left behind.
- Save schema.sql.
- Scroll through the Exchange module section you just added. You should see two tables: RED_EXCHANGES (the parent) followed by RED_EXCHANGE_ITEMS (the child).
Donations are simpler than exchanges. A donation request is one-directional: someone asks for a specific resource. No child table needed because there is only one resource per request.
- Add RED_DONATION_REQUESTS below RED_EXCHANGE_ITEMS:
CREATE TABLE RED_DONATION_REQUESTS (
request_id NUMBER(10) CONSTRAINT pk_donation_reqs PRIMARY KEY,
resource_id NUMBER(10) NOT NULL
CONSTRAINT fk_dreqs_resource
REFERENCES RED_RESOURCES (resource_id),
requester_id NUMBER(10) NOT NULL
CONSTRAINT fk_dreqs_requester
REFERENCES RED_USERS (user_id),
message VARCHAR2(500),
status VARCHAR2(20) DEFAULT 'pending'
CONSTRAINT ck_dreqs_status
CHECK (status IN ('pending', 'approved', 'rejected', 'completed')),
created_at DATE DEFAULT SYSDATE,
updated_at DATE DEFAULT SYSDATE
);What does this table do?
- resource_id links the request to a specific resource in RED_RESOURCES. The requester is asking for that exact item.
- The status CHECK constraint limits values to 'pending', 'approved', 'rejected', or 'completed'. A donation request flows through these states as the resource owner responds.
- Save schema.sql.
- Count the tables in your Exchange and Donation module section. You should see three: RED_EXCHANGES, RED_EXCHANGE_ITEMS, and RED_DONATION_REQUESTS.
Missing a constraint name?
Every constraint in this schema uses the CONSTRAINT keyword followed by a unique name (like fk_dreqs_resource). If you left out the name, Oracle will auto-generate one like SYS_C007352. Go back and make sure every PRIMARY KEY, FOREIGN KEY, and CHECK has an explicit name.
Need help debugging your schema?
Add the search and notification tables
The final two tables serve supporting roles. Search logs record what users look for so you can analyze usage patterns. Notifications alert users when something relevant happens across any module.
- Below RED_DONATION_REQUESTS, add the Search and Notification module section with RED_SEARCH_LOGS:
-- ============================================================
-- MODULE 5: SEARCH AND NOTIFICATION MODULE
-- ============================================================
CREATE TABLE RED_SEARCH_LOGS (
search_id NUMBER(10) CONSTRAINT pk_search_logs PRIMARY KEY,
user_id NUMBER(10) CONSTRAINT fk_slogs_user
REFERENCES RED_USERS (user_id),
search_query VARCHAR2(200) NOT NULL,
filters VARCHAR2(500),
searched_at DATE DEFAULT SYSDATE
);What does this table do?
RED_SEARCH_LOGS records every search a user performs. The user_id foreign key is NOT marked NOT NULL because anonymous (non-logged-in) searches could still be logged for analytics.
- Add RED_NOTIFICATIONS below RED_SEARCH_LOGS:
CREATE TABLE RED_NOTIFICATIONS (
notification_id NUMBER(10) CONSTRAINT pk_notifications PRIMARY KEY,
user_id NUMBER(10) NOT NULL
CONSTRAINT fk_notif_user
REFERENCES RED_USERS (user_id),
entity_type VARCHAR2(30) NOT NULL
CONSTRAINT ck_notif_entity
CHECK (entity_type IN ('exchange', 'donation_request', 'community', 'system')),
entity_id NUMBER(10),
message VARCHAR2(500) NOT NULL,
is_read NUMBER(1) DEFAULT 0
CONSTRAINT ck_notif_read
CHECK (is_read IN (0, 1)),
created_at DATE DEFAULT SYSDATE
);Why does entity_type use a CHECK constraint instead of a foreign key?
A notification can be about an exchange, a donation request, a community event, or a system-wide message. The entity_id column stores the ID of the related record, but it could point at RED_EXCHANGES, RED_DONATION_REQUESTS, or RED_COMMUNITIES depending on the type.
A single foreign key cannot reference multiple parent tables. The entity_type column acts as a discriminator that tells the application which table to look up. This is a standard polymorphic reference pattern used in production notification systems.
Why is is_read a NUMBER(1) instead of BOOLEAN?
Oracle SQL does not have a native BOOLEAN column type. NUMBER(1) with a CHECK constraint restricting values to 0 and 1 is the standard Oracle pattern for true/false columns.
- Save schema.sql.
- Scroll through the entire file and count your CREATE TABLE statements. You should have 16 tables total across all modules.
Not seeing 16 tables?
Use Ctrl+F (Windows) or Cmd+F (macOS) to search for CREATE TABLE in the file. Each match is one table definition. If you are short, check that you did not accidentally paste a table inside another table's definition.
Help me find which table I'm missing in my schema.
✔️ Awesome, I've got everything!
Great. Make sure you have saved schema.sql.
ⓧ I'd like to double check the full code
Your schema.sql is now too long to display in a single block. Scroll through the file and confirm these sections appear in order:
- SEQUENCES section with 14 CREATE SEQUENCE statements (ending with seq_notification_id).
- MODULE 1 (User): RED_USERS, RED_ROLES, RED_USER_ROLES.
- MODULE 6 (Administration): RED_ADMIN_LOGS.
- MODULE 2 (Resource): RED_CATEGORIES, RED_RESOURCES, RED_RESOURCE_IMAGES.
- MODULE 4 (Community): RED_COMMUNITIES, RED_COMMUNITY_MEMBERS, RED_POSTS, RED_COMMENTS.
- MODULE 3 (Exchange and Donation): RED_EXCHANGES, RED_EXCHANGE_ITEMS, RED_DONATION_REQUESTS.
- MODULE 5 (Search and Notification): RED_SEARCH_LOGS, RED_NOTIFICATIONS.
Create the route files and design document
With the schema complete, you need matching route files so your Express.js server knows which API endpoints each module will expose. You have already done this for users, admin, resources, and communities. The pattern is the same here.
- Create a new file routes/exchangeRoutes.js and add the exchange route stubs:
const express = require('express');
const router = express.Router();
// Propose a new exchange
router.post('/', (req, res) => {
res.status(201).json({ message: 'Create exchange - not yet implemented' });
});
// Get exchange details
router.get('/:id', (req, res) => {
res.json({ message: `Get exchange ${req.params.id} - not yet implemented` });
});
// Update exchange status
router.put('/:id/status', (req, res) => {
res.json({ message: `Update exchange ${req.params.id} status - not yet implemented` });
});
// Get all exchanges for a user
router.get('/user/:userId', (req, res) => {
res.json({ message: `Get exchanges for user ${req.params.userId} - not yet implemented` });
});
module.exports = router;- Create routes/donationRoutes.js and add the donation route stubs:
const express = require('express');
const router = express.Router();
// Create a donation request
router.post('/request', (req, res) => {
res.status(201).json({ message: 'Create donation request - not yet implemented' });
});
// Get all donation requests
router.get('/requests', (req, res) => {
res.json({ message: 'Get all donation requests - not yet implemented' });
});
// Get a specific donation request
router.get('/requests/:id', (req, res) => {
res.json({ message: `Get donation request ${req.params.id} - not yet implemented` });
});
// Update donation request status
router.put('/requests/:id/status', (req, res) => {
res.json({ message: `Update donation request ${req.params.id} status - not yet implemented` });
});
module.exports = router;What do these route stubs do?
Each route defines an HTTP method and path that the API will respond to. The placeholder responses confirm the route is wired correctly. When you implement the database layer later, you replace these placeholders with real query logic.
Now wire the exchange and donation routes into server.js so Express loads them at startup.
- Switch back to server.js.
- Find this line near the top:
const communityRoutes = require('./routes/communityRoutes');- Add these two import lines directly below it:
const exchangeRoutes = require('./routes/exchangeRoutes');
const donationRoutes = require('./routes/donationRoutes');- Find this line further down:
app.use('/api/communities', communityRoutes);- Add these two mount lines directly below it:
app.use('/api/exchanges', exchangeRoutes);
app.use('/api/donations', donationRoutes);- Save server.js.
- If the server is still running from earlier, press Ctrl+C in the terminal to stop it, then restart it by running:
node server.js- You should see RED Network server running on http://localhost:3000 with no errors.
- Open your browser and visit http://localhost:3000/api/exchanges. You should see the placeholder JSON response from your new exchange route.
Seeing an error about a missing module?
Check that your require path exactly matches the file name. './routes/exchangeRoutes' must match a file named exchangeRoutes.js in the routes/ folder. File names are case-sensitive.
Why is my server failing to start after adding new routes?
Now stop the server (Ctrl+C) and create the remaining two route files for search and notifications.
- Create routes/searchRoutes.js and add the search route stubs:
const express = require('express');
const router = express.Router();
// Search resources
router.get('/resources', (req, res) => {
res.json({ message: 'Search resources - not yet implemented', query: req.query });
});
// Search users
router.get('/users', (req, res) => {
res.json({ message: 'Search users - not yet implemented', query: req.query });
});
// Search communities
router.get('/communities', (req, res) => {
res.json({ message: 'Search communities - not yet implemented', query: req.query });
});
module.exports = router;- Create routes/notificationRoutes.js and add the notification route stubs:
const express = require('express');
const router = express.Router();
// Get all notifications for the current user
router.get('/', (req, res) => {
res.json({ message: 'Get notifications - not yet implemented' });
});
// Mark a single notification as read
router.put('/:id/read', (req, res) => {
res.json({ message: `Mark notification ${req.params.id} as read - not yet implemented` });
});
// Mark all notifications as read
router.put('/read-all', (req, res) => {
res.json({ message: 'Mark all notifications as read - not yet implemented' });
});
module.exports = router;Wire the search and notification routes into server.js the same way you added the exchange and donation routes.
- In server.js, find the donation import line you just added:
const donationRoutes = require('./routes/donationRoutes');- Add these two import lines directly below it:
const searchRoutes = require('./routes/searchRoutes');
const notificationRoutes = require('./routes/notificationRoutes');- Find the donation mount line:
app.use('/api/donations', donationRoutes);- Add these two mount lines directly below it:
app.use('/api/search', searchRoutes);
app.use('/api/notifications', notificationRoutes);- Save server.js.
The last artifact for this step is a draft design document that maps each module to its tables and route files. This becomes your consistency reference in the next step.
- Create a new file docs/DESIGN.md and add this module overview table:
# RED Network - System Design Document
## Module Overview
| Module | Tables | Route File |
|--------|--------|------------|
| User | RED_USERS, RED_ROLES, RED_USER_ROLES | userRoutes.js |
| Resource | RED_CATEGORIES, RED_RESOURCES, RED_RESOURCE_IMAGES | resourceRoutes.js |
| Exchange and Donation | RED_EXCHANGES, RED_EXCHANGE_ITEMS, RED_DONATION_REQUESTS | exchangeRoutes.js, donationRoutes.js |
| Community | RED_COMMUNITIES, RED_COMMUNITY_MEMBERS, RED_POSTS, RED_COMMENTS | communityRoutes.js |
| Search and Notification | RED_SEARCH_LOGS, RED_NOTIFICATIONS | searchRoutes.js, notificationRoutes.js |
| Administration | RED_ADMIN_LOGS | adminRoutes.js |
(Draft - to be completed in Step 5)What is DESIGN.md for?
This document is your single point of reference connecting every module to its database tables and API route files. In the next step, you will expand it with foreign key relationships and a full API summary. For now, the module overview table is enough to prove your six modules are covered.
- Save DESIGN.md.
Time for the end-of-step verification. You will check two things: that the donation scenario traces cleanly through your schema, and that the server loads all 8 route modules without errors.
Before you restart the server, think about this: you have 8 route files wired into server.js. If any one of them has a typo in its require path or a syntax error, what do you expect to see?
- Restart the server by running:
node server.jsYou should see RED Network server running on http://localhost:3000 with zero errors. That confirms all 8 route modules loaded successfully.
- Open your browser and visit http://localhost:3000/api/resources. You should see {"message": "GET all resources - not yet implemented"}.
Now trace through a donation scenario in schema.sql. A user creates a resource (RED_RESOURCES), someone requests it (RED_DONATION_REQUESTS with resource_id FK), the owner approves (status changes to 'approved'), and a notification is created (RED_NOTIFICATIONS with entity_type = 'donation_request'). Every foreign key in that chain resolves to a valid parent table.
✔️ Awesome, I've got everything!
Great. Make sure you have saved all files, including server.js and DESIGN.md.
ⓧ I'd like to double check the full code
Your server.js should now have 8 route imports and 8 route mounts. Scroll through and confirm these appear in order:
- Imports: userRoutes, adminRoutes, resourceRoutes, communityRoutes, exchangeRoutes, donationRoutes, searchRoutes, notificationRoutes.
- Mounts: /api/users, /api/admin, /api/resources, /api/communities, /api/exchanges, /api/donations, /api/search, /api/notifications.
- Root endpoint: app.get('/') returning 'RED Network API is running'.
Your schema now covers all six RED Network modules with 16 tables, and your server loads all 8 route files without errors. Next up, you will validate the entire design end-to-end and complete the design document with foreign key mappings and a full API summary.
Validate and Document the Complete System Design
Your schema now covers all six RED Network modules, and all eight route files respond to requests. The individual pieces are in place.
But pieces that look correct on their own can still break when connected. A foreign key might reference a column that does not exist in the parent table. A route file might define an endpoint that no table supports. This step is where you audit the complete design as a unified system, catching inconsistencies before they become bugs.
In this step, get ready to:
- Add an API index endpoint that lists every route across all modules.
- Complete the DESIGN.md documentation with foreign key relationships and a full API summary.
- Validate the entire system by tracing foreign key chains and confirming zero server errors.
Add an API index endpoint to server.js
Right now, someone reading your project has to open each route file individually to understand what the API offers. An API index endpoint solves this by returning every available route from a single URL.
- In server.js, find the root endpoint block that contains res.send('RED Network API is running').
- Below its closing });, start the API index endpoint by pasting the first chunk of code below. This chunk covers the Users and Admin modules:
// API index endpoint
app.get('/api', (req, res) => {
res.json({
name: 'RED Network API',
version: '1.0.0',
modules: {
users: {
base: '/api/users',
endpoints: [
'POST /api/users/register',
'POST /api/users/login',
'GET /api/users/:id',
'PUT /api/users/:id',
'DELETE /api/users/:id'
]
},
admin: {
base: '/api/admin',
endpoints: [
'GET /api/admin/logs',
'GET /api/admin/users',
'PUT /api/admin/users/:id/status'
]
},Red squiggles are expected
Your editor will show syntax errors until all three code chunks are in place. The brackets are not closed yet, so VS Code will flag the file. Keep pasting.
- Directly below the admin section you just pasted (after the trailing comma), add the Resources and Communities modules:
resources: {
base: '/api/resources',
endpoints: [
'GET /api/resources',
'POST /api/resources',
'GET /api/resources/:id',
'PUT /api/resources/:id',
'DELETE /api/resources/:id',
'GET /api/resources/category/:categoryId'
]
},
communities: {
base: '/api/communities',
endpoints: [
'GET /api/communities',
'POST /api/communities',
'GET /api/communities/:id',
'POST /api/communities/:id/join',
'POST /api/communities/:id/posts',
'GET /api/communities/:id/posts',
'POST /api/communities/posts/:postId/comments'
]
},- Directly below the communities section, add the final four modules and close the route handler:
exchanges: {
base: '/api/exchanges',
endpoints: [
'POST /api/exchanges',
'GET /api/exchanges/:id',
'PUT /api/exchanges/:id/status',
'GET /api/exchanges/user/:userId'
]
},
donations: {
base: '/api/donations',
endpoints: [
'POST /api/donations/request',
'GET /api/donations/requests',
'GET /api/donations/requests/:id',
'PUT /api/donations/requests/:id/status'
]
},
search: {
base: '/api/search',
endpoints: [
'GET /api/search/resources',
'GET /api/search/users',
'GET /api/search/communities'
]
},
notifications: {
base: '/api/notifications',
endpoints: [
'GET /api/notifications',
'PUT /api/notifications/:id/read',
'PUT /api/notifications/read-all'
]
}
}
});
});What does this endpoint do?
The GET /api endpoint returns a single JSON object that maps every module to its base path and full list of endpoints. Anyone who hits this URL gets a complete table of contents for the entire API.
This is a living reference. As you add real logic to each route later, the index stays accurate because it lists the paths that already exist in your route files.
- Confirm the red squiggles are gone. The closing }); from the third chunk should close everything cleanly.
- Save server.js.
- If your server is still running from a previous step, stop it by pressing Ctrl+C in the terminal.
- Start the server by running:
node server.jsBefore you open the browser, think about what you expect to see at http://localhost:3000/api. Will it be plain text like the root endpoint, or something different?
- Open your browser and visit http://localhost:3000/api.
You should see a JSON object with a modules key containing all eight modules. Each module lists its base path and every endpoint registered in its route file.
Seeing a syntax error or blank page?
Check that all three code chunks are pasted in sequence. A missing closing bracket from the third chunk will prevent the file from running.
Make sure the app.get('/api', ...) block sits between the root endpoint (app.get('/', ...)) and app.listen(). If it is outside or overlapping another block, the server will crash.
help me fix a syntax error in my server.js API index endpoint
✔️ Awesome, I've got everything!
Great. Double check you have saved server.js and your server is running.
ⓧ I'd like to double check the full code
The full server.js file is too long for a single code block. Scroll through your file and confirm each of these sections is present, in order:
- The require imports for express and all 8 route modules.
- Eight app.use() lines mounting each route at its /api/... path.
- The root endpoint (app.get('/')) returning 'RED Network API is running'.
- The API index endpoint (app.get('/api')) with all 8 modules listed inside the modules object: users, admin, resources, communities, exchanges, donations, search, notifications.
- The app.listen(PORT, ...) call at the bottom.
Complete the design document
Your DESIGN.md currently has a Module Overview table, but no record of which tables depend on each other or what each API route does. Adding those sections turns the document into a consistency reference you can cross-check against schema.sql and your route files.
- In docs/DESIGN.md, find the line that says (Draft - to be completed in Step 5).
- Delete that line.
- Below the Module Overview table, add the Database Relationships section by pasting this code:
## Database Relationships
| Foreign Key | Child Table | Parent Table | Column |
|-------------|-------------|--------------|--------|
| fk_uroles_user | RED_USER_ROLES | RED_USERS | user_id |
| fk_uroles_role | RED_USER_ROLES | RED_ROLES | role_id |
| fk_alogs_admin | RED_ADMIN_LOGS | RED_USERS | admin_id -> user_id |
| fk_resources_owner | RED_RESOURCES | RED_USERS | owner_id -> user_id |
| fk_resources_category | RED_RESOURCES | RED_CATEGORIES | category_id |
| fk_images_resource | RED_RESOURCE_IMAGES | RED_RESOURCES | resource_id |
| fk_communities_creator | RED_COMMUNITIES | RED_USERS | created_by -> user_id |
| fk_cmembers_community | RED_COMMUNITY_MEMBERS | RED_COMMUNITIES | community_id |
| fk_cmembers_user | RED_COMMUNITY_MEMBERS | RED_USERS | user_id |
| fk_posts_community | RED_POSTS | RED_COMMUNITIES | community_id |
| fk_posts_author | RED_POSTS | RED_USERS | author_id -> user_id |
| fk_comments_post | RED_COMMENTS | RED_POSTS | post_id |
| fk_comments_author | RED_COMMENTS | RED_USERS | author_id -> user_id |
| fk_exchanges_initiator | RED_EXCHANGES | RED_USERS | initiator_id -> user_id |
| fk_exchanges_receiver | RED_EXCHANGES | RED_USERS | receiver_id -> user_id |
| fk_eitems_exchange | RED_EXCHANGE_ITEMS | RED_EXCHANGES | exchange_id |
| fk_eitems_resource | RED_EXCHANGE_ITEMS | RED_RESOURCES | resource_id |
| fk_eitems_offerer | RED_EXCHANGE_ITEMS | RED_USERS | offered_by -> user_id |
| fk_dreqs_resource | RED_DONATION_REQUESTS | RED_RESOURCES | resource_id |
| fk_dreqs_requester | RED_DONATION_REQUESTS | RED_USERS | requester_id -> user_id |
| fk_slogs_user | RED_SEARCH_LOGS | RED_USERS | user_id |
| fk_notif_user | RED_NOTIFICATIONS | RED_USERS | user_id |What does this table document?
Each row maps a named foreign key constraint to the child table that holds it and the parent table it references. The Column field shows which column in the child maps to which column in the parent.
There are 22 rows. Every one of them corresponds to a CONSTRAINT ... REFERENCES line in schema.sql. If a constraint exists in the schema but not in this table (or vice versa), your design has a gap.
- Save docs/DESIGN.md.
- Count the rows in the Database Relationships table. You should see exactly 22 foreign key entries.
Now add the API Summary section. This section documents every route with its HTTP method, path, and description. Below the Database Relationships table, paste the first batch covering the User, Administration, and Resource modules:
## API Summary
### User Module
| Method | Path | Description |
|--------|------|-------------|
| POST | /api/users/register | Register a new user |
| POST | /api/users/login | Authenticate user |
| GET | /api/users/:id | Get user profile |
| PUT | /api/users/:id | Update user profile |
| DELETE | /api/users/:id | Delete user account |
### Administration Module
| Method | Path | Description |
|--------|------|-------------|
| GET | /api/admin/logs | View admin activity logs |
| GET | /api/admin/users | List all users (admin view) |
| PUT | /api/admin/users/:id/status | Update user status |
### Resource Module
| Method | Path | Description |
|--------|------|-------------|
| GET | /api/resources | List all resources |
| POST | /api/resources | Create a resource listing |
| GET | /api/resources/:id | Get resource details |
| PUT | /api/resources/:id | Update resource |
| DELETE | /api/resources/:id | Delete resource |
| GET | /api/resources/category/:categoryId | Filter resources by category |- Below the Resource Module table, continue pasting the Community, Exchange, and Donation modules:
### Community Module
| Method | Path | Description |
|--------|------|-------------|
| GET | /api/communities | List all communities |
| POST | /api/communities | Create a community |
| GET | /api/communities/:id | Get community details |
| POST | /api/communities/:id/join | Join a community |
| POST | /api/communities/:id/posts | Create a post |
| GET | /api/communities/:id/posts | List posts in community |
| POST | /api/communities/posts/:postId/comments | Comment on a post |
### Exchange Module
| Method | Path | Description |
|--------|------|-------------|
| POST | /api/exchanges | Propose an exchange |
| GET | /api/exchanges/:id | Get exchange details |
| PUT | /api/exchanges/:id/status | Update exchange status |
| GET | /api/exchanges/user/:userId | Get user exchanges |
### Donation Module
| Method | Path | Description |
|--------|------|-------------|
| POST | /api/donations/request | Create donation request |
| GET | /api/donations/requests | List donation requests |
| GET | /api/donations/requests/:id | Get request details |
| PUT | /api/donations/requests/:id/status | Update request status |- Finally, add the Search and Notification modules below the Donation table:
### Search Module
| Method | Path | Description |
|--------|------|-------------|
| GET | /api/search/resources | Search resources |
| GET | /api/search/users | Search users |
| GET | /api/search/communities | Search communities |
### Notification Module
| Method | Path | Description |
|--------|------|-------------|
| GET | /api/notifications | Get user notifications |
| PUT | /api/notifications/:id/read | Mark notification as read |
| PUT | /api/notifications/read-all | Mark all as read |Why document every route?
Each row in the API Summary corresponds to a router.get(), router.post(), router.put(), or router.delete() call in one of your route files. If a row appears here but no matching route exists in code, or a route exists in code but no row appears here, you have a drift between design and implementation.
- Save docs/DESIGN.md.
- Scroll through the API Summary. You should see eight module sub-sections, each with a table listing its routes. Confirm that all six modules from the Module Overview table are covered (Exchange and Donation are split into separate sub-sections).
Table not rendering correctly in the preview?
Make sure every table row starts and ends with a pipe character (|). A missing pipe will break the markdown table formatting.
Check that each section heading uses three hashes (###) with a space before the module name.
help me fix a broken markdown table in DESIGN.md
✔️ Awesome, I've got everything!
Great. Your DESIGN.md is complete. Make sure you have saved the file.
ⓧ I'd like to double check the full code
The full DESIGN.md is too long for a single code block. Scroll through your file and confirm each of these sections is present, in order:
- The # RED Network - System Design Document heading at the top.
- The ## Module Overview table with 6 rows (one per module). No draft note below it.
- The ## Database Relationships table with 22 foreign key rows.
- The ## API Summary section with 8 sub-section tables: User, Administration, Resource, Community, Exchange, Donation, Search, Notification.
Run the final validation
Your documentation and API index are in place. The last task is to verify that the whole system holds together. Every foreign key in schema.sql must reference a table and column that actually exist.
- Open database/schema.sql in the editor.
- Use Ctrl+F (Windows) or Cmd+F (macOS) and search for REFERENCES.
- For each match, confirm the referenced table name (e.g., RED_USERS) exists as a CREATE TABLE earlier in the file, and that the referenced column (e.g., user_id) exists in that table.
What am I checking for?
A foreign key that references a table or column that does not exist would cause an error when Oracle tries to create the table. Tracing each REFERENCES clause manually catches these mismatches before you ever run the schema against a live database.
Pay special attention to columns that use a different name than the parent table's primary key. For example, fk_alogs_admin uses admin_id in RED_ADMIN_LOGS but references user_id in RED_USERS.
Now confirm the server loads every route module cleanly. Before you run this, do you expect any errors from the server? Think about whether all your route files export a valid router.
- If the server is still running, stop it with Ctrl+C.
- Start the server by running:
node server.jsYou should see RED Network server running on http://localhost:3000 with no errors above it. If any route file had a typo or missing export, Node.js would crash here with an error message naming the file.
- Open your browser and visit http://localhost:3000/api.
- Confirm the JSON response lists all 8 modules with their endpoints.
- Open docs/DESIGN.md and verify that each of the six modules in the Module Overview table has at least one table in the Database Relationships section and at least two routes in the API Summary.
Server crashing on startup?
Read the error message. It will name the file and line number where the problem is. Common causes are a missing module.exports = router at the bottom of a route file, or a typo in a require() path in server.js.
help me debug why my RED Network server is crashing on startup
Your complete system design is validated. Every table, constraint, route, and document cross-references correctly. Next up, the Secret Mission takes your schema to the next level with Oracle PL/SQL procedures and optimized views.
Secret mission
Add PL/SQL Procedures and Optimized Views
Your schema is complete, but right now all the matching logic between donors and requesters would live in application code. Real production databases push that kind of work into the database layer. Can you write Oracle PL/SQL procedures and optimized views that handle auto-matching directly in SQL?
Clean Up Your Resources
Clean Up Your Resources
This project runs entirely on your local machine. There are no cloud services, paid APIs, or ongoing costs to worry about.
Decide whether to keep your resources running, pause them to come back later, or delete them entirely.
Resources you used:
- The red-network project folder on your Desktop (includes all source files, node_modules, and design documents).
- Node.js v24 LTS and npm (installed during Step 1).
- VS Code (installed during Step 1).
Keep everything running
No action needed. Choose this if you are still actively building on the RED Network project or plan to start implementing the route logic soon.
- Your red-network folder, Node.js, and VS Code all stay on your machine ready for your next coding session.
- If your Express server is still running in the terminal, you can stop it with Ctrl+C when you are done for the day. Restart it anytime with node server.js.
Pause - I'll come back to this later
Shut down the running server to free up your terminal. All your files stay on disk, ready to pick up later.
- Switch to the terminal where your Express server is running.
- Press Ctrl+C to stop the server.
- When you are ready to resume, open the red-network folder in VS Code, open a terminal, and run node server.js to start the server again.
Delete - I don't want to use this again
Remove all project files and optionally uninstall the tools you set up. This is a full reset.
- If your Express server is still running, press Ctrl+C in the terminal to stop it.
- Close VS Code so no files in the project folder are locked.
Delete the project folder by running one of these commands in a new terminal, depending on your operating system:
macOS
cd ~/Desktop
rm -rf red-networkWindows
cd ~\Desktop
rmdir /s /q red-network- Confirm the folder is gone by running ls (macOS) or dir (Windows) on your Desktop. You should no longer see red-network listed.
To uninstall Node.js and VS Code (optional, skip if you use them for other projects):
- On Windows, open Settings, select Apps, then Installed apps.
- Search for Node.js, click the three-dot menu, and select Uninstall. This removes both Node.js and npm.
- Search for Microsoft Visual Studio Code, click the three-dot menu, and select Uninstall.
Nice Work!
Nice Work!
Nice work! You just designed a complete, code-ready system for the RED Network platform.
You've learned how to:
- Design a complete Oracle SQL schema with 16 tables, sequences, named constraints, and foreign key relationships covering all six RED Network modules.
- Build an Express.js project with organized route files defining the full REST API across user, resource, exchange, donation, community, search, notification, and administration endpoints.
- Create a DESIGN.md mapping document proving consistency between every module, table, and API route, then validate the complete system by tracing foreign key chains end-to-end.
- Secret Mission: wrote Oracle PL/SQL procedures and optimized views that push complex donor-matching logic into the database layer.
Ready to quiz yourself?