I have a database in postgres with 147 tables; these tables contain data generated by functions that have most of the logic of the system, for example how to build an accounting entry.
These functions are called by an application in java that has almost no business logic in the code.
This database structure is used for different clients and some of these functions have small adaptations per customer with comment in the function body.
This approach where most of the logic is in the database is correct?
If you update a function in a database and we must pass it to each other how we can easily distinguish which function has changes To dont delete or replace a change in client database by error?
Two queries - two replies:
a) Placing business logic to database has strong defenders and strong opponents. Lot of arguments for/against are volatile and valid only for some configurations and environment. Some databases has not good capabilities for stored procedural programming, some companies has not good personal resources for programming in relative different environments. Some projects doesn't require really effective data processing and more valuable is simply monolithic deployment. There are a some arguments for placing business logic to database (only processes that are not INTERACTIVE!). You have to divide application to interactive (presentation) layer and non interactive layer (what is usually a best way, what you can do):
Generally I can say, a power of stored procedures is more significant in heterogenous environment and less in monolititic single application environment.
b) PostgreSQL has a CREATE OR REPLACE FUNCTION statement and data visibility based on snapshots - so update in production usually is not problem. You can update database dictionary (with functions) under transaction.