1

Background:I'm a not very experienced software developer, currently working on a legacy project where all the business logic is in the database(sqlserver) using stored procedures. Now we are asked to rewrite the application from foxpro to .net but using the same database.

Question: My team architect suggest a n-layer architecture with a BLL(Business Logic Layer) but all the business logic is in the database, and there is no plan to have business logic out of the database due to organizational policies. I believe that the BLL is useless in this case, could you share your experience and justify this decision if agree?

2
  • 3
    I feel like we're not getting the whole story. You might want to ask your architect how he came to the conclusion that a BLL is necessary and then convey his opinions here so we can critique constructively.
    – Alternatex
    Commented Jul 22, 2016 at 13:37
  • BL in a database usually proves to be very slow as well. Recently I have optimized a stored procedure by pulling it from the database into PHP and doing most of the work in the code. In that case that alone reduced the execution time 170 times (from several seconds to a few miliseconds).
    – Andy
    Commented Jul 22, 2016 at 20:10

2 Answers 2

Reset to default
0

Though it does seem supperfluous it could lay in the ground works to make the move at a later time easier. You could however emulate the business logic by having thin wrappers where all the db interaction code will be. Then use this to construct your Data objects for use in the rest of the system.

On the other side of the argument is YAGNI... unless it is alreay planned that the stored proc code be moved to a proper business layer you could just be adding layers to a system that was not designed to receive them. In other words the app will end up having a placeholder, with all the added complexity this might entail, that will never really be used.

3

Putting business logic in a DBMS can afford some logical layering, but has significant practical and strategic drawbacks:

Lock-in. Your code will be bound to that database and its available facilities, probably forever. Database migrations have historically proven extremely hard, and are thus very rare. Database lock-in has been an extensive and expensive business problem. The more tightly coupled you are to DBMS facilities, the harder it will be to ever separate from those dependencies.

Weak development tools and ecosystem. You can program stored procedures and other embedded behavior inside PostgreSQL, Oracle DBMS, DB2, SQL Server, etc. But that style of coding lacks the rich ecosystem of IDEs, debuggers, test frameworks, code profilers, module repositories, build systems, etc.--everyday tools in Java, Python, JavaScript, and just about every other major programming language. Coding the majority of logic inside a DBMS is not the modern development norm, so both vendor and community investment for it is radically weaker.

This weakness is probably permanent. I've had significant discussions with CTOs at IBM, Microsoft, Oracle, and other DBMS makers about database-resident logic. Some, like Microsoft, are relatively positive. Most, however, are dismissive of or hostile to the idea; while they support it in key dribs and drabs like stored procedures, and while they appreciate developers locking themselves into their products whenever possible, they don't generally see logic-in-the-DBMS as a style of development practiced by most customers, thus not a sound place to invest significant resources.

Failure to support -ities. One of the key motivators for layered architectures is separating concerns to support modularity, scalability, availability, testability, flexibility, security, and other "-ities." Middleware engines such as application servers designed for N-tier, layered architectures often have significant abilities to isolate components from each other (for security and availability), to scale up or out (for scalability), to recognize and dynamically adjust to failed resources (for availability), and otherwise leverage the separation of concerns that layering enables. Putting logic inside a DBMS defeats many of those opportunities. Even when DBMS clusters are supported, the semantic guarantees for which databases are beloved (e.g. referential integrity, ACID) are weakened. Their internal logic programming systems have similar potholes in multi-instance support.

Impedance mismatch. With business logic in the DBMS you will still face the problem of "impedance matching" between DBMS constructs (e.g. tables and relations) into programming language constructs (e.g. objects and collections). Business logic layers often transparently up-level raw data into business-relevant objects (either directly or with the assistance of a related "business object," "ORM," or "domain" layer.) Dropping your business logic into the DBMS means that you don't have such a convenient place to "shim" DBMS results. Meaning the application code that touches your data will either touch lower-level constructs, or still require an outside-the-database mapping layer. It's not an impossible structure, but it's unwieldy.

4
  • I strongly agree with the vendor lock-in point. But that would be true also with triggers. Are we supossed not to use triggers to avoid vendor lock-in? The database should reject invalid (businesswise) transactions even if someone bypassess the application and uses another SQL client. By the way, it's no longer a one-app<->one-DB world.
    – Tulains Córdova
    Commented Jul 22, 2016 at 18:59
  • 1
    I would also specifically highlight testability to the "ities" mentioned above. Having a business logic layer outside the DBMS makes dedicated testing of true business logic (i.e. calculations, transformations etc) possible without having to worry about what the actual data source is. As an example an in-memory database like H2 can be leveraged to set up unit tests of true business logic hence breaking dependencies on a production/QA database.
    – srrm_lwn
    Commented Jul 22, 2016 at 19:11
  • 1
    Any DBMS features depended upon potentially risk lock-in. The point is not to completely avoid them--else, why use 'em? And every product/technology has potential lock-in. The point is to manage how you use them, to mitigate dependence. External business logic would let you use triggers, stored procs, or special data types in DBMSs that support them (e.g. PostgreSQL), but polyfill them for DBMSs that don't or that have weaker native facilities (e.g. MySQL, MongoDB), without impacting the bulk of app code.
    – Jonathan Eunice
    Commented Jul 22, 2016 at 19:12
  • The tooling is a very strong argument, but I have heard the lock-in argument so many times and it just leads to choosing the lowest common denominator, which usually means poorly performing, confusing, or over-complicated code, which means longer dev time day over day just in case you one day switch DBMS's. It's basically paying a high recurring cost to mitigate a speculative one-time cost, like insurance.
    – Brandon
    Commented Aug 3, 2016 at 1:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.