How to store function logic in the database

Question

I am making a finance management application. I have a database containing all the places the user has his/her money which includes banks. Here is how the table is structured...

CREATE TABLE IF NOT EXISTS reserves (
                            id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                            name VARCHAR(31) NOT NULL,
                            balance DECIMAL(10, 2) NOT NULL
                        )
CREATE TABLE IF NOT EXISTS banks (
                            reserve_id SMALLINT UNSIGNED UNIQUE NOT NULL,
                            apy DECIMAL(4, 2) NOT NULL,
                            accrued_interest DECIMAL(10, 4) NOT NULL,
                            last_transaction DATE,
                            FOREIGN KEY(reserve_id) REFERENCES reserves(id)
                        )

In this model I can have a fixed APY which will be set while insertion. But in the real world, banks have variable interest rates based on the balance. And the specifics are different for every bank in the banks table.

In the Java class I can capture this very easily with the APY defined as a Function APY where I can store the specific APY logic and use APY.apply(balance) to retrieve the interest rate at any point.

But I don't know how to store this logic in a MySQL database.

I know I can create a separate table like bank_balance_interest where I can store the interest rate to the minimum balance to the id of the specific bank and then refer to that.

But it just does not feel right. For one, it is very cumbersome and tedious. Besides there still won’tbe any solution if there aren’t any clear boundaries for balance to interest and rather it is a continuous function.

Is there a more elegant approach?

Here is some of my code:

public class Reserve {
    short id;
    final String name;
    BigDecimal balance;

    ReservesData reservesData;
    public Reserve(short id, String name, BigDecimal balance) {
        this.id = id;
        this.name = name;
        this.balance = balance;

        reservesData = ReservesData.instance;
    }

    public Reserve(String name) {
        this((short) -1, name, new BigDecimal("0.0"));
    }

    @Override
    public String toString() {
        return name;
    }

    public short getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public BigDecimal getBalance() {
        return balance;
    }

    public boolean transact(BigDecimal amount) {
        if(balance.add(amount).compareTo(new BigDecimal("0.0")) < 0)
            return false;
        balance = balance.add(amount);
        return true;
    }

    public boolean save() {
        if(id == -1)
            return (id = reservesData.addReserve(this)) != -1;
        return reservesData.updateReserve(this);
    }
}

public class Bank extends Reserve{

    private final Function APY;
    private BigDecimal accruedInterest;
    private Date lastTransactionDate;

    private final BanksData banksData;

    public Bank(short id, String name, BigDecimal balance, Function APY) {
        super(id, name, balance);

        this.APY = APY;
        accruedInterest = new BigDecimal("0.0");

        banksData = BanksData.instance;
    }

    public Bank(String name, Function APY) {
        this((short) -1, name, new BigDecimal("0.0"), APY);
    }

    @Override
    public BigDecimal getBalance() {
        return balance.add(accruedInterest);
    }

    public Function getAPY() {
        return APY;
    }

    public BigDecimal getAccruedInterest() {
        return accruedInterest;
    }

    public void setAccruedInterest(BigDecimal accruedInterest) {
        this.accruedInterest = accruedInterest;
    }

public class ReservesDAO implements ReservesData {

    public ReservesDAO() {
        try(Statement stmt = MyConnection.getMySQLconnection().createStatement()) {
            stmt.executeUpdate("""
                            CREATE TABLE IF NOT EXISTS reserves (
                                id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                                name VARCHAR(31) NOT NULL,
                                balance DECIMAL(10, 2) NOT NULL
                            )"""
            );
        } catch (SQLException sqlException) {
            System.out.println("Failed to create reserves table on the database!");
            sqlException.printStackTrace();
        }
    }

    @Override
    public short addReserve(Reserve reserve) {
        try (
                PreparedStatement pstmt = MyConnection.getMySQLconnection().prepareStatement("""
                        INSERT INTO reserves (name, balance) VALUES (?, ?)""", Statement.RETURN_GENERATED_KEYS
                )
        ) {
            pstmt.setString(1, reserve.getName());
            pstmt.setBigDecimal(2, reserve.getBalance());

            pstmt.executeUpdate();
            ResultSet rs = pstmt.getGeneratedKeys();
            if (rs.next())
                return rs.getShort(1);
            else
                throw new RuntimeException("Auto-Generated ID was not returned from reserves!");
        } catch (SQLException sqlException) {
            System.out.println("Failed to insert " + reserve.getName() + " info in the database!");
            sqlException.printStackTrace();
            return -1;
        }
    }

