Transaction Management in Systems using .NET Core and MS SQL

Introduction

In financial or e-commerce systems, managing financial transactions is a crucial part. Each transaction can consist of multiple components such as purchasing items, platform fees, or payment gateway fees. To ensure data integrity and transparency, the system needs a mechanism to record transactions and financial journals clearly and accurately.

This article will guide you on how to design a database and implement an API using .NET Core to manage transactions with the following features:

• Generate transaction numbers (TrxNumber) for each transaction.
• Record multiple sub-transactions with the same transaction number but different types (buy item, platform fee, payment gateway fee).
• Record financial journals to debit and credit user accounts.

1. Database Design

1.1 Transactions Table

This table stores detailed information about each transaction, including the transaction number, user, amount, and transaction type.

CREATE TABLE Transactions (
    Id INT PRIMARY KEY IDENTITY,
    TrxNumber NVARCHAR(50),
    UserId INT,
    Amount DECIMAL(18, 2),
    Type NVARCHAR(50), -- Buy Item, Platform Fee, Payment Gateway Fee
    CreatedAt DATETIME DEFAULT GETDATE(),
    CONSTRAINT UC_TrxNumber UNIQUE (TrxNumber, Type)
);

1.2 Journals Table

This table records financial journals, allowing tracking of debit and credit for each transaction.

CREATE TABLE Journals (
    Id INT PRIMARY KEY IDENTITY,
    TrxId INT,
    UserId INT,
    Debit DECIMAL(18, 2),
    Credit DECIMAL(18, 2),
    Description NVARCHAR(255),
    CreatedAt DATETIME DEFAULT GETDATE(),
    FOREIGN KEY (TrxId) REFERENCES Transactions(Id)
);

2. Code Implementation with .NET Core API

2.1 Entity Models

These models represent the Transactions and Journals tables in the database.

public class Transaction
{
    public int Id { get; set; }
    public string TrxNumber { get; set; }
    public int UserId { get; set; }
    public decimal Amount { get; set; }
    public string Type { get; set; }
    public DateTime CreatedAt { get; set; }
}

public class Journal
{
    public int Id { get; set; }
    public int TrxId { get; set; }
    public int UserId { get; set; }
    public decimal Debit { get; set; }
    public decimal Credit { get; set; }
    public string Description { get; set; }
    public DateTime CreatedAt { get; set; }
}

2.2 DbContext

DbContext connects and interacts with the database using Entity Framework Core.

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }

    public DbSet<Transaction> Transactions { get; set; }
    public DbSet<Journal> Journals { get; set; }
}

2.3 Service

This service handles the logic for creating transactions and financial journals.

public class TransactionService
{
    private readonly AppDbContext _context;

    public TransactionService(AppDbContext context)
    {
        _context = context;
    }

    public async Task CreateTransaction(int userId, List<(decimal Amount, string Type)> details)
    {
        var trxNumber = Guid.NewGuid().ToString();

        foreach (var detail in details)
        {
            var transaction = new Transaction
            {
                TrxNumber = trxNumber,
                UserId = userId,
                Amount = detail.Amount,
                Type = detail.Type,
                CreatedAt = DateTime.Now
            };

            _context.Transactions.Add(transaction);
            await _context.SaveChangesAsync();

            _context.Journals.Add(new Journal
            {
                TrxId = transaction.Id,
                UserId = userId,
                Debit = detail.Type == "Buy Item" ? detail.Amount : 0,
                Credit = detail.Type != "Buy Item" ? detail.Amount : 0,
                Description = $"Transaction {trxNumber} - {detail.Type}",
                CreatedAt = DateTime.Now
            });
            await _context.SaveChangesAsync();
        }
    }
}

2.4 Controller

The controller provides an API to create transactions.

[ApiController]
[Route("api/[controller]")]
public class TransactionsController : ControllerBase
{
    private readonly TransactionService _service;

    public TransactionsController(TransactionService service)
    {
        _service = service;
    }

    [HttpPost]
    public async Task<IActionResult> CreateTransaction([FromBody] TransactionRequest request)
    {
        await _service.CreateTransaction(request.UserId, request.Details);
        return Ok("Transaction Created");
    }
}

public class TransactionRequest
{
    public int UserId { get; set; }
    public List<(decimal Amount, string Type)> Details { get; set; }
}

Conclusion

Managing transactions and financial journals is an essential part of modern financial systems. With the database design and API implementation outlined above, the system can easily track and process complex transactions, ensuring transparency and security for users.

I hope this article provides you with a clearer understanding of how to design and implement a transaction management system in real-world applications.