I need to know the disadvantages do keep Business Layer in Procedures ...

Question

In my work, i have a friend that insists to keep the business logic inside the database using stored procedures ...

What are the arguments that i can use to convince him not to do such thing?

The reason he want to do this is because we have multiple systems with diferent plataforms (Web Application in .NET with VB.NET and another Desktop Application developed in Power Builder - Sybase).

Thanks!

Answer 1

What are the arguments that i can use to convince him not to do such thing?

What are your arguments against it? Why do you necessarily want to stay away from stored procedures?

This is actually not a bad idea. If you have different systems written in different languages access the database, it may help to keep at least some of the business logic in the database.

Answer 2

I would go for the web service for several reasons:

• You can expose your services to all kind of clients to use it
• It's more flexible, more convenient for debugging, for testing
• You will gain a better source control support
• Your programming language will be probably more expressive than SQL

Answer 3

• Not strongly-typed.
• Cannot easily refactor, breaks clients at runtime.
• Not unit-testable.
• Most likely not all business logic can be a stored-proc so your logic is split amongst db and code.
• Stored procedures are not as capable or powerful as a programming language
• Harder to obtain the right level of encapsulation requiring your client to know the internals of your data schema
• Harder to version
• Business logic is tied to a database vendor which you'll be stuck with for life

Edit: other user-contributed points:

• harder and more expensive to scale

Answer 4

Define business logic

Anyway, you simply use the DB to do what it does best. Stuff like aggregation and data integrity.

But every system is a compromise. If you have 5 different sets of clients, then the DB might be the best place. Not every spreadsheet or Access DB can call your beautiful middle tier web services...

Answer 5

Pro web services, con database logic:

1. If your database is shared by others your app will now be coupled both at the data and logical layers if you put the logic in the database. That's usually a bad thing.
2. I would be concerned about load on the database server. If your relational database is getting hammered by queries and calculations you won't have the possibility to scale it as easily as you would if the middle tier is doing calculations.

Pro database logic, con web services:

1. If the database is wholly owned by your app, perhaps putting the logic in it is an acceptable choice.
2. I don't worry as much about the fear of switching databases, because that happens more rarely than middle tier changes. Middleware fashions come and go, but the data is the crown jewel of any business.
3. Web services have their downside, too. Translating XML to objects and back is a waste of CPU cycles. In-memory calls are orders of magnitude faster than network calls. A single roundtrip to your "simple" service today might be 200 ms, but start adding those up and pretty soon your SLA will be gone.

Don't give in to dogma. Consider the pros and cons of both and make a rational decision based on your app's particular situation. Both you and your co-worker sound like you've make it a black or white emotional decision. Remember: the outcome doesn't reflect on you as a person. You'll find out about what it says about you as a designer after it's in production.