    public Reserve getReserve(short id) {
        try(
                PreparedStatement pstmt = MyConnection.getMySQLconnection().prepareStatement("""
                        SELECT * FROM reserves WHERE id = ?""")
        ) {
            pstmt.setShort(1, id);
            ResultSet rs = pstmt.executeQuery();

            if(rs.next())
                return new Reserve(rs.getShort(1), rs.getString(2), rs.getBigDecimal(3));
            else throw new RuntimeException("No reserve found on the database with the id " + id);

        } catch (SQLException sqlException) {
            System.out.println("Failed to fetch reserve from the database!");
            sqlException.printStackTrace();
            return null;
        }
    }

    public List getAllReserves() {
        List reserves = new ArrayList<>();
        try(Statement stmt = MyConnection.getMySQLconnection().createStatement()) {
            ResultSet rs = stmt.executeQuery("SELECT * FROM reserves");
            while(rs.next())
                reserves.add(new Reserve(rs.getShort(1), rs.getString(2), rs.getBigDecimal(3)));
        } catch (SQLException sqlException) {
            System.out.println("Failed to fetch reserves from the database!");
            sqlException.printStackTrace();
        }

        return reserves;
    }

    @Override
    public BigDecimal getTotalReserveBalance() {
        try(Statement stmt = MyConnection.getMySQLconnection().createStatement()) {
            ResultSet rs = stmt.executeQuery("""
                    SELECT SUM(balance) FROM reserves""");
            if(rs.next())
                return rs.getBigDecimal(1);
            return new BigDecimal("0.0");
        } catch (SQLException sqlException) {
            System.out.println("Could not get total reserve balance from database!");
            sqlException.printStackTrace();
            return null;
        }
    }

    @Override
    public List getAllWallets() {
        List reserves = new ArrayList<>();
        try(Statement stmt = MyConnection.getMySQLconnection().createStatement()) {
            ResultSet rs = stmt.executeQuery("""
                    SELECT reserves.* FROM reserves
                    LEFT JOIN banks ON reserves.id = banks.id
                    WHERE banks.id IS NULL
                    """);
            while(rs.next())
                reserves.add(new Reserve(rs.getShort(1), rs.getString(2), rs.getBigDecimal(3)));
        } catch (SQLException sqlException) {
            System.out.println("Failed to fetch reserves from the database!");
            sqlException.printStackTrace();
        }

        return reserves;
    }

    @Override
    public BigDecimal getTotalWalletBalance() {
        try(Statement stmt = MyConnection.getMySQLconnection().createStatement()) {
            ResultSet rs = stmt.executeQuery("""
                    SELECT SUM(balance) FROM reserves
                    LEFT JOIN banks ON reserves.id = banks.id
                    WHERE banks.id IS NULL
                    """);
            if(rs.next())
                return rs.getBigDecimal(1) == null ? new BigDecimal("0.0") : rs.getBigDecimal(1);
            return new BigDecimal("0.0");
        } catch (SQLException sqlException) {
            System.out.println("Could not get total wallet balance from database!");
            sqlException.printStackTrace();
            return null;
        }
    }

    @Override
    public boolean updateReserve(Reserve reserve) {
        try(PreparedStatement pstmt = MyConnection.getMySQLconnection().prepareStatement("""
                UPDATE reserves SET name = ?, balance = ? WHERE id = ?""")
        ) {
            pstmt.setString(1, reserve.getName());
            pstmt.setBigDecimal(2, reserve.getBalance());
            pstmt.setShort(3, reserve.getId());
            pstmt.executeUpdate();
            return true;
        } catch(SQLException sqlException) {
            System.out.println("Failed to update reserves with new data!");
            sqlException.printStackTrace();
            return false;
        }
    }
}

public class BanksDAO extends ReservesDAO implements BanksData {
    public BanksDAO() {
        try(
            Statement stmt = MyConnection.getMySQLconnection().createStatement()
        ) {
            stmt.executeUpdate("""
                            CREATE TABLE IF NOT EXISTS banks (
                                id SMALLINT UNSIGNED UNIQUE NOT NULL,
                                apy DECIMAL(4, 2) NOT NULL, // I have no way to store a logic here, so currently it only stores fixed value.
                                accrued_interest DECIMAL(10, 4) NOT NULL,
                                last_transaction_date DATE,
                                FOREIGN KEY(id) REFERENCES reserves(id)
                            )"""
            );
        } catch (SQLException sqlException) {
            System.out.println("Failed to create banks table on the database!");
            sqlException.printStackTrace();
        }
    }

