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 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 APY defined as a Function<BigDecimal, Big Decimal> 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 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 wont be any solution if there are no clear boundaries for balance to interest and rather it is a continuous function.

Is there a more elegant approach?

Edit 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<BigDecimal, BigDecimal> APY;
private BigDecimal accruedInterest;
private Date lastTransactionDate;

private final BanksData banksData;
public Bank(short id, String name, BigDecimal balance, Function<BigDecimal, BigDecimal> APY) {
    super(id, name, balance);

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

    banksData = BanksData.instance;
}
public Bank(String name, Function<BigDecimal, BigDecimal> APY) {
    this((short) -1, name, new BigDecimal("0.0"), APY);
}

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

public Function<BigDecimal, BigDecimal> 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<Reserve> getAllReserves() {
    List<Reserve> 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<Reserve> getAllWallets() {
    List<Reserve> 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<Bank> getAllBanks() {
    List<Bank> 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 this banks are created on 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 Funtion<BigDecimal, BigDecimal> into mysql database.

Many people suggested stored procedures and that would have worked if it was only 1 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 everytime. 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. I think there might be a better way no?

Answer 1

Advice/opinion

A database is for persistent storage of numbers and text.

An app is for computing and deciding things.

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

Phrased differently: "Business logic" belongs in the app, 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 Procs 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 Procs have "cursors" and "loops" but they are clumsy; the app language is likely to have better code.