October 25, 2007 at 3:57 am
I have an interesting position at present in a small firm that is growing fast. The IT Development Team are currently defining development strategy for the future and there current recommendation is that the database should contain no business logic whatsoever....
Comments and feedback welcome on how to approach this interesting suggestion.
Cheers,
Knight :w00t:
October 25, 2007 at 4:20 am
r they on a hunt for a Architect kind of role in this company. The kind of a person should be well versed in Cutting edge technologies & should have relevant experience with Database etc.,
October 25, 2007 at 10:17 am
So they want to create a database with no business logic and the business logic will reside in the stored procedure, is that what your company want?
October 25, 2007 at 10:20 am
That is what my senior developers and architects are recommending whilst I'm sat here pulling my hair out 😉
To me business logic should be in all layers,not one.
October 25, 2007 at 11:26 am
Relational integrity can't be guaranteed from the business layer alone. It just can't - you could skip some logic at any time, and oops, there it goes - you have orphans.
Not everything can or should be done synchronously. Some things (and yes - some business-logic related things) should be done asynchronously through scheduled tasks. The best, most reliable, most secure place to do that is from SQL Agent.
Look - if they want to include SQL Projects with the various stored procs, etc..., in their business layer documentation - that's fine. Hell - even SourceSafe will do that with SQL Server 2005. But confusing "something runnning on SQL server" with "part of the data layer" clearly stems from someone with no understanding of client-server processing. That would also tend to point to your "architect" not having any understanding of what a RDBMS can actually do for you.
It's kind of like buying a high-performance vehicle for the ashtray.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 25, 2007 at 11:40 am
What that often means coming out of the mouths of developers is that they want to use some code-generators and write zero T-SQL.
Data access will be either all run-time code-generated dynamic SQL or a mix of run-time code-generated dynamic SQL and build-time code-generated CRUD procs. The database will be full of over-normalized tables with only identity columns for keys.
When a decent amount of data hits the database and performance stinks, they'll blame SQL Server and ask why you can't tune it any better.
Just ask them "Who will write the database code?" If they have a specific plan for who will handle data access, then I'm wrong. 🙂 If they're using ORM and generators, they'll be glad to tell you how much time they're saving by using them.
Remind everyone - as often as you can - that performance and data integrity must be baked in. Adding a few indexes after it's built will not solve bad design.
-Eddie
Eddie Wuerch
MCM: SQL
October 25, 2007 at 6:16 pm
Most companies like to put business logic in one layer, especially if they are writing web programs so they can have a thin client, and also this way it is harder for the hacker to break into your system. I don't know what kind of application you are working on, they may have a point to have the business logic in the stored procedures. In this way if something goes wrong, it is easier to find out what the problem is.
October 25, 2007 at 10:02 pm
But - what exactly IS a data layer "without business logic"?
The data structure itself is "business logic".
The indexing scheme is dictated by "business logic".
The Keys, primary or foreign - all dictates of the business logic you're implementing.
If you want it entirely devoid of business logic, go to flat tables, all varchar(max). Forget relating them.
On the other hand - formatting data for input is business logic - does that belong in a stored Proc? Hell - while we're at it - why not build the stored procs to output the HTML code - that way you don't have any business logic in the presentation layer either. And oh what a fine performing server you get (believe - I know all aobut that one. I had another "genius" architect like that too).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 25, 2007 at 10:06 pm
If nothing else you can use constraints. triggers and referential integrity to enforce business rules. Ideally you could force your developers to use stored procedures, etc. for access to the database but ORM's, etc. are definitely on the rise/make sense from a developers perspective.
The standard argument for stored procedures has always been that they improve performance but the rise of ORM's that generate strongly typed, repeatable SQL (e.g. paramaterized SQL Commands that can be cached just like store procedures) have largely made that particular argument moot - ORM's do not generate dynamic SQL...
Given a push comes to shove choice, make the developers call out the rules and then make sure that the database won't accept data that doesn't meet those rules.
Joe
October 26, 2007 at 11:51 am
The data structure, the primary key, the index are all belonged to the database side. Of course if you put in foreign key, trigger to enforce data integrity, this is considered 'business logic', otherwise those considered data logic.
Example of 'Business logic' - if a customer reaches $10,000 debt, sends the credit department a warning; or changes the customer status to 'I' as inactive and the customer cannot order anymore.
October 26, 2007 at 12:09 pm
My experience with seeing this in many large companies as well is there issue is portability of the code. Business are always looking to save a buck or someone comes in an changes a software standard and their goal is to minimalize impact to systems if direction changes.
This is an admiriable goal but has some flaws in the logic.
1) You assume you can just translate the changes between say, SQL Server to Oracle, MySQL or other) by changing the data connection. This is try as long as you 100% minimal things to simple statements, however versioning can also have unexpected differences such as the support of JOINs thru things like INNER, OUT and such or the need to have to use old style ANSI joins. There will always exist a chance to have to rethink the whole thing.
2) You code in such a manner as to actually loose many of the performance gains associated with the software you choose. Why then would you bother unless you are going to see a performance gain even by upgrading then.
3) For maximum portability you have to fluent in all systems to ensure capatibility is maintained for the purpose of ready need to port.
4) The database can loose integrity by not having any business logic involved such as constraints that a money can't be negative on a bank account. You will have to explicitly disallow any other access to the system and account for every possibility of code flaw there is so your QA has to be set to a higher standard to ensure all outcomes are tested and accounted for.
5) You will have work write an external process to perform event firing for a lot more things than maybe you intended to have to do because the purpose of the tool was taken away.
My issue has been then why do you pay for a product you don't intend to use, a set of flat files can be built that perform equally as well in this case.
October 26, 2007 at 12:48 pm
Loner (10/26/2007)
The data structure, the primary key, the index are all belonged to the database side. Of course if you put in foreign key, trigger to enforce data integrity, this is considered 'business logic', otherwise those considered data logic.Example of 'Business logic' - if a customer reaches $10,000 debt, sends the credit department a warning; or changes the customer status to 'I' as inactive and the customer cannot order anymore.
I'm fully aware of what Business Logic is. But the data layer is built in large part to directly sustain said business logic. The data layer, relational integrity and all, constraints and all, data model is dictated by the need to enforce/support or create business logic.
Talking about fully separating the two is like talking about separating your head from the rest of you: makes it kind of hard to keep functioning:)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 30, 2007 at 9:10 pm
If they believe they know how to do database and queries, and you think they don't then don't argue, just sit and prepare set of scripts to populate their tables with 3..5 years worth of data.
As soon as test database is ready run it and let them enjoy their design.
And don't listen to statements like "this table never gonna exceed 50 rows!".
Just offer a contract: "If the number of rows in table {table name} exceeds 50 then {his/her name here} pays {your name here} amount {how much does a good house cost in your area?}"
They not gonna insist after this.
_____________
Code for TallyGenerator
November 1, 2007 at 8:19 am
Sergyi: Bwahahahaha! Spot on!!! It's an even safer bet when it's the users who come saying, "It'll never need to {fill in whatever here}".
Another reason to put business logic in the data layer: the DBMS can usually enforce DRI more efficiently than code.
Put the rest of the business rules into stored procs, and the front-end(s) can be written using whatever is the flavour of the moment / the most appropriate tool for the task.
June 5, 2008 at 12:18 am
I understand this is a SQL Server forum and as such the majority of readers are going to be database enthusiasts, so I'm anticipating some flaming, but here goes regardless.
You seem to have data integrity very confused with business logic. If you consider the overall picture of application development, this includes multiple tiers in any reasonably sized application, primarily some kind of UI/Client, the application layer and the database.
You need to consider very carefully the roles of those three layers. The role of the UI or client is to provide the user with an interface, a way to interact with the system. The Application layer is reponsible for interpreting the UI requests, applying business logic to the requests and then storing information in the database (and/or retrieving data from teh database).
The role of the database in this grand picture is to provide housing for the data. It needs to do this in an efficient and structured manner. I completely agree, that the database is 'a' place to enforce referential integrity, and is generally the easiest place to do this. This in turn enhances the performance of your databases. What needs to be realised though is in a development lifecycle, systems are designed on an object-relational model. This is then interpreted and normalised to form a data architecture.
You should never compromise your RI in the database when someone mentions business logic, and your database structure should be decided well before anyone starts coding. That being said, the database tables don't (or very rarely) map directly to the objects being used in the system. There is a Busines Logic Layer in any traditional application architecture that lives in the application tier, and its purpose is to apply business rules to objects. These are solid business rules derived from requirements gathering. The 'business rules' you are applying with RI and such constructs are inferred when the OR model is translated into a schema.
Business Logic, in an application sense should not be tightly bound to the database. The business should be able to change business rules without changing data. It should change how objects interact in the system and this has no relevance to a database structure.
So in answer to the original post, businesses talking about putting business logic back into the application means they are correcting bad architecture choices made early on. They should never touch RI, indexes, keys, data. Many old systems have crammed business logic into stored procedures and this is a bad practice. The purpose of tiering applications is to promote pluggability and reusability. I should be able to move from one database to another with the same structure and have a system function identically. I should also be able to plug a new system on top of the same database and interact with the database intuitively and not create a whole new set of stored procedures for the new system. Stored procedures are the gateway to a well kept database and should reflect the database, not the aplication sitting on top of them.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply