Relational database design for a cross-border remittance system, covering the full transaction lifecycle, from creation and fee calculation to cancellation and refund; using views, functions, stored procedures, and triggers to enforce data integrity and automate business logic.
Money transfer companies handle sensitive, mission-critical information: customers, beneficiaries, agents, exchange rates, fees, cancellations, and refunds. When this information lives in spreadsheets or poorly normalized structures, it becomes hard to answer simple questions like what state is this transfer in? or how much did we generate in fees last month?
The challenge was to design a relational schema that guaranteed full traceability of a remittance's lifecycle, from creation to payment, cancellation, or refund; while also making financial reporting easy without relying on repeated, complex queries.
I started by modeling the business's 8 entities:
CLIENTE / customer
BENEFICIARIO / beneficiary
AGENTE / agent
TASA_CAMBIO / exchange rate
TRANSACCION / transaction
TRANSACCION_COMISION / transaction fee
CANCELACION / cancellation
REEMBOLSO / refund
and their relationships, shown in the entity-relationship diagram below.
Separating cancellation and refund into two distinct tables. Not every cancellation implies an immediate refund, so modeling them as independent entities connected 1:1 makes it possible to audit each step separately: when and why something was canceled, and when and how it was refunded.
Recording the exchange rate applied to each transaction, not just the final amount. The exchange rate table keeps the currency pair and the date of the rate used, so it's possible to reconstruct months later exactly which rate was applied to a given transfer — essential for financial audits.
Calculating fees automatically via a trigger, not in the application layer. The business rule (a different percentage depending on the amount sent) lives inside the database through a trigger that fires when each transaction is created, preventing a fee from ever going uncalculated due to an application-layer bug.
Encapsulating repetitive logic into reusable functions, such as a fee-calculation function, to avoid duplicating the calculation across queries, views, or stored procedures.
The final model consolidates the entire operational and financial flow of a remittance into a single schema, removing the need for repetitive JOINs on the most common queries (thanks to the 5 views) and automating two processes that would otherwise be manual and error-prone: fee calculation and status synchronization between transaction and cancellation.
Key takeaway: automating business rules directly in the database (via triggers) reduces human error, but also couples that logic to the database engine — a trade-off worth weighing based on how often the rule is likely to change.
Next steps: add indexes on the most-queried columns (customer ID, origin agent ID, status) to sustain performance at higher volume, and evaluate date-based partitioning on the transactions table for a real production scenario.
Full SQL implementation of the schema's business logic: 5 views, 2 functions, 2 stored procedures, and 2 triggers. Each section includes a short description of what the object does and why — expand "Show code" to see the full definition.