    @Override
    public short addBank(Bank bank) {
        try (
                PreparedStatement pstmt = MyConnection.getMySQLconnection().prepareStatement("""
                        INSERT INTO banks(id, apy, accrued_interest, last_transaction_date) VALUES (?, ?, ?, ?)"""
                )
        ) {
            short id = addReserve(bank);
            pstmt.setShort(1, id);
            pstmt.setBigDecimal(2, bank.getAPY());
            pstmt.setBigDecimal(3, bank.getAccruedInterest());
            pstmt.setDate(4, bank.getLastTransactionDate());

            pstmt.executeUpdate();
            return id;
        } catch (SQLException sqlException) {
            System.out.println("Failed to insert " + bank.getName() + " info in the database!");
            sqlException.printStackTrace();
            return -1;
        }
    }

    @Override
    public Bank getBank(short reserve_id) {
        try(
            PreparedStatement pstmt = MyConnection.getMySQLconnection().prepareStatement("""
                        SELECT * FROM reserves NATURAL JOIN banks WHERE id = ?""")
        ) {
            pstmt.setShort(1, reserve_id);
            ResultSet rs = pstmt.executeQuery();
            if(!rs.next())
                return null;
            Bank requestedBank = new Bank(rs.getShort(1), rs.getString(2),
                    rs.getBigDecimal(3), rs.getBigDecimal(4));
            requestedBank.setAccruedInterest(rs.getBigDecimal(5));
            requestedBank.setLastTransactionDate(rs.getDate(6));
            return requestedBank;
        } catch (SQLException sqlException) {
            System.out.println("Failed to fetch bank data from the database!");
            sqlException.printStackTrace();
            return null;
        }
    }

    @Override
    public List getAllBanks() {
        List allBanks = new ArrayList<>();
        try(
            Statement stmt = MyConnection.getMySQLconnection().createStatement()
        ) {
            ResultSet rs = stmt.executeQuery("SELECT * FROM reserves NATURAL JOIN banks");
            while(rs.next()) {
                Bank bank = new Bank(rs.getShort(1), rs.getString(2),
                        rs.getBigDecimal(3), rs.getBigDecimal(4));
                bank.setAccruedInterest(rs.getBigDecimal(5));
                bank.setLastTransactionDate(rs.getDate(6));
                allBanks.add(bank);
            }

            return allBanks;

        } catch (SQLException sqlException) {
            System.out.println("Failed to fetch bank data from the database!");
            sqlException.printStackTrace();
            return null;
        }
    }

    @Override
    public BigDecimal getTotalBankBalance() {
        try(Statement stmt = MyConnection.getMySQLconnection().createStatement()) {
            ResultSet rs = stmt.executeQuery("""
                    SELECT SUM(balance) FROM reserves NATURAL JOIN banks""");
            if(rs.next())
                return rs.getBigDecimal(1) == null ? new BigDecimal("0.0") : rs.getBigDecimal(1);
            return new BigDecimal("0.0");
        } catch (SQLException sqlException) {
            System.out.println("Could not get total bank balance from database!");
            sqlException.printStackTrace();
            return null;
        }
    }
}

Now I can initialize a bank as:

Bank bank1 = new Bank("TestBank1", balance -> balance.compareTo(new BigDecimal("10000")) == -1 ? new BigDecimal("4") : new BigDecimal("5"));

While I can create another bank as:

Bank bank2 = new Bank("TestBank2", balance -> balance.compareTo(new BigDecimal("8000")) == -1 ? new BigDecimal("3.5") : new BigDecimal("5.3"));

Now both these banks are created in memory and will work perfectly as long as the application is running. But when I need to persist this for long term use I cannot directly store a variable of type Function into the MySQL database.

Many people suggested stored procedures and that would have worked if it was only one logic, like balance -> balance.compareTo(new BigDecimal("10000")) == -1 ? new BigDecimal("4") : new BigDecimal("5") for every bank in the banks table, but this information will change every time.

Which means if I have 50 entries in my banks table I need to create 50 different stored procedures with 50 kinds of logics for every entry in my banks table, to keep updating the APY field as the balance changes. Might there be a better way?

user_3122921 · Answer

I think you're being misunderstood. You're probably not asking how to move your logic into the database, to which a stored procedure would be an answer, but how to store the logic you're implementing in code to the database so that you can restore that state later on. So that's the premise of my answer.

Your design related to these calculations is not great. You should definitely consider expressing these APY calculation methods as classes implementing some kind of IAPYCalculationMethod interface, with a method to actually calculate it. Anonymous lambdas like the ones you have are just bad for this purpose.

So let's say you actually do have an IAPYCalculatioMethod interface with CalculateAPY method, and then a BalanceBasedCalculationMethod that looks like this:

// The code is not complete. It is just to give you an idea
class BalanceBasedCalculationMethod implements IAPYCalculationMethod {
  public BalanceBasedCalculationMethod(BigDecimal balanceThreshold, BigDecimal whenLower, BigDecimal whenGreater) { ... }

