Business logic / Rules embedded in SQL DB

[PeterG]

I am not sure if this is the forum for this topic but here goes.
While I was planing the design for a client service management system, This adea dawned on me.
Why not have a large portion of the business logic embedded in the SQL database. So I am reaching out to you guys for advice. Please bare in mind that while pondering on this, the Idea is not to put yourself in the shoes of a large corporation but small startup small to medium enterprise. I am sure the the larger companies can afford to employ SQL DBA's with the skillset and many years of experience to design a system to achieve this. I would really like for a sort of pros and cons type of response with an explanation why it is better or not, to have a large portion of the business logic in SQL database. My knowledge of sql scripting is minimal and this is already one reason why I would be hesitant to go this route.

Have fun.

Peter G

[Mikey]

The only way I can think of to even begin to attempt to do this is via triggers, and the short answer is that the process is difficult and error-prone, and would still require a lot of work on the client side to handle the output from the trigger.

Triggers are important and very useful, and can be used to implement some data functionality, but I think you will find that it is easier to embed most business logic somewhere besides in triggers.

[MaxV]

You can't create a DB for a company without modelling it to that company business logic.

[PeterG]

Hi guys
This is what I could find out with a bit of googling.
That there is no hard and fast rule on how to optimally do things in software and that it is impossible to embed all the Business logic in SQL DB but one can try to embed a large portion of your business logic in Database for the reasons listed in the pros.
The pros for embedding most of the Business logic in SQL Database are:
- Considering the cost to company for a DBA vs a programmer the case is for more Business logic in the Database
- The centralisation of the Business Logic
- Sql databases are less prone to technology migration or big refactorings
- No rework on application technology migration (e.g.: .NET to Java, Perl to Python, etc).

Here are some of the cons:
- More difficult (if possible at all) code reuse through libraries.
- A DBA will be required for performance tuning.
- Your developers will have to be very well versed in your particular SQL dialect(T-SQL, Pl/SQL, etc.
- It is harder to code SQL when covering higher level concepts that aren't really related to data objectively.

In most cases, stored procedures are only needed in some cases. For instance, if you have a report to generate where you need to do a lot of conditional processing across a couple of big tables, it would make a better case for having business logic in SQL DB.

Please feel free to submit any points of view for or against and correct me on any of my errors made on wrong conclusions

Best
Peter G

[bogs]

I don't really have a knife in this kind of steak, but am curious as to what you mean by business logic/rules (shows you just how little I know of what your discussing heh).

I've heard the term before, but it seems to mean something different to everyone.

[PeterG]

Hi Bogs
My apologies for only responding now to your post.
Was out of action for a while.
Just want to say I am no expert in SQL.
This is what i managed to get as an example of a business rule in triggers

Some data integrity rules are easy to express with ordinary constraints. We have trivial things like:
NOT NULL constraints Unique constraints Foreign keys
Check constraints
All of these things model your business rules in very simple ways.
In more complex situations, triggers can become
very useful in enforcing data integrity across several rows within the table, or even across several tables.
While validation can obviously take place in other application layers as well (in the UI for immediate user feedback, in the service layer, in case other systems need to be checked, too), keeping data integrity close to the actual data makes total sense – when it doesn’t hurt performance too much.
For example, in a banking application, where you want to ensure that a CUSTOMER will not perform any PAYMENTS beyond their overall 
CREDIT_LIMIT , nor beyond each ACCOUNTS ’ CREDIT_LIMIT :
Using a database trigger, you could check on each INSERT of a PAYMENT , or on each UPDATE of a
PAYMENT ’s AMOUNT whether the following query yields a non (zero, zero) result for CUSTOMER_CREDIT_LIMIT_EXCEEDED or ACCOUNT_CREDIT_LIMIT_EXCEEDED:

Have not tried this myself yet. ....using triggers that is

Peter G

[bogs]

Thanks for the explanation, no problem on the timing.

With your explanation of what your trying to do, and as it pertains to small and medium sized businesses (not, for instance, Comcast), and what your trying to check, I don't think I'd go the route of sticking it into a db at all. Instead, I'd isolate one stack with a single or multiple cards, a simple table field, or even a set of fields, and insert the rules into that.

Mind you, this is a completely irrelevant discussion if we are talking 10's of thousands of rules.

Not being a part of the application, it could be made to be self saving and efficient for the programmer to access without a lot of db knowledge, similar to the simpler address book applications. From all I've read, Lc is able to handle several thousand records in this manner very quickly (small and medium businesses aren't likely to need more than a few hundred to a thousand variations). More to the point, you could code the lookup in such a way that multiple customers would fall into groupings, making the total amount needing to be stored/retrieved even smaller.

Pros from my point of view, its local instead of networked lookup (although you could take that single stack and access it from a network share if that is more desirable and/or have more than one terminal accessing it, but you'd still have to make sure ONLY one person can update/change it), changes are programmatically written, not requiring a dba, speed and size should both be excellent, no one will be looking at it directly (aside from you), so it doesn't require a data-grid or anything near that complicated and it doesn't have to be pretty, just functional.

cons to this type of approach - you'd have to code your own lookup/ update/ insert routines (not much of a con imho and probably less lines than the equivalent in `sql_x`. You'd have to ensure your own data integrity rules.
...As well, there are probably TONS more dba's around than Lc programmers if updating is required later (could be a pro though, ensuring future calls more likely go to you).

Off the top of my head (not having woken up yet completely), thats what I come up with.