Business Logic in the Database
Early in the Design phase for Elfenware, we made a strategic decision to place the business logic in the database. This approach runs counter to how most applications are written today with the current trend being to push the logic to the client’s browser. In fact, many programmers believe our approach is just plain wrong. Well, truth be told they are wrong, at least with respect to the type of application we are developing.
Let me explain…
Elfenware is a database-centric system, using SQL Server and .Net Framework. Client instances will be hosted on Azure, with shared, dedicated and high availability options.
The three (3) main reasons we chose this strategy are as follows:
- Make it extremely difficult to reverse engineer our proprietary database schema
- Provide superior security and access control
- Minimize the amount of future code remediation (as SQL syntax does not change)
In addition to these benefits, below is a listing of Pros and Cons for placing business logic on the database.
Pro data layer.
Performance. Parallel processing and cheap memory benefit modern databases as the data and the processing are on the same node. In addition, query optimizers (execution plans) in modern databases are extremely powerful. Elfenware is business software and therefore heavy on reporting and datamining, and 4G SQL is the best programming language for these functions.
Data integrity. SQL provides foreign keys and constraints (e.g., unique, NOT NULL and check constraints) that can model many of our business rules in simple ways, with arguably less risk of programming errors.
Data security. Direct control over which users are allowed to access data at the schema, table, column and row levels. And no one can see the actual Elfenware database schema and note we have written code to eliminate the need to directly access (and thus expose) the schema.
Data logic reuse (in database-centric systems). As an example, we use a single stored procedure to replace code that would have to be written in multiple parts of the object-oriented frontend code.
Re-platforming cost. By having the business logic in the database, in the event we wanted to change the frontend code or use a different language, there would be less code to program. In a future post we will explain the frontend design which leverages Clean Architecture and SOLID design principles, further facilitating web page redesign or re-platforming.
Con data layer.
Business logic complexity. Object oriented programming have two (2) features which are hard to model in SQL: inheritance and identity.
Developer tools. SQL is seriously lacking in development tools, so the developer has to do all the work (excluding the execution plan).
As will close in on releasing the beta version of Elfenware, we are seeing the benefits of placing the business logic in the backend, and we are confident we made the right decision.
Please feel free to share your comments or questions!
Leo Aldecocea, CTO