Business Layer vs SQL Server

Question

I have an application that does complex calculations for members. Each member may have several US states associated with their profile. Each state has different calculations for each course that a member performs.

I am currently doing calculations in the database (SQL Server 2008), and then sending the data back to the application level, where they can see their history, and then upload the certificate for each course.

I have a level of business logic, but not much there. I know this has been asked a lot, but where do you think I should perform these calculations: the business layer or the database? I go back and forth!

Answer 1

I would basically do something in SQL Server to:

• does a lot of summing, counting, averaging, etc. data and returns only one value. In fact, it makes no sense to transfer large amounts of data to the middle level, just to summarize the column

• performs many manipulations based on strings / sets; if you need to copy, paste, update a lot of data, again there is no point in dragging all this data to the middle level, and then sending everything back to the server - do it on the server from the very beginning. In addition: T-SQL works much faster with many operations (e.g. moving data around) than anything from your middle tier

In short: try to avoid sending large amounts of data to the client / mid-level / business level if you do not have to (for example, because you want to load a file stored in the database, or if you really need these 200 lines to materialize into objects in your application so that they appear somewhere or work)

One of the functions that are often not taken into account are calculated columns directly in your database table - they are really good, for example. adding up the amount of your order plus tax and shipping to the total amount, or they are great to combine your first and last name into your display name. Such things really should not be processed only at the level of business logic - if you do it directly in the database, these "calculated" values ​​are also available to you when checking database tables and viewing data in SQL Server Mgmt Studio ...

---

I would put it at the mid-level / business logic level

• if extensive logic testing, string parsing, pattern matching, etc .; T-SQL sucks in these things

• if for this you need things like a web service call to get some data to validate your data, or something like this

• if more business logic operations are required (rather than strict "atomic" database operations)

But these are just “rough” recommendations - this is always a design decision for each case, and I do not believe in strict rules; Your mileage may vary from case to case - so choose one approach that is best suited for any given task.

Answer 2

This helps to avoid having business logic code inside the database (stored procedures). It is much better to have it directly in the application, so that it matches the architecture. This SQL code contains your business logic, and there is nothing wrong with that. (There is nothing wrong with sprocs having data or service related code).

If your level of business logic does little and simply transfers data from SQL Server to the caller, you may not need it at all.

Answer 3

The level of business logic is not intended for heavy lifting, the purpose of which is to provide abstractions by entities in the language of the subject. Thus, the business layer can be used to provide a collaborative and consistent approach for any layers / applications that are necessary to work in this space.

To build things up, to make a point, the ultimate goal would be that the level of business logic is used throughout the organization for all applications running in this subject space. That is, wrapped in a service, etc.

In the real world of small applications, to do this or that, the level of business logic is felt several times as an application. The trick is to also remember that any use cases should be implemented as tests against the business layer, giving you another way to think about what the public interface should look like.

As the business logic layer does this work, it must be hidden from the parts of the application that invoke it.

Thus, it is perfectly acceptable to compute the data in the most efficient way (i.e. sql) if these calculations are given an appropriate presentation at the level of business logic.