Trying to return rows between a given id and the same id + offset, where should most logic go to determine how to retrieve the data?: BLL or DAL

Question

Hi,

In my database I have a table called ThingsInACircle. Every time a Thing is added to the ThingsInACircle, it gets added with a ThingId that is auto incremented.

Imagine the Things in this table as being in a circle.
SELECT Thing FROM ThingsInACircle WHERE ThingId = 10 is next to
SELECT Thing FROM ThingsInACircle WHERE ThingId = 11.
Also...
SELECT Thing FROM ThingsInACircle WHERE ThingId = min(ThingId) is next to
SELECT Thing FROM ThingsInACircle WHERE ThingId = max(ThingId)

I want to be able to say: For a given ThingId and an offset, return all of the records in ThingsInACircle ranging from the ThingId to (ThingId + offset) (offset may be negative).

So, take this as an example set up:

Here is a list of our ThingIds: 1 2 3 4 5 6 7 8 9

If I want all the ThingIds where @ThingId = 2 and @offset = 3, there's no problem

SELECT ThingId FROM ThingsInACircle WHERE
ThingId BETWEEN @ThingId AND (@ThingId + @offset)

and it would return: 2 3 4 5

But, if I want all the ThingIds where @ThingId = 8 and @offset = 3, then there's a problem

The function should return: 7 8 9 1

So here's my dilemma, should my Data Access Layer contain a query that uses a more complicated stored procedure (Selecting min, max, and using if & else to determine if they have been exceeded) to determine exactly which records to retrieve in order to treat the records as being linked in a circle?

Or should the Business Logic Layer determine whether or not the user has requested an id + offset that exceeds min or max and then use simple DAL methods to accomplish what it needs to return?

This may just be a matter of opinion. I just started learning about three-tiered structure two days ago by following microsofts tutorial, so I just want to get an idea of how some people think DAL and BLL should be formed: If DAL should be simple and BLL should do all of the verifying... Or the other way around... Or something I've missed all together.

Answer 1

SELECT ThingId FROM ThingsInACircle 
WHERE
   ThingId BETWEEN @ThingId AND (@ThingId + @offset) 
   OR 
   ThingID BETWEEN 1 AND (@ThingID + @Offset - @MaxID)

@MaxID is the maximum thing id (end of the circle).

You have two cases:

normal case is when you have no overflow on offset. This is covered entirely by the second between.

second case is when you have an overflow, and you need to apply the offset over the boundary. In this case you have the range @ThingID - @ThingID + @offset, handled by the first BETWEEN, and 1 - ((@ThingID + @offset) - @MaxID), handled by the second BETWEEN.

If offset is higher than @MaxID, then all the @ThingIDs have to be considered, and this case is also covered by the second between.

Answer 2

I usually find these easier to deal with elsewhere than the DAL, especially if you're dealing with an RDBMS. Other kinds of collection abstractions (arrays, collections, dictionaries, etc.) will be easier to work with.

Answer 3

Most of the time (and in this situation), I would recommend starting with business logic (i.e. the last thing in a circle is next to the first thing) in the business layer.

This means doing at least two queries. The first gets your range of thingIDs:

int maxThingId = // select max(thingId) 'maxThingId' from ThingsInACircle
int minThingId = // select min(thingId) in the same query

so your second query will normally be:

select thingId 
from ThingsInACircle 
where thingId > @lowerBound and thingId < @upperBound

where the parameters were precomputed as:

int lowerBound = requestedThingId;
int upperBound = lowerBound + offset;

and then a third, if necessary:

if(upperBound > maxThingId) { 
  upperBound -= maxThingId - minThingId; 
  // third query:
  // select thingId from ThingsInACircle where thingId < @upperBound
}

If you start seeing performance problems or otherwise have issues with data integrity, etc. you can still consider moving this logic into the DAL.