  public BigDecimal CalculateAPY(Bank bank) {
     if (bank.getBalance() < this.balanceThreshold)
         return this.whenLower;
     else
         return this.whenGreater;
  }
}

Then when you create a new Bank:

bank1 = new Bank("TestBank1", new BalanceBasedCalculationMethod(10000, 4, 5));
bank2 = new Bank("TestBank2", new BalanceBasedCalculationMethod(8000, 3.5, 5.3));

This is already better. And this also allows you to somehow serialize all of this. You can have a table with all calculation methods, relation between the bank and the method used, together with a JSON of arguments (because different methods may have different arguments; you might also want to store them in a separate table instead of JSON).

CREATE TABLE IF NOT EXISTS apy_calculation_method
(
   id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
   name VARCHAR(100) NOT NULL
);


CREATE TABLE IF NOT EXISTS bank_calculation_method
(
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  bank_id SMALLINT UNSIGNED NOT NULL REFERENCES reserves(id),
  method_id SMALLINT UNSIGNED NOT NULL REFERENCES apy_calculation_method(id),
  arguments JSON
);

To represent our balance based method and the banks using it we'd then have:

INSERT INTO apy_calculation_method (name) VALUES ('Balance Based');

Then the method for each of the banks:

INSERT INTO bank_calculation_method (bank_id, method_id, arguments)
VALUES (1, 1, '{"balance": 10000, "whenLower": 4, "whenGreater": 5}')
     , (2, 1, '{"balance":  8000, "whenLower": 3.5, "whenGreater": 5.3}');

And the banks:

INSERT INTO banks (id, bank_method_id, accrued_interest)
VALUES (1, 1, 0) -- first bank using the first method (balance based with 10000 balance)
     , (2, 2, 0) -- second bank using the second method (balance based with 8000 balance)

This method can be less or more rigid (normalized / denormalized), you could for example just store the calculation method as JSON in the banks table (with {"method": "balance", "balance": 10000, .... } structure of sort). The design of your calculation method factory and how it serialized/deserializes data to/from the database depends on it, but I'm sure you can figure it out.

What this all gives you is the option for the calculation method to be serialized into the database, instead of some kind of random lambda function that is impossible to reference in any way. So instead of writing the actual logic to the database, you just write the "type of logic used" and the parameters.

As an added benefit, this creates a testable design, you can write a test for BalanceBasedCalculationMethod to ensure it actually does what it's supposed to do. You have no way of automatically testing these lambdas whatsoever because there is no telling what they may or may not do, right?

user_1766831 · Answer

Advice/opinion

A database is for persistent storage of numbers and text.

An application is for computing and deciding things.

That is, the complex business logic you describe may be more appropriately encoded in Java (or other application language). Meanwhile, the breakpoints, etc. for interest rates should be stored in the database.

Phrased differently: "Business logic" belongs in the application, not the database. (There is, of course, a gray lines between them. For example, SQL is excellent at summing up all the data in a table; so, I would do that in SQL, not Java.)

Decimal

Banks have picky rules that may not be the same as what DECIMAL or DOUBLE provide -- in Java or MySQL or _any other computer language. Be careful of the rules that may be required. In particular, DECIMAL(10, 4) is unlikely to be adequate in your application. On the other hand, apy DECIMAL(4, 2) may be adequate if that is what is presented to the customer. But, again, beware of rounding rules when generating that number. You may be forced to enter that number manually.

Be aware of the difference in rounding characteristics between DECIMAL and DOUBLE.

Answer

If you choose to implement an algorithm in the database, then see CREATE STORED PROCEDURE and CREATE FUNCTION.

Stored procedures can be used to encapsulate a set of SQL statements. It can receive and send strings and numbers, but not arrays. It is capable of reading tables (hence "arrays" of a sort).

Functions can be called anywhere an expression can occur. It can receive some numbers/strings and produce one number or string.

Array

I'm not clear on what is needed, but I envision a table of a few rows or a few dozen rows with each row saying "for values up to $xxx, use y.yy% interest rate".

Stored procedures have "cursors" and "loops", but they are clumsy; the application language is likely to have better code.

How to store function logic in the database

2 Answers