Wednesday, April 20, 2005

Business Logic APIs

Database application development generally has two major components:

1) The user interface
2) The database

These have to talk to each other somehow, typically by an "Application Programmatic Interface" or API. What should such an API look like logically?

Let's take as an example a screen that allows the user to transfer funds from one account to another. It looks something like this:

Source Account: ..........
Target Account: ..........
Amount: ..........

[OK] [Cancel]

The database has the following tables:

CREATE TABLE accounts
( account_id INTEGER PRIMARY KEY
, account_name VARCHAR2(30)
, current_balance NUMBER CHECK (current_balance >= 0)
);

CREATE TABLE transactions
( transaction_id INTEGER PRIMARY KEY
, transaction_date DATE NOT NULL
, source_account_id INTEGER NOT NULL REFERENCES accounts
, target_account_id INTEGER NOT NULL REFERENCES accounts
, amount NUMBER CHECK (amount >= 0)
);

What needs to be done:

Once user has entered the 2 account numbers and the amount then we must:

  1. Update the source account and decrease its balance by the amount (subject to the check constraint that says the balance cannot become negative)

  2. Update the target account and increase its balance by the amount

  3. Create a transactions record for the transfer

How should we build the code?

The wrong approach

This is the approach that I so often see used:

1) Create a "table API" for table ACCOUNTS with a procedure:
PROCEDURE update_account_balance
( p_account_id IN INTEGER
, p_amount IN NUMBER
);

2) Create a “table API” for table TRANSACTIONS with a procedure:
PROCEDURE create_transaction
( p_source_account_id IN INTEGER
, p_target_account_id IN INTEGER
, p_amount IN NUMBER
);

3) In the application, invoke the following code when OK is pressed:
  • accounts_api.update_account_balance (:source_account_id, :amount * -1);
  • accounts_api.update_account_balance (:target_account_id, :amount);
  • transactions_api.create_transaction (:source_account_id, :target_account_id, :amount);
It works, but it is poor design. The client application is made responsible for carrying out the transaction properly: for example, if there are insufficient funds in the source account then the transfer must be aborted.

What is wrong with that?

  • Suppose there are other variants of the transfer process, implemented in different client screens. The business logic must be duplicated between the various screens. This leads to increased maintenance, increased testing, increased chance of something going wrong.

  • Suppose this application is a Windows client/server app, but now we want to add an alternative web-based interface. Yet again, we will be duplicating the business logic, with the attendant costs and risks.

The right approach

What is required is a Business Logic API, in which each complete transaction is represented by a single procedure like this:
PROCEDURE transfer_funds
( p_source_account_id IN INTEGER
, p_target_account_id IN INTEGER
, p_amount IN NUMBER
);

The body of that procedure performs all 3 steps of the process, and handles any errors. It either succeeds in all 3 steps, or leaves the database unchanged.

In the application, the code behind the OK button is:

accounts_api.Transfer_funds (:source_account_id, :target_account_id, :amount);

It is not possible for the application to corrupt the database, because it doesn’t contain any business logic – it is just an interface to the business logic that is in the API.

This has many advantages:
  • The business logic resides in exactly one place, the API – so only needs to be maintained once, tested once

  • The application is simpler – less maintenance, less testing

  • The application is lower risk

  • Adding an alternative interface is easier and low-risk, because we will only be creating a user interface, not new (duplicated) business logic

Conclusion

Have I built a straw man here? Isn’t the above obvious? You would think so, but as I already said I actually see code developed the wrong way all the time. I’m not making it up! In my experience it is usually because the developer (who may be fairly inexperienced in software design principles) is left to his or her own devices to build the application and the API at the same time, without any direction from a senior designer. Since he is building all the code, and only building one client module, what does he care whether the logic is in the client application or in the API?

It is only later, when changes are made to the rules, or a new interface is wanted, that the whole sorry mess becomes apparent.

8 comments:

Anonymous said...
This comment has been removed by a blog administrator.
Tony Andrews said...
This comment has been removed by a blog administrator.
Anonymous said...

I don't think you really understand what an API is, let alone how business logic is properly implemented.

Tony Andrews said...

Anonymous said...

> I don't think you really understand what an API is, let alone how business logic is properly implemented.

Who, me or the spammer whose spam I deleted?

Anonymous said...

Or alternatively we could add the following trigger

create or replace trigger transactions_insert
after insert on transactions
for each row
begin
update accounts
set current_balance = current_balance - :new.amount
where account_id = :new.source_account_id;
update accounts
set current_balance = current_balance + :new.amount
where account_id = :new.target_account_id;
end;

and dispense with the need for the API altogether.

This seems to me to have numerous advantages.

If we added a new column to the transactions table with a default then we would only need to change those inserts in the code that needed to insert something other than the default value.

If we have an API we need to add the new column to the arguments of the API and then modify, recompile and relink every piece of code that calls the API.

Tony Andrews said...

Will> If we have an API we need to add the new column to the arguments of the API and then modify, recompile and relink every piece of code that calls the API.

Not modify: API parameters can have default values too! But the recompilation would be required I agree.

I agree to some extent that triggers could be used instead of APIs, but not entirely. For one thing, triggers can become unwieldy and confusing, e.g. when avoiding the "mutating table" issue. Also, some transactions do not have an obvious "triggering statement" off which the logic can hang, and so we would end up with APIs for some transactions and not for others.

I also have a feeling that when one calls an API it seems not unreasonable for it to perform various tasks, whereas "INSERT INTO transaction VALUES (...)" feels much like it should ONLY insert a row into that table.

Anonymous said...

Ok, so the API does the work, but
you're not done yet, because the
client GUI has to reflect the
changes. How do you do that? The
client may not always know what data was changed, does it have to
requery the whole data that is displayed?

For this example, that's no problem. But what about more complex business logic that affects a lot of data. I guess you can minimze this by only view little data on the client, so you only have to refresh little. Is that the right approach?

Tony Andrews said...

Yes, the client will need to be refreshed if it may be showing data that has been changed by the API. Whether you should therefore minimise the amount displayed depends on whether the users really need to see it. If they do then refresh it, if they don't then don't show it until they ask for it.