database 数据库与代码中的业务逻辑？

Business Logic in Database versus Code?

提问by senfo

As a software engineer, I have a strong bias towards writing business logic in the application layer, while typically relying on the database for little more than CRUD (Create Retrieve Update and Delete) operations. On the other hand, I have run across applications (typically older ones) where a large amount of the business logic was written in stored procedures, so there are people out there that prefer to write business logic in the database layer.

作为一名软件工程师，我非常倾向于在应用程序层编写业务逻辑，而通常只依赖数据库进行 CRUD（创建检索更新和删除）操作。另一方面，我遇到过在存储过程中编写大量业务逻辑的应用程序（通常是较旧的应用程序），因此有些人更喜欢在数据库层中编写业务逻辑。

For the people that have and/or enjoy written/writing business logic in a stored procedure, what were/are your reasons for using this method?

对于已经和/或喜欢在存储过程中编写/编写业务逻辑的人，您使用这种方法的原因是什么？

采纳答案by Matt Wrock

I try to seriously limit my business logic in the DB to only procs that have to do alot of querying and updating to perform a single application operation. Some may argue that even that should be in the app, but I like to keep the IO down if I can.

我尝试将我在数据库中的业务逻辑严格限制为仅需要进行大量查询和更新才能执行单个应用程序操作的过程。有些人可能会争辩说，即使这应该在应用程序中，但如果可以的话，我喜欢保持 IO。

Databases are great for CRUD but if they get bloated with logic:

数据库非常适合 CRUD，但如果它们因逻辑而变得臃肿：

1. It becomes confusing where the logic is,
2. Typically databases are a silo and do not scale horizontally nearly as well as the app servers.
3. t_sql/PLsql is hard to read and procedural in nature
4. You forfeit all of the benefits of OOAD.

1. 逻辑在哪里变得混乱，
2. 通常数据库是一个孤岛，几乎不像应用服务器那样水平扩展。
3. t_sql/PLsql 本质上难以阅读和程序化
4. 您将失去 OOAD 的所有好处。

回答by Matt Wrock

To the maximum extent possible, keep your business logic in the environment that is the most testable and debuggable. There are some valid reasons for storing business logic in the database in other people's existing answers, but they are almost always far outweighed by this.

尽可能将您的业务逻辑保持在最可测试和可调试的环境中。在其他人现有的答案中将业务逻辑存储在数据库中是有一些正当理由的，但它们几乎总是远远超过这一点。

回答by HLGEM

Limiting the business logic to the application layer is short-sighted at best. Experienced professional database designers rarely allow it on their systems. Database need to have constraints and triggers and stored procs to help define how the data from any source will go into it.

将业务逻辑限制在应用层是一种短视。经验丰富的专业数据库设计人员很少允许在他们的系统上使用它。数据库需要有约束和触发器以及存储过程来帮助定义来自任何来源的数据将如何进入它。

If the database is to maintain its integrity and to ensure that all sources of new data or data changes follow the rules, the database is the place to put the required logic. Putting it the application layer is a data nightmare waiting to happen. Databases do not get information just from one application. Business logic in the application is often unintentionally bypassed by imports (assume you got a new customer who wanted their old historical data imported to your system or a large number of target records, no one is going to enter a million possible targets through the interface, it will happen in an import.) It is also bypassed by changes made through the query window to fix one-time issues (things like increasing the price of all products by 10%). If you have application layer logic that should have been applied to the data change, it won't be. Now it's ok to put it in the application layer as well, no sense sending bad data to the database and wasting network bandwidth, but to fail to put it in the database will sooner or later cause data problems.

如果数据库要保持其完整性并确保所有新数据或数据更改的来源都遵循规则，那么数据库就是放置所需逻辑的地方。把它放在应用层是一个等待发生的数据噩梦。数据库不仅仅从一个应用程序中获取信息。应用程序中的业务逻辑经常会被导入无意中绕过（假设您有一个新客户希望将他们的旧历史数据导入您的系统或大量目标记录，没有人会通过界面输入一百万个可能的目标，它会在导入中发生。）它也被通过查询窗口所做的更改绕过以解决一次性问题（例如将所有产品的价格提高 10%）。如果您有应用层逻辑应该应用于数据更改，不会。现在放到应用层也可以了，把不好的数据发到数据库里浪费网络带宽是没有意义的，但是不放到数据库中迟早会导致数据问题。

