它是一个数据库的最佳实践，允许应用程序做CRUD操作总是通过SP？ [英] Is it a best practise for a database to allow apps do CRUD operations always through SPs?

问题描述

我听说过这个原因，我很好奇，如果别人认为这是一个最佳实践/好主意。

一个原因是，直接访问数据库表，并强制应用程序/用户使用SP（存储过程）进行CRUD操作将允许DBA

1. 精细控制



$ b

这意味着开发人员无法在表上写LINQ查询（尽管可能涉及SP的LINQ查询）
这也意味着开发人员必须忘记编译时检查以及在总体控制数据，而主要使用另一种语言（SQL）来处理数据。





我不反对这一点，也不认为这是一个好主意。我只是想知道别人的想法。

更新：如



/ questions / 543274 / is-it-a-a-database-to-a-database-to-allow-apps-do-crud-operations-always-thro / 543578＃543578> Thomas在他的回答 ，我倾向于认为SP是逻辑，并使用SQL中的数据库编程做逻辑是好的，只要它不是业务逻辑。业务逻辑需要分离，编译时检查和集成检查点和什么不是。

解决方案

完全取决于您的环境。





如果你的数据库只支持一个应用程序，并且相当紧密地集成在一起，那么这个问题的答案是真的不是一个编码问题，而是一个业务决策。为了灵活性，最好将逻辑放在应用程序中。在这些情况下，将数据库简单地作为使用通用功能的普通数据存储库处理，可以减少您的工作量并获得灵活性 - 包括供应商，实施，部署等。





如果你正在处理一个公司数据库，那么最好是尽可能地减少安全性。至少应该实现所有适当的约束，如果可能，访问数据应仅通过视图和过程。在这些情况下，应忽略程序员为...



1. 对于企业数据库，资产是有价值的，无效的数据或操作可以有业务 - 威胁性后果。在这些情况下，您的主要关注的是业务，而不是为您的编码器方便的访问。


2. 此类数据库按定义由多个应用程序访问。您需要使用存储proc提供的抽象，以便在应用程序A升级时您可以更改数据库，并且您没有资源来升级应用程序B.


3. 同样，业务的封装SP中的逻辑而不是应用程序代码中的逻辑比在逻辑嵌入应用程序代码中更容易和可靠地允许跨越业务实现这样的逻辑的改变。例如，如果税计算更改了其工作量，并且更稳健，如果计算必须在多个应用程序的一个SP中更改。这里的经验法则是，业务规则应该在与其唯一的数据最接近的点实现 - 因此如果您有专门的应用程序，那么该应用程序的逻辑可以在该应用程序中实现，但逻辑更广泛适用对业务应该在SP中实施。






I've heard reasoning behind this and I was curious if others think of this as a best practise/good idea.

One reasoning is, that putting restrictions on direct access to database tables, and forcing apps/users to use SPs (Stored Procedures) to do CRUD operations will allow DBAs

1. fine grained control to approve/review SPs before moving them to production
2. avoid random queries written by developers which might cause server load/other server issues

This means the developer can not write Linq queries on tables (although Linq queries involving SPs are possible) This also means the developer has to forget about compile-time checks as well as being in total control of the data, rather principally use another language(SQL) to work with data.

I am not objecting to this, neither think this is a great idea. I am just curious to know what others think. What are any other advantages and disadvantages of this approach.

Update: As mentioned by Thomas in his answer, I tend to think that SPs are logic and using 'database programming in SQL' to do logic is okay as long as it's not 'Business logic'. Business logic needs separation, compile-time check and integration checkpoints and what not.

解决方案

Depends entirely on your environment. The answer to the question really isn't a coding problem, but a business decision.

If your database supports just one application, and is reasonably tightly integrated with it, then it's better, for reasons of flexibility, to place your logic inside your application program. Under these circumstances handling the database simply as a plain data repository using common functionality looses you little and gains flexibility - with vendors, implementation, deployment and much else.

On the other hand if your are handling a corporate database then it is highly advisable to screw down the security as far as you can. At the very least all appropriate constraints should enabled, and if possible access to the data should be through views and procedures only. Whining programmers should be ignored in these cases as...

1. With a corporate database the asset is valuable and invalid data or actions can have business-threatening consequences. In these circumstances your primary concern is the business, not how convenient access is for your coders.
2. Such databases are by definition accessed by more than one application. You need to use the abstraction that stored procs offer so the database can be changed when application A is upgraded and you don't have the resource to upgrade application B.
3. Similarly the encapsulation of business logic in SPs rather than in application code allows changes to such logic to be implemented across the business more easily and reliably than if such logic is embedded in application code. For example if a tax calculation changes it's less work, and more robust, if the calculation has to be changed in one SP than multiple applications. The rule of thumb here is that the business rule should be implemented at the closest point to the data where it is unique - so if you have a specialist application then the logic for that app can be implemented in that app, but logic more widely applicable to the business should be implemented in SPs.

这篇关于它是一个数据库的最佳实践，允许应用程序做CRUD操作总是通过SP？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！