SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


how to avoid duplicate business logic


how to avoid duplicate business logic

Author
Message
norbert.manyi
norbert.manyi
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 76
Take the following example:

select * from tbl_Users U where U.active = 1 and U.deleted = 0



It seems that the application owner keeps changing the business logic, so I would like some opinions on how I can make the business logic as separated from the code as possible.

My first idea (given my coding background) was to create functions to be used like

select * from tbl_Users U where fn_IsUserActive(U.UserID) = 0

but I don't know about how much that would degrade performance...

Ps. I understand that this particular example can be done using views, but I'm taking about the situations where you cannot do this. (I just can't find a good example right now)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86328 Visits: 45232
norbert.manyi (10/2/2012)

My first idea (given my coding background) was to create functions to be used like

select * from tbl_Users U where fn_IsUserActive(U.UserID) = 0

but I don't know about how much that would degrade performance...


A lot. That query form means that indexes are useless and the only way to process that is with a scan.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Roland Alexander STL
Roland Alexander STL
Say Hey Kid
Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)

Group: General Forum Members
Points: 665 Visits: 461
norbert.manyi (10/2/2012)
Take the following example:

select * from tbl_Users U where U.active = 1 and U.deleted = 0



It seems that the application owner keeps changing the business logic, so I would like some opinions on how I can make the business logic as separated from the code as possible.

My first idea (given my coding background) was to create functions to be used like

select * from tbl_Users U where fn_IsUserActive(U.UserID) = 0

but I don't know about how much that would degrade performance...

Ps. I understand that this particular example can be done using views, but I'm taking about the situations where you cannot do this. (I just can't find a good example right now)


How is the application owner changing the business logic? Is the business changing their procedures? Or is more a case of changing SARG values? A little more detail's needed, I think, before we can begin to answer.

Roland Alexander
The Developing World


There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16030 Visits: 19524
norbert.manyi (10/2/2012)
...Ps. I understand that this particular example can be done using views, but I'm taking about the situations where you cannot do this. (I just can't find a good example right now)


Views would be a better option than functions - coded properly they won't hammer performance quite like the suggested UDF. You can't find a good example right now - if you still can't find one in an hour, would it matter to you if there was the odd documented exception to the rule?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
norbert.manyi
norbert.manyi
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 76
Roland Alexander STL (10/2/2012)
norbert.manyi (10/2/2012)
Take the following example:

select * from tbl_Users U where U.active = 1 and U.deleted = 0





How is the application owner changing the business logic? Is the business changing their procedures? Or is more a case of changing SARG values? A little more detail's needed, I think, before we can begin to answer.


For the previous example, we had to add a TempInactive field, so employees on maternal/paternal leave can be excluded. Also business decided (against my insistencies) that deleted employee records are no longer kept, so the deleted field became obsolete.

But I got a better example in the following post.
norbert.manyi
norbert.manyi
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 76
Views would be a better option than functions - coded properly they won't hammer performance quite like the suggested UDF. You can't find a good example right now - if you still can't find one in an hour, would it matter to you if there was the odd documented exception to the rule?

Example:
Employees can belong to regions and/or teams. Teams are split into organizations.
There is more than one type of team-leader. One that is directly above the employees, one that is in charge of a region and one that is in charge of an organization.
Most reports can be run by either type of team-leader for their employees, and they all do filtering at the beginning. (this is how it was before we took over)

Although this can also be done using multiple views and a few lines of code, I think this is getting closer to the 'cannot use views' scenario.
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16030 Visits: 19524
norbert.manyi (10/2/2012)
Views would be a better option than functions - coded properly they won't hammer performance quite like the suggested UDF. You can't find a good example right now - if you still can't find one in an hour, would it matter to you if there was the odd documented exception to the rule?

Example:
Employees can belong to regions and/or teams. Teams are split into organizations.
There is more than one type of team-leader. One that is directly above the employees, one that is in charge of a region and one that is in charge of an organization.
Most reports can be run by either type of team-leader for their employees, and they all do filtering at the beginning. (this is how it was before we took over)

Although this can also be done using multiple views and a few lines of code, I think this is getting closer to the 'cannot use views' scenario.


If you can't code it up as a view, then it's unlikely that you can code it up as a query.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
norbert.manyi
norbert.manyi
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 Visits: 76
ChrisM@Work (10/2/2012)

If you can't code it up as a view, then it's unlikely that you can code it up as a query.

You could still do it as a quite complex function, but I guess that would be wasteful.

Also, I am not stuck on functions vs views; if there are any other ideas/tips that I could use to keep the business logic more manageable, please mention them...
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16030 Visits: 19524
norbert.manyi (10/2/2012)
ChrisM@Work (10/2/2012)

If you can't code it up as a view, then it's unlikely that you can code it up as a query.

You could still do it as a quite complex function, but I guess that would be wasteful.

Also, I am not stuck on functions vs views; if there are any other ideas/tips that I could use to keep the business logic more manageable, please mention them...


Norbert - check out the two articles by Paul White in my signature block. Although the subject is APPLY, they will give you some useful information for constructing and using iTVF's (inline table-valued functions).

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Jayanth_Kurup
Jayanth_Kurup
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2897 Visits: 1351
faster simpler better coding relies on moving business logic to the application layer. This is proven in many SOA implementations. The DB should stick to simple crud operations. The stored procedure is already a layer of abstraction between the application and data , however in your case the data itself changes so functions and views or even normal tables need to evaluated on a case to case basis.

A table that had a id column as PK may not id a PK once a new column is added or deleted. Try putting business logic in .NEt and app layer and if that doesnt work evaluate how it can be done within a proc.

a common mistake i have seen people make is trying too hard to make the business logic generic. Its not a good idea coz the business logic is constantly changing to meet new scenarios and managing all that change within the DB is a major headache.

Jayanth Kurup
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search