Another reason to keep all of this in the database has to to with the possibility of users committing fraud. If you put all your logic in the application layer, then you must grant the users access directly to the tables. If you encapsulate all your logic in stored procs, they can be limited to doing only what the stored procs allow and not anything else. I would not consider allowing any kind of access by users to a database that stores financial records or personal information (such as health records) as I would not allow anyone except a couple of dbas to directly access the production records in any way shape or form. More fraud is committed than many developers realize and almost none of them consider the possibility in their design.

将所有这些都保存在数据库中的另一个原因与用户进行欺诈的可能性有关。如果您将所有逻辑都放在应用程序层，那么您必须授予用户直接访问表的权限。如果您将所有逻辑封装在存储过程中，则它们只能执行存储过程允许的操作，而不能执行其他任何操作。我不会考虑允许用户以任何形式访问存储财务记录或个人信息（例如健康记录）的数据库，因为我不允许除几个 dbas 之外的任何人以任何形式或形式直接访问生产记录. 发生的欺诈比许多开发人员意识到的要多，而且几乎没有人在他们的设计中考虑过这种可能性。

If you need to import large amount of data, going through a data access layer could slow down the import to a crawl becasue it doesn't take advanatge of the set-based operations that databases are designed to handle.

如果您需要导入大量数据，通过数据访问层可能会减慢导入到爬网的速度，因为它不会利用数据库旨在处理的基于集合的操作。

回答by Erwin Smout

Your usage of the term "business logic" is rather vague.

您对“业务逻辑”一词的使用相当含糊。

It can be interpreted to mean to include the enforcement of constraints on the data (aka 'business rules'). Enforcement of these unequivocally belongs in the dbms, period.

它可以被解释为包括对数据的约束（又名“业务规则”）的实施。执行这些明确属于 dbms 期间。

It can also be interpreted to mean to include things like "if a new customer arrives, then within a week we send him a welcome letter." Trying to push stuff like this in the data layer is probably a big mistake. In such cases, the driver for "create a new welcome letter" should probably be the application that also triggers the new customer row insertion. Imagine every new database row insertion triggering a new welcome letter, and then suddenly we take over another company and we must integrate that company's customers in our own database ... Ouch.

它也可以解释为包括诸如“如果有新客户到来，我们会在一周内向他发送一封欢迎信”之类的内容。试图在数据层推送这样的东西可能是一个很大的错误。在这种情况下，“创建新的欢迎信”的驱动程序可能应该是也触发新客户行插入的应用程序。想象一下，每个新的数据库行插入都会触发一封新的欢迎信，然后突然我们接管了另一家公司，我们必须将那家公司的客户集成到我们自己的数据库中……哎呀。

回答by Joe

We do a lot of processing in the DB tier, where appropriate. There's a lot of operations you wouldn't want to pull back large datasets to the app tier to do analysis on. It's also an easier deployment for us -- a single point vs. updating applications at all install points. But a lot depends on your application and what it does; there's no single good answer here.

在适当的情况下，我们在 DB 层进行了大量处理。有很多操作您不希望将大型数据集拉回应用层进行分析。这对我们来说也是一个更简单的部署——单点与在所有安装点更新应用程序。但很大程度上取决于您的应用程序及其功能；这里没有一个好的答案。

回答by n8wrl

On a couple of ocassions I have put 'logic' in sprocs because the CRUD might be happening in more than one place. By 'logic' I would have to say it is not really business logic but more 'integrity logic'. It might be the same - some cleanup might be necessary if something gets deleted or updated in a certain way, and if that delete or update could happen from more than one tool with different code-bases it made sense to put it in the proc they all used.

有几次我将“逻辑”放在 sproc 中，因为 CRUD 可能发生在不止一个地方。通过“逻辑”，我不得不说它不是真正的业务逻辑，而是更多的“完整性逻辑”。它可能是相同的 - 如果某些内容以某种方式被删除或更新，则可能需要进行一些清理，并且如果该删除或更新可能来自多个具有不同代码库的工具，那么将其放入 proc 中是有意义的。都用过。

