Creating a multi-company ledger system involves building a database and backend that can handle multiple companies’ financial transactions (debits and credits). This article walks you through developing a ledger system using MySQL and Node.js, which can manage transactions and calculate account balances for multiple companies.
Table of Contents:
- Overview of the Multi-Company Ledger System
- Setting Up the Environment
- Database Schema Design
- Developing the Node.js API for Ledger Transactions
- Implementing Core Features: Inserting Transactions and Calculating Balances
- Testing the Ledger System
- Conclusion
1. Overview of the Multi-Company Ledger System
A ledger system tracks financial transactions such as debits and credits for one or more companies. For this multi-company implementation:
- Each company will have multiple accounts.
- Each account can have transactions that represent either a credit or a debit.
- The system will allow for calculating balances and querying transactions by company and account.
Core Features:
- Multiple companies: Each company is identified separately, and transactions are tied to specific companies.
- Transaction tracking: The system will track each transaction’s amount, date, type (debit/credit), and a description.
- Balance calculation: The balance for each company can be calculated by summing up all debits and credits.
SEO Keywords: ledger system, MySQL ledger, Node.js ledger, multi-company ledger system, track debits and credits, MySQL and Node.js ledger system
2. Setting Up the Environment
To build this system, you need the following tools:
- Node.js: For building the API and backend logic.
- Express.js: For routing and handling HTTP requests.
- MySQL: For storing and managing the ledger data.
- Sequelize ORM: To simplify database interaction.
- Postman: For testing API routes.
2.1 Installing Dependencies
Create a new Node.js project:
mkdir multi-company-ledger
cd multi-company-ledger
npm init -y
Install the necessary packages:
npm install express mysql2 sequelize
npm install –save-dev nodemon
Add the following to your package.json file to use nodemon for development:
json
“scripts”: {
“start”: “nodemon server.js”
}
SEO Keywords: Node.js environment setup, MySQL database setup, Sequelize ORM, Express.js routing, Node.js and MySQL integration
3. Database Schema Design
3.1 Company Table
Each company will have a unique identifier (primary key) and some basic information like name and email.
sql
CREATE TABLE companies (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
email VARCHAR(255),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
3.2 Account Table
Each company can have multiple accounts. The company_id will act as a foreign key linking the account to its company.
sql
CREATE TABLE accounts (
id INT AUTO_INCREMENT PRIMARY KEY,
company_id INT,
account_name VARCHAR(255) NOT NULL,
account_type VARCHAR(255),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (company_id) REFERENCES companies(id)
);
3.3 Transactions Table
Each transaction will record debits or credits for a particular account. The amount field will store positive values for credits and negative values for debits. The account_id links transactions to specific accounts.
sql
CREATE TABLE transactions (
id INT AUTO_INCREMENT PRIMARY KEY,
account_id INT,
amount DECIMAL(15, 2) NOT NULL,
description VARCHAR(255),
transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (account_id) REFERENCES accounts(id)
);
SEO Keywords: MySQL schema design, multi-company database schema, ledger database design, transactions table in MySQL, MySQL foreign keys, MySQL account management
4. Developing the Node.js API for Ledger Transactions
4.1 Set Up Sequelize
In server.js, initialize Express and Sequelize to connect to MySQL:
javascript
const express = require(‘express’);
const { Sequelize, DataTypes } = require(‘sequelize’);
// Initialize Express
const app = express();
app.use(express.json());
// Initialize Sequelize for MySQL
const sequelize = new Sequelize(‘ledger_db’, ‘root’, ‘password’, {
host: ‘localhost’,
dialect: ‘mysql’
});
// Test database connection
sequelize.authenticate()
.then(() => console.log(‘Database connected…’))
.catch(err => console.log(‘Error: ‘ + err));
4.2 Define Models
Create models for Company, Account, and Transaction:
javascript
const Company = sequelize.define(‘Company’, {
name: { type: DataTypes.STRING, allowNull: false },
email: { type: DataTypes.STRING, allowNull: true }
}, { timestamps: true });
const Account = sequelize.define(‘Account’, {
account_name: { type: DataTypes.STRING, allowNull: false },
account_type: { type: DataTypes.STRING, allowNull: true }
}, { timestamps: true });
const Transaction = sequelize.define(‘Transaction’, {
amount: { type: DataTypes.DECIMAL(15, 2), allowNull: false },
description: { type: DataTypes.STRING, allowNull: true },
transaction_date: { type: DataTypes.DATE, defaultValue: Sequelize.NOW }
}, { timestamps: true });
// Relationships
Company.hasMany(Account);
Account.belongsTo(Company);
Account.hasMany(Transaction);
Transaction.belongsTo(Account);
Sync the models with the database:
javascript
sequelize.sync()
.then(() => console.log(‘Tables created successfully’))
.catch(err => console.log(‘Error: ‘ + err));
SEO Keywords: Node.js Sequelize models, Node.js MySQL API, Sequelize MySQL integration, Node.js API development for MySQL, creating ledger models in Node.js
5. Implementing Core Features
5.1 Insert a Transaction
Add a route for inserting transactions. This will allow users to create debits and credits for a specific company and account.
javascript
app.post(‘/transactions’, async (req, res) => {
try {
const { account_id, amount, description } = req.body;
const transaction = await Transaction.create({ account_id, amount, description });
res.status(201).json(transaction);
} catch (err) {
res.status(500).json({ error: err.message });
}
});
5.2 Get Transactions for a Company
Create a route to fetch all transactions for a particular company:
javascript
app.get(‘/companies/:companyId/transactions’, async (req, res) => {
try {
const transactions = await Transaction.findAll({
include: {
model: Account,
where: { companyId: req.params.companyId }
}
});
res.status(200).json(transactions);
} catch (err) {
res.status(500).json({ error: err.message });
}
});
5.3 Calculate Account Balance for a Company
Create an endpoint that calculates the total balance for a company’s accounts:
javascript
app.get(‘/companies/:companyId/balance’, async (req, res) => {
try {
const accounts = await Account.findAll({
where: { companyId: req.params.companyId },
include: Transaction
});
let totalBalance = 0;
accounts.forEach(account => {
const accountBalance = account.Transactions.reduce((sum, transaction) => sum + parseFloat(transaction.amount), 0);
totalBalance += accountBalance;
});
res.status(200).json({ balance: totalBalance });
} catch (err) {
res.status(500).json({ error: err.message });
}
});
SEO Keywords: insert transaction Node.js, query transactions for company, calculate account balance Node.js, Node.js transaction tracking API
6. Testing the Ledger System
6.1 Insert Sample Data
Use Postman or curl to insert sample data into the system:
Create a Company:
json
POST /companies
{
“name”: “ABC Corp”,
“email”: “contact@abccorp.com”
}
Create Accounts for the Company:
json
POST /accounts
{
“companyId”: 1,
“account_name”: “Cash Account”,
“account_type”: “Asset”
}
Add Transactions (Credits and Debits):
json
POST /transactions
{
“account_id”: 1,
“amount”: 1000.00,
“description”: “Initial deposit”
}
6.2 Query Transactions and Balance
Get All Transactions for a Company:
json
GET /companies/1/transactions
Get Balance for a Company:
json
GET /companies/1/balance
SEO Keywords: test API with Postman, sample ledger transactions Node.js, query multi-company transactions, Node.js ledger API testing
7. Conclusion
In this article, we built a multi-company ledger system using MySQL and Node.js. We designed the database schema, created routes to handle inserting transactions and calculating account balances, and demonstrated how to retrieve financial data for different companies.
This system can be further enhanced by adding features like user authentication, more detailed account management, and reporting capabilities to make it even more robust for real-world applications.