IWETHEY v. 0.3.0 | TODO
1,095 registered users | 0 active users | 0 LpH | Statistics
Login | Create New User
IWETHEY Banner

Welcome to IWETHEY!

New Why that should not be in the DB:
Before I start, some background: I've written entire rules-based automated travel booking systems in stored procedures. I've worked at places that have over a million lines of PL/SQL code. The very forums we're using incorporate stored procedures to manage the complex insertion logic of a new post using nested set trees. I'm not trying to pull argument from authority, but just understand that I've seen a few patterns and anti-patterns, and I've done things with databases that would give Bryce wet dreams for a month.

Also, a caveat: I work on big, complex systems. Some of the things I'm going to mention may not apply to smaller systems. However, every system starts small. It doesn't take a lot of effort to future-proof while it's small and if you do you may reap a lot of benefit later on, or indeed be able to do things that would be nearly impossible from an organizational or resource standpoint once the system gets big.

There are probably even more arguments for my position but I'm not spending a lot of time on this, believe it or not:

1) Maintenance
2) Traceability
3) Accessibility
4) Testing
5) Scalability
6) Portability
7) Functionality

Maintenance: Presumably you are using a different language, such as PHP, to do what you're calling 3rd and 4th order business logic. As soon as you add a second language you're multiplying your maintenance costs. You need a new test framework, you need to add more functionality to your build system, you need resources that can program in PL/SQL or TransactSQL or whatever, you need more debuggers and tools, and so on. Multiple programming environments increase your technical and cognitive surface.

Traceability: Where is the logic executing? Because you've added a whole new environment that can run logic, but not *all* the logic -- remember that 3rd and 4th order logic is somewhere else -- you don't immediately know where a particular piece of logic may be located. Let's say you have that bit that calculates "the price for a product based on cost, overhead, contract price, etc." in a stored procedure. Now, one of your clients requires that you calculate their price based on the results of a web service call. You had better not be trying to do that from within a stored procedure (I will kill you for every time you mention UTL_HTTP, fair warning), so now you need to move that logic out of the stored procedure and into your app tier. Maybe you have other logic for other clients as well... do you move everyone's out now? Or do you maintain price calculation logic in two different places? The next time you need to make a change, how do you know the price calculation logic is in PHP, and the state tax logic is still in a stored procedure? Or what if the pricing logic needs to look up values, or it now needs to be modifiable by an administrative person or user? If you've got logic in a trigger that is accessing other tables you're asking for trouble. Because triggers are essentially invisible you can end up with chains of them doing multiple things, all within your transaction space. How do you really know what's happening on that insert? Another variation on this point is separation of concerns: when you start using stored procedures and triggers the line between data and logic becomes blurred.

Accessibility: Some databases, like Oracle, let you do an awful lot in their stored procedures. Invariably this functionality is slower, clunkier, and almost guaranteed to cause performance issues as the system grows. Or take logging. You're going to want to have logging in any procedural code you write. I've written a syslog client for PL/SQL just to get a logging solution that would perform well enough to not cause the database heartburn under load. Not recommended.

Testing: Unit tests need to be fast. Very fast. They should run every time you check code into your repository, and they need to be self-contained. If you have logic in a database system, now you need to have a running database to run your tests, which means that tests for this logic automatically get moved to the component (integration) testing phase of your development pipeline. Errors get caught later as a result. And since your code resides in a trigger, you have to scrub your data before and after testing, all of which complicates your test harness.

Scalability: Once you dip into a database's transactional context your costs of execution go up by an order of magnitude at least. If you can do your calculations, constraints, validations, etc. before you have to invoke a remote call with a transaction context and persistence, you're going to scale better. Leave that logic out in the app tier and it can be parallelized, optimized. A database is persistent storage. Treat it like a hard drive, and don't go there unless you absolutely have to. The most performant systems do their work in memory (this can be done in a high availability fashion if required) and write the persistence out lazily.

Portability: IWETHEY is a case in point. I'd love to test it on MySQL, but I can't because the post creation logic is implemented in a stored procedure. If it were out in my app tier then switching to a different persistent store would be as simple as changing my JDBC driver and loading the table schema elsewhere. Or let's say that you decide a few years into the life of the application that parts of it would be better off in a columnar-oriented database like Vertica. Or maybe in something completely different like BigTable. If you are only persisting data with your persistent data store, you can easily swap that piece out.

Functionality: Rules engines. Put your validation, calculation, and constraint logic in a rules engine and you can improve your functionality (and maintenance). Your admins can change the rules without needing a programmer, either directly via the rules themselves or with an administrative GUI. Or not... either way the functionality available to a general purpose language, running outside of a database's execution context, is much greater than the limitations imposed by a special purpose stored procedure language.
Regards,
-scott
Welcome to Rivendell, Mr. Anderson.
New Very interesting. Thanks for the detalied explanation.
     Question on DB "best practices" - (drook) - (31)
         We don't even... - (folkert)
         Design coin flip - (jay)
         dumb datastore is easier to debug imho -NT - (boxley)
         Step away from the gun... - (malraux) - (17)
             Wow, not the response I was expecting - (drook) - (16)
                 Why that should not be in the DB: - (malraux) - (1)
                     Very interesting. Thanks for the detalied explanation. -NT - (Another Scott)
                 I wasn't expecting that trend, either. - (static) - (13)
                     Re: I wasn't expecting that trend, either. - (malraux) - (6)
                         I think I know what the problem is - (drook) - (4)
                             Paging is tricky. - (static)
                             Re: I think I know what the problem is - (malraux) - (2)
                                 Grid views, basically - (drook) - (1)
                                     Re: Grid views -- thoughts - (S1mon_Jester)
                         And ORMs have gotten better, too. - (static)
                     DB does have one key advantage here - (jay) - (5)
                         This -NT - (drook)
                         As scott said - (crazy) - (3)
                             Yes, this is where my preference is going now. - (static) - (2)
                                 There comes the moment when the constraints get dropped - (crazy) - (1)
                                     That's exactly why I want the App to know what to do! :-) -NT - (static)
         Well, it depends - (lincoln) - (9)
             5,000 to 10,000 lines long? - (malraux) - (7)
                 I still win - (crazy) - (5)
                     Let me guess - No comments, too? ;-) -NT - (Another Scott) - (2)
                         Don't recall - (crazy) - (1)
                             I remember looking at it... - (folkert)
                     Hmmm. - (malraux) - (1)
                         :-) -NT - (Another Scott)
                 they were written over a dozen years ago - (lincoln)
             Amateur - (drook)

I WAS HAVING FUN. CAPS EASIER READ.
60 ms