How I Store Merchant Suggestions Without Polluting Relational Database Keys

When designing a database for an expense tracker, normalization is usually the first instinct of any software engineer.

You create a transactions table. A transaction has a merchant. So you immediately create a normalized merchants table with an auto-incrementing primary key (id, name, category_id), and add a foreign key column to your transaction table: merchant_id REFERENCES merchants(id).

It feels like textbook database design.

Then real users start typing real-world merchant names into your application, and that relational foreign key constraint quickly turns into an engineering nightmare.

Here is why FinLedger deliberately decoupled merchant auto-suggestions from relational foreign keys, and how we built an on-device merchant intelligence engine without database pollution.

The Normalization Trap: Why Foreign Keys Fail for Merchants

In the real world, merchant names are noisy, erratic, and constantly shifting:

• A user buys coffee at "Blue Tokai Coffee Roasters".
• Next week, their credit card SMS alerts them about a transaction at "BLUE TOKAI COFFEE".
• Two weeks later, they manually enter an expense as "Blue Tokai".
• Three weeks later, they visit a branch at the airport and type "Blue Tokai - Terminal 2".

flowchart TD
    subgraph StrictRelational["The Strict Relational Anti-Pattern"]
        direction TB
        M1["merchants Table:
id=1: 'Blue Tokai'
id=2: 'Blue Tokai Coffee'
id=3: 'BLUE TOKAI COFFEE ROASTERS'"]
        Tx["transactions Table:
Foreign Key: merchant_id -> Rigid Dependency!"]
        M1 --- Tx
        Problem["What happens when the user cleans up duplicate merchants?
Deleting or merging merchant rows requires cascading updates across 
thousands of historical transaction records!"]
    end

If your transaction table enforces a strict foreign key constraint (merchant_id):

1. The Proliferation of Duplicates: Every minor spelling variation, capitalization difference, or branch suffix forces the creation of a brand new row in the merchants table.
2. Cascading Migration Nightmares: If a user wants to merge or rename a merchant, SQLite must rewrite foreign key pointers across hundreds of historical transactions. If an account is deleted or transactions are purged, foreign key constraints risk breaking.
3. SMS Ingestion Friction: Automated SMS parsers often extract truncated merchant strings (e.g. "VSI*UBER INDIA"). Forcing an automated parser to match or create a permanent relational merchant entity before saving a transaction creates unnecessary pipeline complexity.

The FinLedger Architecture: Flexible Text + Independent Learning

To solve this, FinLedger enforces a clean separation of concerns:

Transactions store merchants as pure, decoupled text attributes (purposeMerchant: String).

Meanwhile, merchant auto-suggestions, category predictions, and usage frequency are managed by an entirely independent subsystem: the Merchant Rule & Intelligence Engine (MerchantRuleEntity, MerchantRuleDao, and LocalCategoryIntelligenceEngine).

flowchart LR
    subgraph DecoupledArchitecture["FinLedger Decoupled Merchant Architecture"]
        direction TB
        TxEntity["Transaction Entity:
amount: 450.00
purposeMerchant: 'Blue Tokai' (Plain String)
categoryId: 3 (Food & Dining)"]

        RuleEntity["MerchantRule Entity (Independent):
normalizedPattern: 'blue tokai'
preferredCategoryId: 3
usageCount: 14
lastUsedTimestamp: 1729864200000"]

        TxEntity -.->|"Learns From (Asynchronous)"| RuleEntity
        RuleEntity -.->|"Provides Auto-Suggestions To"| UI["Add Transaction UI Screen"]
    end

1. The Transaction Entity Remains Resilient

Because purposeMerchant is a standard string column, transactions have zero foreign key dependencies on merchant entities:

• A transaction can be edited, copied, imported from a bank statement, or deleted without worrying about orphan relational keys.
• Truncated SMS names or detailed manual notes exist safely side-by-side.

2. The Auto-Learning Rule Engine

Whenever a transaction is confirmed in TransactionRepository:

• The engine extracts the merchant string and normalizes it (lowercase, trimmed whitespace, special characters removed).
• It queries the merchant_rules table. If the normalized pattern exists, it increments usageCount, updates lastUsedTimestamp, and refines the associated preferredCategoryId.
• If the pattern is new, it inserts a new rule learning that this merchant belongs to this category.

Intelligent Auto-Suggestions with Blank Query Support

When a user taps into the merchant field in ModernAddTransactionScreen:

• As they type "bl", the repository queries MerchantRuleDao.searchMerchantRules("bl%").
• Results are ranked dynamically by recency and frequency (frequently visited merchants appear at the top of the dropdown).
• Tapping a suggestion auto-fills the merchant name and automatically selects the associated spending category with a single tap.

Fixing the Blank Query Edge Case (Commit 8922c14)

A subtle UX defect in early versions occurred when a user opened the merchant field without typing:

• If the search query was blank, the dropdown showed nothing.
• In commit 8922c14, we updated the query pipeline: when the search query is blank, the engine automatically surfaces the user's top 5 most frequently used recent merchants. If a user clears their search query, the suggestion sheet gracefully re-opens rather than closing.

Key Takeaway

Database normalization is a tool, not a religious dogma.

Keeping merchant names as decoupled text attributes in your transaction tables while managing auto-complete intelligence in an independent learning engine gives you the best of both worlds: frictionless data entry for users and zero relational fragility in your database.

---

FinLedger is a privacy-first, offline-first personal finance application for Android that models bank accounts, credit cards, and cash without double-counting.

Explore FinLedger on Google Play: https://play.google.com/store/apps/details?id=com.devanshufinance.app