how much work should we do in the database? Ok I'm really confused as to exactly how much "work" should be done IN the database, and how much work had to be done instead at the application level?
在数据库中我们应该做多少工作?好吧，我真搞不懂到底应该在数据库中做多少“工作”，而在应用程序级别上要做多少工作?
I mean I'm not talking about obvious stuff like we should convert strings into SHA2 hashes at the application level instead of the database level..
我的意思是，我并不是说我们应该在应用程序级别而不是数据库级别将字符串转换成SHA2散列。
But rather stuff that are more blur, including, but not limited to "should we retrieve the data for 4 column and do a uppercase/concatenation at the application level, or should we do those stuff at the database level and send the calculated result to the application level?
而是更模糊的东西，包括但不限于“我们应该检索4列的数据并在应用程序级别执行大写/连接，还是应该在数据库级别执行这些操作并将计算结果发送到应用程序级别?”
And if you could list any more other examples it would be great.
如果你能举出更多的例子那就太好了。
4 个解决方案
#1
4
It really depends on what you need.
I like to do my business logic in the database, other people are religously against that.
这取决于你需要什么。我喜欢在数据库中执行我的业务逻辑，其他人则非常反对。
You can use triggers and stored procedures/functions in SQL.
您可以在SQL中使用触发器和存储过程/函数。
Links for MySQL:
http://dev.mysql.com/doc/refman/5.5/en/triggers.html
http://www.mysqltutorial.org/introduction-to-sql-stored-procedures.aspx
http://dev.mysql.com/doc/refman/5.5/en/stored-routines.html
MySQL的链接:http://dev.mysql.com/doc/refman/5.5/en/triggers.html
My reasons for doing business logic in triggers and stored proces
我在触发器和存储过程中执行业务逻辑的原因。
Note that I'm not talking about bending the database structure towards the business logic, I'm talking about putting the business logic in triggers and stored procedures.
注意，我说的不是将数据库结构弯曲成业务逻辑，而是将业务逻辑放入触发器和存储过程中。
- It centralizes your logic, the database is a central place, everything has to go through it. If you have multiple insert/update/delete points in your app (or you have multiple apps) you'll need to do the checks multiple times, if you do it in the database you only have to do the checks in one place.
- 它集中了你的逻辑，数据库是一个中心，所有东西都要经过它。如果您的应用程序中有多个插入/更新/删除点(或者您有多个应用程序)，那么您将需要多次进行检查，如果您在数据库中进行检查，那么您只需在一个位置进行检查。
- It simplifies the application e.g., you can just add a member, the database will figure out if the member is already known and take the appopriate action.
- 它简化了应用程序，例如，您只需添加一个成员，数据库就会知道该成员是否已经知道并采取appopriate操作。
- It hides the internals of your database from the application, if you do all your logic in the application you will need intricate knowledge of your database in the application. If you use database code (triggers/procs) to hide that, you don't need to know every database detail in your app.
- 它向应用程序隐藏数据库的内部结构，如果在应用程序中执行所有逻辑，那么在应用程序中就需要对数据库有复杂的了解。如果您使用数据库代码(触发器/procs)来隐藏它，则不需要知道应用程序中的每个数据库细节。
- It makes it easier to restucture your database If you have the logic in your database, you can just change a tablelayout, replace the old table with a blackhole table, put a trigger on that and let the trigger do the updates to the new table, your app does not even need to know the database has changed, this allows legacy apps to keep working unchanged, whilst new apps can use the improved database layout.
- 它更易于租赁数据库如果你有逻辑在数据库中,你可以改变一个tablelayout,取代旧的表与黑洞表,把一个触发器,让引发对新表的更新,甚至你的应用程序不需要知道数据库已经改变,这允许遗留应用程序继续工作不变,而新的应用程序可以使用改进的数据库布局。
- Some things are easier in SQL
- 在SQL中，有些事情更容易
- Some things work faster in SQL
- 有些东西在SQL中工作得更快
- I don't like to use (lots of and/or complicated) SQL code in my application, I like to put SQL code in a stored procedure/function and try to only put simple queries in my application code, that way I can just write code that explains what I mean in my application and let the database layer do the heavy lifting.
- 我不喜欢使用(很多和/或复杂)的SQL代码在我的应用程序中,我喜欢把SQL代码放在一个存储过程/函数,只把简单的查询在我的应用程序代码,这样我可以编写代码来解释我的意思在我的应用程序,让数据库层承重。
Some people disagree strongly with this, but this approach works well for me and has simplified debugging and maintenance of my applications a lot.
有些人强烈反对这一点，但是这种方法对我来说很有效，并且简化了我的应用程序的调试和维护。
#2
0
Generally, its a good practice to expect only "Data" from the Database. Its upto Application(s), to apply Business/Domain Logic and make sense of the data retrieved. Its highly recommended to do the following things in the Application Layer:
一般来说，最好只从数据库中获取“数据”。应用业务/域逻辑并理解所检索到的数据，这是应用程序的结果。强烈建议在应用层做以下事情:
1) Formatting Date 2) Applying Math functions, such as interpolation/extrapolation, etc 3) Dynamic sorting (based on columns)
1)格式化日期2)应用数学函数，如插补/外推等3)动态排序(基于列)
However, situations sometime warrant few things to be done at the database level.
然而，在某些情况下，数据库级几乎不需要做什么事情。
#3
0
In my opinion application should use data and database should provide them and that should be clear separation of concerns. So database gives records sorted, ordered and filtered according to requested conditions but it is up to application to apply some business logic to that records and "convert" them into something meaningful to the user.
在我看来，应用程序应该使用数据，数据库应该提供数据，这应该是明确的关注点分离。因此，数据库根据所请求的条件对记录进行排序、排序和筛选，但应用程序将一些业务逻辑应用到该记录，并将它们“转换”为对用户有意义的内容。
For example, in my previous company we worked on big application for work time calculations. One of obvious functionalities in this kind of application is tracking vacation days of employees - how many days employee has per year, how many he used, how many left, etc. Basically we could write some triggers and procedures that would update those columns automatically. So when employee had his vacation days approved amount of days he applied for is taken from his "vacation pool" and added to "vacation days used". Pretty easy stuff but we decided to make it explicit on application level and boy, very soon we were happy we did it that way. Application had to be labor law compliant and it quickly turned out that not for all employees vacation days are calculated equally and sometimes vacation day can be not so vacation day at all but that is beside the point. Had we put this "easy" operation in database we had to version our database with every little change to a vacation days related logic and that would lead us straight to hell in customer support field due to a fact that it was possible to update only application without a need to update database (except clear "breakthrough" moments where database structure was changed of course).
例如，在我之前的公司里，我们做了一个关于工作时间计算的大应用。这类应用程序的一个明显功能是跟踪员工的休假日——员工每年有多少天，他使用了多少天，还剩下多少天等等。基本上，我们可以编写一些触发器和过程来自动更新这些列。因此，当员工的休假天数得到批准时，他申请的天数就会从他的“度假池”中扣除，并加入“休假日”。非常简单，但是我们决定在应用程序级别上明确说明，很快我们就很高兴我们这么做了。申请必须符合劳动法，很快就发现并不是所有员工的假期都是平等计算的，有时候休假日根本就不是休假日，但那不是重点。如果我们把这个“简单”操作在数据库中,我们不得不与每一个小变化我们的数据库版本假期相关的逻辑,将直接导致我们地狱在客户支持领域由于事实可以只更新应用程序,而不需要更新数据库(除了明确“突破”的时刻,当然改变了数据库结构)。
#4
0
In my experience I've found that many applications start with a straight-forward set of tables and then and handful of stored procedures to provide basic functionality. This works very well; it usually yields high performance and is simple to understand, it also mitigates any need for a complex middle-tier.
在我的经验中，我发现许多应用程序都是从一组简单的表开始，然后是少量的存储过程来提供基本的功能。这种方法可以很好地工作;它通常会产生很高的性能，并且易于理解，还可以减少对复杂中间层的任何需求。
However, applications grow. It's not unusual to see large data-driven applications with thousands of stored procedures. Throw triggers into the mix and you have an application which, for anybody other than the original developers (if they're still working on it), is very difficult to maintain.
然而,应用程序增长。看到拥有数千个存储过程的大型数据驱动应用程序并不少见。将触发器混合在一起，您就有了一个应用程序，对于除原始开发人员之外的任何人(如果他们仍在开发它)，都很难维护。
I will put a word in for applications which place most logic in the database - they can work well when you have some good database developers and/or you have a legacy schema which cannot be changed. The reason I say this is that ORMs take much of the pain out of this part of application development when you let them control the schema (if not, you often need to do a lot of fiddling to get it working).
我将为在数据库中放置大部分逻辑的应用程序加上一句话——当您有一些优秀的数据库开发人员和/或您有一个不能更改的遗留模式时，它们可以很好地工作。我这么说的原因是，当您让orm控制模式时，orm从应用程序开发的这一部分中获得了很多痛苦(如果不是，您通常需要做大量的修改才能使其工作)。
If I was designing a new application then I would usually opt for a schema which is dictated by my application domain (the design of which will be in code). I would normally let an ORM handle the mapping between the objects and the database. I would treat stored procedures as exceptions to the rule when it came to data access (reporting can be much easier in sprocs than trying to coax an ORM into producing a complex output efficiently).
如果我正在设计一个新的应用程序，那么我通常会选择由我的应用程序域决定的模式(其设计将在代码中)。我通常会让ORM处理对象和数据库之间的映射。在访问数据访问时，我将把存储过程看作是规则的例外(在sprocs中，报告比试图使一个ORM高效地生成一个复杂的输出要容易得多)。
The most important thing to remember though, is that there are no "best practices" when it comes to design. It is up to you the developer to weigh up the pros and cons of each option in the context of your design.
但最重要的是要记住，在设计方面没有“最佳实践”。由开发人员在设计上下文中权衡每个选项的优缺点。
#1
4
It really depends on what you need.
I like to do my business logic in the database, other people are religously against that.
这取决于你需要什么。我喜欢在数据库中执行我的业务逻辑，其他人则非常反对。
You can use triggers and stored procedures/functions in SQL.
您可以在SQL中使用触发器和存储过程/函数。
Links for MySQL:
http://dev.mysql.com/doc/refman/5.5/en/triggers.html
http://www.mysqltutorial.org/introduction-to-sql-stored-procedures.aspx
http://dev.mysql.com/doc/refman/5.5/en/stored-routines.html
MySQL的链接:http://dev.mysql.com/doc/refman/5.5/en/triggers.html
My reasons for doing business logic in triggers and stored proces
我在触发器和存储过程中执行业务逻辑的原因。
Note that I'm not talking about bending the database structure towards the business logic, I'm talking about putting the business logic in triggers and stored procedures.
注意，我说的不是将数据库结构弯曲成业务逻辑，而是将业务逻辑放入触发器和存储过程中。
- It centralizes your logic, the database is a central place, everything has to go through it. If you have multiple insert/update/delete points in your app (or you have multiple apps) you'll need to do the checks multiple times, if you do it in the database you only have to do the checks in one place.
- 它集中了你的逻辑，数据库是一个中心，所有东西都要经过它。如果您的应用程序中有多个插入/更新/删除点(或者您有多个应用程序)，那么您将需要多次进行检查，如果您在数据库中进行检查，那么您只需在一个位置进行检查。
- It simplifies the application e.g., you can just add a member, the database will figure out if the member is already known and take the appopriate action.
- 它简化了应用程序，例如，您只需添加一个成员，数据库就会知道该成员是否已经知道并采取appopriate操作。
- It hides the internals of your database from the application, if you do all your logic in the application you will need intricate knowledge of your database in the application. If you use database code (triggers/procs) to hide that, you don't need to know every database detail in your app.
- 它向应用程序隐藏数据库的内部结构，如果在应用程序中执行所有逻辑，那么在应用程序中就需要对数据库有复杂的了解。如果您使用数据库代码(触发器/procs)来隐藏它，则不需要知道应用程序中的每个数据库细节。
- It makes it easier to restucture your database If you have the logic in your database, you can just change a tablelayout, replace the old table with a blackhole table, put a trigger on that and let the trigger do the updates to the new table, your app does not even need to know the database has changed, this allows legacy apps to keep working unchanged, whilst new apps can use the improved database layout.
- 它更易于租赁数据库如果你有逻辑在数据库中,你可以改变一个tablelayout,取代旧的表与黑洞表,把一个触发器,让引发对新表的更新,甚至你的应用程序不需要知道数据库已经改变,这允许遗留应用程序继续工作不变,而新的应用程序可以使用改进的数据库布局。
- Some things are easier in SQL
- 在SQL中，有些事情更容易
- Some things work faster in SQL
- 有些东西在SQL中工作得更快
- I don't like to use (lots of and/or complicated) SQL code in my application, I like to put SQL code in a stored procedure/function and try to only put simple queries in my application code, that way I can just write code that explains what I mean in my application and let the database layer do the heavy lifting.
- 我不喜欢使用(很多和/或复杂)的SQL代码在我的应用程序中,我喜欢把SQL代码放在一个存储过程/函数,只把简单的查询在我的应用程序代码,这样我可以编写代码来解释我的意思在我的应用程序,让数据库层承重。
Some people disagree strongly with this, but this approach works well for me and has simplified debugging and maintenance of my applications a lot.
有些人强烈反对这一点，但是这种方法对我来说很有效，并且简化了我的应用程序的调试和维护。
#2
0
Generally, its a good practice to expect only "Data" from the Database. Its upto Application(s), to apply Business/Domain Logic and make sense of the data retrieved. Its highly recommended to do the following things in the Application Layer:
一般来说，最好只从数据库中获取“数据”。应用业务/域逻辑并理解所检索到的数据，这是应用程序的结果。强烈建议在应用层做以下事情:
1) Formatting Date 2) Applying Math functions, such as interpolation/extrapolation, etc 3) Dynamic sorting (based on columns)
1)格式化日期2)应用数学函数，如插补/外推等3)动态排序(基于列)
However, situations sometime warrant few things to be done at the database level.
然而，在某些情况下，数据库级几乎不需要做什么事情。
#3
0
In my opinion application should use data and database should provide them and that should be clear separation of concerns. So database gives records sorted, ordered and filtered according to requested conditions but it is up to application to apply some business logic to that records and "convert" them into something meaningful to the user.
在我看来，应用程序应该使用数据，数据库应该提供数据，这应该是明确的关注点分离。因此，数据库根据所请求的条件对记录进行排序、排序和筛选，但应用程序将一些业务逻辑应用到该记录，并将它们“转换”为对用户有意义的内容。
For example, in my previous company we worked on big application for work time calculations. One of obvious functionalities in this kind of application is tracking vacation days of employees - how many days employee has per year, how many he used, how many left, etc. Basically we could write some triggers and procedures that would update those columns automatically. So when employee had his vacation days approved amount of days he applied for is taken from his "vacation pool" and added to "vacation days used". Pretty easy stuff but we decided to make it explicit on application level and boy, very soon we were happy we did it that way. Application had to be labor law compliant and it quickly turned out that not for all employees vacation days are calculated equally and sometimes vacation day can be not so vacation day at all but that is beside the point. Had we put this "easy" operation in database we had to version our database with every little change to a vacation days related logic and that would lead us straight to hell in customer support field due to a fact that it was possible to update only application without a need to update database (except clear "breakthrough" moments where database structure was changed of course).
例如，在我之前的公司里，我们做了一个关于工作时间计算的大应用。这类应用程序的一个明显功能是跟踪员工的休假日——员工每年有多少天，他使用了多少天，还剩下多少天等等。基本上，我们可以编写一些触发器和过程来自动更新这些列。因此，当员工的休假天数得到批准时，他申请的天数就会从他的“度假池”中扣除，并加入“休假日”。非常简单，但是我们决定在应用程序级别上明确说明，很快我们就很高兴我们这么做了。申请必须符合劳动法，很快就发现并不是所有员工的假期都是平等计算的，有时候休假日根本就不是休假日，但那不是重点。如果我们把这个“简单”操作在数据库中,我们不得不与每一个小变化我们的数据库版本假期相关的逻辑,将直接导致我们地狱在客户支持领域由于事实可以只更新应用程序,而不需要更新数据库(除了明确“突破”的时刻,当然改变了数据库结构)。
#4
0
In my experience I've found that many applications start with a straight-forward set of tables and then and handful of stored procedures to provide basic functionality. This works very well; it usually yields high performance and is simple to understand, it also mitigates any need for a complex middle-tier.
在我的经验中，我发现许多应用程序都是从一组简单的表开始，然后是少量的存储过程来提供基本的功能。这种方法可以很好地工作;它通常会产生很高的性能，并且易于理解，还可以减少对复杂中间层的任何需求。
However, applications grow. It's not unusual to see large data-driven applications with thousands of stored procedures. Throw triggers into the mix and you have an application which, for anybody other than the original developers (if they're still working on it), is very difficult to maintain.
然而,应用程序增长。看到拥有数千个存储过程的大型数据驱动应用程序并不少见。将触发器混合在一起，您就有了一个应用程序，对于除原始开发人员之外的任何人(如果他们仍在开发它)，都很难维护。
I will put a word in for applications which place most logic in the database - they can work well when you have some good database developers and/or you have a legacy schema which cannot be changed. The reason I say this is that ORMs take much of the pain out of this part of application development when you let them control the schema (if not, you often need to do a lot of fiddling to get it working).
我将为在数据库中放置大部分逻辑的应用程序加上一句话——当您有一些优秀的数据库开发人员和/或您有一个不能更改的遗留模式时，它们可以很好地工作。我这么说的原因是，当您让orm控制模式时，orm从应用程序开发的这一部分中获得了很多痛苦(如果不是，您通常需要做大量的修改才能使其工作)。
If I was designing a new application then I would usually opt for a schema which is dictated by my application domain (the design of which will be in code). I would normally let an ORM handle the mapping between the objects and the database. I would treat stored procedures as exceptions to the rule when it came to data access (reporting can be much easier in sprocs than trying to coax an ORM into producing a complex output efficiently).
如果我正在设计一个新的应用程序，那么我通常会选择由我的应用程序域决定的模式(其设计将在代码中)。我通常会让ORM处理对象和数据库之间的映射。在访问数据访问时，我将把存储过程看作是规则的例外(在sprocs中，报告比试图使一个ORM高效地生成一个复杂的输出要容易得多)。
The most important thing to remember though, is that there are no "best practices" when it comes to design. It is up to you the developer to weigh up the pros and cons of each option in the context of your design.
但最重要的是要记住，在设计方面没有“最佳实践”。由开发人员在设计上下文中权衡每个选项的优缺点。