A question that I've come across today that I thought I'd put out there.

When separating Business Logic and Data Access at what point does data access become business logic?

i.e. If I want to get all of the customers for region 2 and their total sales for the past month. Does data access mean getting all of the customers and then the Logic goes through the customers and goes to the DA for the sales for each customer and the BL then calculate the total.

Or does the DA get the customers and then the BL go through each customer and then go back to the DA to get a pre calculated total for each customer.

Or does the BL go to the DA to get the list of customers and a value for each customer in one go?

In all three cases the DA layer is only accessing the data but where do you draw the line?

Any help?

Thanks!

Hmm, the DB should be used to do the summing up...

The BL should collect the list of customers that need to be reported on and pass them as a parameter to the database layer, which will execute the relevant stored procedure, with the list of customers (or null list for all customers)... The stored procedure (or alternatively, if you aren't using SPs, a query built in the DB access layer) should then retrieve the sum of the sales for each customer - should just be a single query - and returns the data to the BL, which then fills out the collection of customers.

Accessing the database once for each customer, or worse once for each sale for each customer, is a very poor way to do it, it increases the amount of IO, processing and memory that would be needed...

The BL and DAL need to be a little flexible to fully optimise the process. You really need to think about how you will need to access the data, in order to minimise the number of transactions and maximise performance.

I think the flexibility is the key here.

At the moment our BL and DA is mixed into one lump with logic and data access throughout.

I think the trick is balancing small IO traffic and fewest database reads with code reusability.

I suppose if there is a particular data read that is performed over and over then this needs to be put into a DA layer so that the BL can reuse the code.

I think the trick is also in getting the parameters passed into the DAL correct. I.e. do you go to the DAL for each customer or do you go to the DAL once with a list of customers and get a list of output values, etc... (Thus allowing a single customer query (list of 1), multiple customer query or all customer query (empty list)).

Thanks for the tips

You should go to the DAL once, with a list of customers to return.

I tried it the other way round on a web site recently, which loaded the customer, then loaded a delivery and each product on the delivery that was in the studio and each movement of that product inside the studio. When testing, it was fine, but when it went live and they had a couple of hundred products on an order and half a dozen movements. The server pretty much died, because the number of reads a second exceeded 4000, for over 10 seconds, then started blowing memory limits.

Loading all the products in one read, then loading all the histories for each product individually solved the load problems. In fact we didn't load any history, unless it was specifically needed, the load became an extra method, as opposed to being part of the class instantiation.

Due to the way I had written the system, switching from loading everything in one hit, as opposed to individual reads took around half an hour, for around 50 classes. Likewise, we further reduced the load by having "paging" parameters: There was no point loading 250 products and their movement information, if the system is only display 10 of them.

That is an incredible valid point that is usually missed completely as the UI is the last thing that is devised or even worse is designed separately.

It is something we used to do (before my time).

Now we have a variable that can be passed into a query that determines the number of records it gets each time.