In addition, sometimes the 'business logic line' is pretty blurry. Take reports for example - they may rely on stored procedures or views that encapsulate 'smarts' about what the schema means to the business. How often have you seen CASE statements and the like that 'do things' based on column values or other critieria? Could be construed as business logic and yet it probably does belong in the DB where it can be optimized, etc.

此外，有时“业务逻辑线”非常模糊。以报告为例——它们可能依赖于封装了关于模式对业务意味着什么的“智能”的存储过程或视图。您是否经常看到 CASE 语句等根据列值或其他标准“做事”？可以解释为业务逻辑，但它可能确实属于可以优化的数据库等。

回答by Simon Oliver

I'd say if 'business-logic' means application flow, user control, timed operations and generally 'doing-business-stuff' then it should be in the application layer. But if it means making sure that no matter how you dig around in the data, it always makes sense and is a sensible, non-self-conflicting whole, then the checks to enforce those rules go in the DB, absolutely, no questions. There are always many ways to push data into the DB and manipulate it once its there. Not all those ways have 'business-logic' built in to them. You will find a SQL session into a DB through a DOS window on a support call at 3am is very liberal in what it allows for example! If the logic isn't in the DB to make sure that ALL data changes make sense, you can bet for sure that the data will get very, very screwed up over time. And since a system is only as valuable as the data it holds, that makes for a much lower return on investment.

我想说的是，如果“业务逻辑”是指应用程序流、用户控制、定时操作以及通常的“做生意”，那么它应该在应用程序层。但是，如果这意味着确保无论你如何挖掘数据，它总是有意义的，并且是一个合理的、不自相矛盾的整体，那么执行这些规则的检查就会进入数据库，绝对没有问题。总是有很多方法可以将数据推送到数据库中并在数据到达后对其进行操作。并非所有这些方式都内置了“业务逻辑”。例如，您会发现在凌晨 3 点的支持电话中，通过 DOS 窗口将 SQL 会话导入到数据库中是非常自由的！如果逻辑不在数据库中以确保所有数据更改都有意义，那么您可以肯定，随着时间的推移，数据会变得非常非常混乱。

回答by dpbradley

Two good reasons for putting the business logic in the database are:

将业务逻辑放入数据库的两个很好的理由是：

• It secures your logic and data against additional applications that may access the database that don't implement similar logic.
• Database designs usually outlive the application layer and it reduces the work necessary when you move to new technologies on the client side.

• 它可以保护您的逻辑和数据免受可能访问未实现类似逻辑的数据库的其他应用程序的影响。
• 数据库设计通常比应用程序层更长寿，当您转向客户端的新技术时，它会减少必要的工作。

回答by Gratzy

You often find business logic at the database layer because it can often be faster to make a change and deploy. I think often the best intentions are not to put the logic there but because of the ease of deployment it ends up there.

您经常会在数据库层找到业务逻辑，因为通常可以更快地进行更改和部署。我认为通常最好的意图不是将逻辑放在那里，而是因为易于部署，它最终会放在那里。

回答by Yaniv C

I work for a financial type company where certain rules are applied by states, and these rules and their calculations are subject to change almost daily if not surely weekly. That being the case, it made more sense to move parts of the logic dealing with calculations to the database; where a change can be tested and applied without having to recompile and redistibute an application, which is impossible to do daily without disrupting business. The stored proc is tested, approved, applied and the end user is none the wiser. With the move to web based applications, the reliance on moving the logic to the database is less but still present. Even web apps (depending on the language) must be compiled and published to the site which could cause downtime.

我在一家金融类公司工作，该公司的某些规则由各州实施，这些规则及其计算几乎每天都会发生变化，即使不是每周一次。既然如此，将处理计算的部分逻辑移到数据库中更有意义；可以测试和应用更改而无需重新编译和重新分发应用程序，这是不可能在不中断业务的情况下每天进行的。存储的过程经过测试、批准、应用，最终用户并不知情。随着转向基于 Web 的应用程序，对将逻辑移动到数据库的依赖减少了，但仍然存在。即使是网络应用程序（取决于语言）也必须编译并发布到网站，这可能会导致停机。