December 1, 2008 at 8:34 am
These posts have been very interesting to read.
For our in-house development, Visual FoxPro has been our primary development tool, although Microsoft's actions have pushed us to move toward and become more fluent in both SQL Server and .NET. One very good outcome of using Visual FoxPro, is that it required all of our developers to become knowledgeable about both coding and databases. (Also, for non-web-based development, Visual FoxPro is far more RAD for major applications than anything .NET.)
Coming from that background and perspective, we would definitely gravitate toward using the database for all it's worth. Why spend so much for SQL Server licensing (FoxPro requires no user licenses, only developer licenses), if all you're using it for is "dumb databases"? (We have hundreds of database users, and our combined, one-time-only, software cost is less than $3,000 for all our Visual FoxPro licenses - try a cost/benefit analysis of that, to hundreds of SQL Server CAL's, server licenses, etc.!) We have SAN's and other backup strategies, so purchasing SQL Server for that purpose isn't merited either.
So, for us, the route looks to be setting up as much as seems effective in SQL Server using stored procedures, and developing middle-tier business objects to interface with those. However, I can understand, that if you're developing software to sell (instead of in-house development), that it simplifies some things for you if you only focus on the application code and use a "dumb database". If you can assume that your customer already has invested in SQL Server, you don't have a need to justify the cost of the database licensing.
Best wishes,
Randy
December 1, 2008 at 8:53 am
Thanks for the editorial Tony, it's not everyday that I get to read phrases such as "surreptitiously hide irrational dogma" :hehe:
Andy
December 1, 2008 at 9:41 am
Thanks Andy ;). As is often the case on SSC, the discussion is just as (if not more) interesting than the original post.
Both approaches have their place, though I would have to say that it often seems like you have to "fight harder" for a more database-oriented approach. Grant summed up well one of the main points I was trying to make: people tend to latch on to the way Microsoft has built a certain application, and that way quickly becomes "the only way" to do it.
December 1, 2008 at 11:09 am
Reading the original editorial and the followups, I am not too sure where I fall in Tony's taxonomy. On the one hand we use the SQL standard data integrity and association management capabilities where possible. Our application runs across multiple databases, so we stick with Standard SQL. But we treat the database as a database and try to leverage its strengths. Data does not change, while business rules are very dynamic, so we do not put business logic into the database ever; that stays in the application. Our business rules operate against the application object model, which we map with Hibernate. It makes for an application that easier to understand, and is, therefore, more agile.
The goal is to use the right tool for the job.
December 1, 2008 at 12:09 pm
sgardner (12/1/2008)
...But we treat the database as a database and try to leverage its strengths. Data does not change, while business rules are very dynamic, so we do not put business logic into the database ever; that stays in the application...
First, I am going to assume that you meant "the Data Schema does not change" as there are not many databases where the data never changes.
Secondly, you do realize that these two sentences are contradictory, right? Keeping all business rules off the database is not leveraging its strengths at all. It may be leveraging your strengths as an application developer, but not using the built-in RI (referential integrity) features is abandoning one of the principal strengths of relational databases.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 1, 2008 at 12:35 pm
samuele.armondi (12/1/2008)decisions should be taken based on 'this is the best tool for the job' rather than 'this is the tool I know hence it will be better than everything else'.
QFT. From your lips to everyone's boss's ears...
I tend to run down the middle as well. The database controls the data model, and I usually use stored procs for data access, but complex logic tends to stay in the application layer. I get some pushback from those who don't understand databases well and don't like stored procs because they think all code should "be in one place" but luckily I am mostly empowered to do things as I think they need to be done.
--
Anye Mercy
"Service Unavailable is not an Error" -- John, ENOM support
"You keep using that word. I do not think it means what you think it means." -- Inigo Montoya in "Princess Bride"
"Civilization exists by geologic consent, subject to change without notice." -- Will Durant
December 1, 2008 at 12:41 pm
Alexandros A Nipirakis (12/1/2008)
Having developed using both of these extreme paradigms, I can honestly tell you that having your business logic primarily implemented in the database is usually the best way to go. If not completely in the database, then surely within some web service logic that separates the data access and the client.The reason for this is simple, as a developer one must always assume that someone down the line is going to want to talk to your database. Not implementing your business logic at a layer that lends its self to being opened up to other developers (IE, either stored procedures, views, functions, etc. in the database or at the very least with a robust web service api that implements the business logic correctly).
One problem I have run into is when developers do not structure their databases correctly (for whatever reason, the concept of a relational database seems to elude many developers). They may make a very complex database with weirdly named columns, without constraints, and of course without any kind of business logic implemented at the database level. When someone comes later and tries to interact with this database (as often will happen) the second developer wreaks havoc on the database because they are not putting the correct data in the places where the original application assumes that they are going to be. What's worse, is that without a good set of database API's for the secondary applications to interact with, the database becomes corrupt because of missed, unimplemented business logic in the secondary or tertiary applications that come down the road.
Telling someone -- HEY if you want to create a sales order, call this Web Service, or HEY if you want to create a PO, just call this stored procedure makes it much easier for future applications to use your database. If your application doesn't require this kind of code reusability, then don't use SQL server. Store all the information in a lightweight database (like SQLite or FirebirdSQL) and leave the real database stuff to the pros. Using SQL Server (or Oracle) insinuates that you are making an Enterprise (or at worst professional business level) database driven application. I think if you are going to play with the big boys, then you ought to program like the big boys do.
Again, anyone can disagree with me, but I think that we need to jump into the 21st century and understand that our database engines today have the ability to do many things, the best of which is that these new database engines allow us to abstract business logic from within our applications, creating a truly extensible framework with which to create our own applications that can live on for years to come.
Aleksei
Most of what you describe here is not what I would call "business logic". Creating a PO isn't business logic - the rules for determining what to put in the PO is. So you have a stored proc that creates the PO, but your application code will figure out who it's for, what sales rep to assign it to, whether they get a discount based on their sales volume etc. Applications call the logic routine (probably in a service) and then once the logic routine has figured out the details of the PO, it will call the stored proc to actually insert the records.
If you make the stored proc handle the business logic as well as just the data access for creating the PO you will have issues if one department uses different business rules to create a PO than another. Your stored proc will either become a spaghetti nightmare of "if PO.Type=X then do these rules" or you will have to create different stored procs for every variation, even though the "shape" of the data is the same. This is the main reason behind separating business logic from data access in the first place.
--
Anye Mercy
"Service Unavailable is not an Error" -- John, ENOM support
"You keep using that word. I do not think it means what you think it means." -- Inigo Montoya in "Princess Bride"
"Civilization exists by geologic consent, subject to change without notice." -- Will Durant
December 1, 2008 at 1:07 pm
rbarryyoung (12/1/2008)
sgardner (12/1/2008)
...But we treat the database as a database and try to leverage its strengths. Data does not change, while business rules are very dynamic, so we do not put business logic into the database ever; that stays in the application...First, I am going to assume that you meant "the Data Schema does not change" as there are not many databases where the data never changes.
Secondly, you do realize that these two sentences are contradictory, right? Keeping all business rules off the database is not leveraging its strengths at all. It may be leveraging your strengths as an application developer, but not using the built-in RI (referential integrity) features is abandoning one of the principal strengths of relational databases.
No, I meant that the data does not change: at least in general. We often add to data, append additional information and the like. The core of the data does not change much. To use one of the canonical examples: The PO. The customer information does not change often. Order data is entered and then added to as the order is processed, but again, it does not change. The business rules for processing the orders changes frequently: what the current pricing model is, incentives, discounts, credit etc. These are implemented in the application, not the database.
Referential integrity is not a business rule, it is a data integrity rule and part of the relational model. I use it extensively in the relational model of the enterprise, but never make the mistake of thinking that the relational model is the enterprise.
I am speaking more about implementing half of the application logic as stored procedures in the database and the rest in the application logic. There are several reasons that I consider this a bad way to develop. The first is that we run across multiple databases, so portability is a huge issue. The second is if half of the business logic is in the db and the other is the application, then there are going to be inevitable cases where the required logic is either missed or duplicated as the system evolves. Keep all like sub-systems together. Third, it makes for much cleaner development and testing; I can test my business logic without worrying about the database and contra-wise. Last, we have import/export requirements to other systems, so we have to keep the model clean and consistent.
December 1, 2008 at 1:12 pm
And then there are those people who irrationally hide surreptitous dogma...
December 1, 2008 at 2:11 pm
My approach is to build a Business Layer which is normally a .NET component or web service layer. Behind this I implement the best solution for the problem at hand, n-tier, stored procs, constraints, access to non-SQL stores etc.
However for hard core integrity requirements you cannot do better than to correctly use primary keys, foreign keys, constraints, transactions, stored procs etc. Relational databases continue to be the most powerful tool in our development kit but are unfortunately under utilized by large number of developers leading to bad integrity and late projects.
December 1, 2008 at 3:03 pm
Bob Abernethy (12/1/2008)
And then there are those people who irrationally hide surreptitous dogma...
Amen to that, brother.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 1, 2008 at 7:45 pm
Andrew Peterson (12/1/2008)
From an architecture approach at the database level, typically you created the db schema based on the business objectives. Now, with the database object abstraction tools, developers come in, use these tools to actually create the db schema, and literally take the stand that they don’t need database architects/DBA’s. The JBOT approach (just a bunch of tables). The problem is that the database schema tends to get ugly, grows in an ugly manner, and is difficult to optimize. (I just finished a project to try to clean up such a mess).
I have had to deal with just such similar issues. Unfortunately, and I know this is not universal, but most of the young crowd doing web-based application design that I have come into contact with, know little about a database, and do exactly as described above. Some in the .NET world, but far more in the open-source world. The sad truth is they do not see the performance hits until the database is too large to change easily. Trying to argue it is painful... keys, relational integrity, storage and disk allocation, security, etcetera... all seem to be foreign language. One poignant case in particular, even database server specs were at issue.
"Watch what happens to your performance when you hit the 2 GB mark, given your current setup and implementation."
"Nothing will happen."
Not long after, "It" happens. Mr. "Nothing-Will-Happen" leaves for another offer. New person is hired... same type, as HR was only told to replace Mr. "Nothing-Will-Happen". Trying to convey what happened and what needs to happen falls again on seemingly deaf ears.
Mr. "Status-Quo" says, "It's not the app, nor the implementation, it's the reporting." (You know, the ones actually trying to get data OUT of the system...)
He instead decides to implement a 2nd identical server and mirror via log shipping... for the reporting clients to pull on. Writes go to one, reads from the other. Inconsistencies begin to abound. Clients become "concerned" about down time. Some threaten departure.
Mr. "Status-Quo" leaves for another offer. 3 more people (all are admins who know how to install said DB platform, and punch command-line statistics only), and an 3rd party off-site vendor monitoring tool are employed to monitor the server health and uptime. Yet a 3rd identical server is also thrown in for good measure. (This boils down to fix it fast and get it up - every time). Root causes not prosecuted. Company rolls through multiple IT department heads, but not a single DBA position implemented, nor even temporarily contracted. Lost opportunity costs are mounting.
December 3, 2008 at 2:39 pm
As a VB developer since VB 3, Access was the DB choice. In fact Access was developed just for VB. Other mostly ISAM databases were around and we frequently developed to take existing DOS applications into the windows world. Ad-Hoc query language was the norm.
I'll admit that learning T-SQL was not something that I couldn't wait to do. It sort of had to be pushed on me gently, but I did begin writing procedures in SQL Server 7, and it was only a year after that when 2000 was released with it's improved client toolset.
I've learned to relish what can be done with T-SQL. As DBarryYoung says, it is foolish not to utilize the built in enforcement rules of the SQL Server database schema design. I would add to that statement that it is foolish when designing an application that requires layered security, to not use SQL Server security utilizing AD or Local Security Groups. Next in the Business Logic train are properly built Stored Procedures that reenforce the schema rules and add a few more where appropriate.
I don't do much web design. I'm a VB 2005 / 2008 programmer, and that makes for a good user interface, and is also the last place for additional Business Logic. The versatility and speed of writing VB code allows for easy interaction for enforcing data types, lengths, and the like.
I don't like to use infinitives, but a well thought design of an application with the bulk of the design being at the database level, will allow for many modifications, especially when debugging, that (never) have to be redistributed.
I don't work with many other DB applications these days, but SQL Server is way to powerful to be wasted as just a data repository.
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply