Handling currency in a Marketplace

Introduction

One of the projects I have been working on is a marketplace for Retro gaming Retrolink. The goal of this marketplace is to provide a platform for sellers to sell their retro gaming items and for buyers to buy them all over the world. One of the first problems we encountered was how to handle currency. My first instinct was, this should not too hard.

The Currency Conundrum

When designing an e-commerce platform with a global reach, currency handling isn’t just a technical detail—it’s a critical feature that can make or break user experience. Customers want to see prices in their familiar currency, understand exactly what they’re paying, and feel confident about the transaction.

Requirements

First, let’s understand the key requirements for handling multiple currencies in our marketplace:

  1. We need to store the original (base) price and currency for each listing
  2. We need to be able to display prices in the user’s preferred currency
  3. We need to handle currency conversion rates and their updates
  4. We need to maintain a history of prices and conversions for accounting and audit purposes

Looking at this list it is clear that the solution maybe not that easy.

Context

Our marketplace is a global platform where sellers can list retro gaming items for sale. Buyers can browse listings and make purchases. Shops can list items and create their own stores. For context our database has the following tables:

This is a very simplified version of the database. The shops table represents the shops that can list items. The listings table represents the items that are listed.

The Solution

Currencies Table: The Foundation

We start with a comprehensive currencies table that goes beyond simple currency codes. This table stores:

  • Currency code (like USD or EUR)
  • Full currency name
  • Currency symbol
  • Number of decimal places
  • Active status

This allows for incredible flexibility. Want to add a new currency? Simply insert a new row. Need to temporarily disable a currency? Just toggle its active status.

Exchange Rates: The Dynamic Heart

The exchange_rates table is where the magic of currency conversion happens. Each entry includes:

  • Source and destination currencies
  • Conversion rate
  • Validity period

By storing rates with start and end times, we create a historical record of exchange rates. This means we can always reference the exact rate that was in effect at any given moment—crucial for financial accuracy and auditing.

Listing

Each listing in the listings table includes:

  • Price
  • Base Currency

When a listing is created, the price is stored in the base currency. When a user views the listing, we convert the price to their preferred currency using the latest exchange rate.

Bringing it together

Combining all things above we get the following ERD:

This ERD shows the relationships between the currencies, exchange_rates, and listings tables. The exchange_rates table is the bridge between the currencies and listings tables, allowing us to convert prices between currencies.

In a next post I will go into more detail on how we implemented this in our marketplace.