There are many valid ways of looking at this sort of question, and it depends on your overall application architecture.
There's a spectrum of validation, from "data" to "business".
Data validation includes things like "is this primary key field unique?", "is this date actually a date?", "is this field really an integer", "does this field have a value?".
Business validation includes things like "we don't accept new orders at the weekend", "we don't accept orders from customers with a balance lower than $100", "All orders must have at least one product from category XYZ".
You want data validation to happen in the database. Standard SQL supports a lot of this.
Business validation can be in the database, but typically requires non-standard extensions - and even then, is often hard to implement.
The benefit of doing it in the database is that you can support multiple clients and ensure they adhere to the same rules.
The drawback is that it's difficult to deliver, and hard to maintain - and even harder to change. Database changes typically are a big deal, and the changes can ripple through many layers of the application. For instance, allowing a column to be null may change the UI logic. And business validation logic is much more likely to change than core data type validation.
So most architectures have a "business logic" layer - the "model" in model/view/controller, for instance - which applies business logic, and validates it.
Your example, however, is not as clear cut.
The validation you propose is much closer to "data type" than to "business logic". It's hard to imagine a scenario where a week would have more than 7 days.
My preference would be to enforce those rules by unit testing the business logic code, rather than the database. It's easier to verify alongside all the other business logic, and I prefer business logic validation to live in a single place (the API) rather than two places.
I favor not having checks defined both in the table definitions and performed on the application level, to avoid the duplication of the logic and improve maintainability (cf single responsibility principle).
For the field validity stuff, like a number being within a range or so, I tend to position those in the app itself, because the DB is often not capable to validate them all with precision (how to make sure a string is a valid email for example), and having all the checks in app also helps build consistent error messages.
OTOH, the DB is the only place where you can check for some invariants, like unicity of some column, consistency of the foreign/primary key relationship, transaction isolation, atomicity... This is especially true in a concurrent and distributed setting: the DB is the only part of the system that is strongly consistent, and therefore "knows enough" to do some of the checks.
Also, all the work that the DB can do is logic that you don't have to re-implement in your app, and the code of the DB is often battled-tested => you can choose to delegate a lot to your DB in order to keep your application code lean.
Some say that you should not depend too much on vendor-specific features of your DB and de-couple it from your application via some abstract interface, although I often disagree with that: vendor-specific DB features (like specific checks in your example) are usually where most of the added-value of that DB lies, and in 20 years of software engineering I've almost never encountered the mythical situation where a project decided to swap their DB vendor for another (or when they did, they precisely did it to benefit from some specific features => the abstraction was in the way and had to be refactored or removed, without providing any protection nor benefit).
In short:
make sure each piece of logic is defined in only one place overall
let your DB do all it's good at, and try to have a large proportion of your code doing business value-added stuff instead of technical stuff
it's all a trade-off really, just pick a philosophy and accept its down-sides :)
发布评论
评论（2）
有许多有效的方法来研究这种问题，这取决于您的整体应用程序体系结构。
从“数据”到“业务”都有各种各样的验证。
数据验证包括诸如“此主要密钥字段唯一的？”，“这个日期实际上是一个日期？
业务验证包括诸如“我们不接受周末的新订单”之类的东西，我们不接受余额低于100美元的客户的订单，“所有订单都必须具有XYZ类的至少一个产品”。
您希望数据验证在数据库中发生。标准SQL支持很多。
业务验证可以在数据库中，但通常需要非标准扩展 - 即使在那时，通常也很难实现。
在数据库中进行操作的好处是，您可以支持多个客户端并确保它们遵守相同的规则。
缺点是很难交付，并且很难维护 - 甚至更难更改。数据库更改通常很重要，并且更改可以通过应用程序的许多层次旋转。例如，允许列为null可能会更改UI逻辑。与核心数据类型验证相比，业务验证逻辑更有可能更改。
因此，大多数体系结构都具有“业务逻辑”层 - 例如，模型/视图/控制器中的“模型”，它应用了业务逻辑并验证它。
但是，您的示例并不那么明确。
您提出的验证更接近“数据类型”，而不是“业务逻辑”。很难想象一个星期的场景将有超过7天的时间。
我的首选是通过单元测试业务逻辑代码而不是数据库来执行这些规则。与所有其他业务逻辑一起进行验证更容易，我更喜欢业务逻辑验证，而不是一个地方（API）而不是两个地方。
There are many valid ways of looking at this sort of question, and it depends on your overall application architecture.
There's a spectrum of validation, from "data" to "business".
Data validation includes things like "is this primary key field unique?", "is this date actually a date?", "is this field really an integer", "does this field have a value?".
Business validation includes things like "we don't accept new orders at the weekend", "we don't accept orders from customers with a balance lower than $100", "All orders must have at least one product from category XYZ".
You want data validation to happen in the database. Standard SQL supports a lot of this.
Business validation can be in the database, but typically requires non-standard extensions - and even then, is often hard to implement.
The benefit of doing it in the database is that you can support multiple clients and ensure they adhere to the same rules.
The drawback is that it's difficult to deliver, and hard to maintain - and even harder to change. Database changes typically are a big deal, and the changes can ripple through many layers of the application. For instance, allowing a column to be null may change the UI logic. And business validation logic is much more likely to change than core data type validation.
So most architectures have a "business logic" layer - the "model" in model/view/controller, for instance - which applies business logic, and validates it.
Your example, however, is not as clear cut.
The validation you propose is much closer to "data type" than to "business logic". It's hard to imagine a scenario where a week would have more than 7 days.
My preference would be to enforce those rules by unit testing the business logic code, rather than the database. It's easier to verify alongside all the other business logic, and I prefer business logic validation to live in a single place (the API) rather than two places.
我想这是意见问题和权衡。
我赞成没有在表定义中定义的检查并在应用程序级别上执行的检查，以避免重复逻辑并提高可维护性（CF单个责任原则）。
对于字段有效性的内容，例如一个数字在范围内左右，我倾向于将这些数字定位在应用程序本身中，因为DB通常无法以精度验证它们（如何确保字符串是有效的电子邮件例如），并且在应用程序中使用所有检查也有助于构建一致的错误消息。
OTOH，DB是您唯一可以检查某些不变性的地方，例如某些列的Unicity，外国/主密钥关系的一致性，交易隔离，原子质...在并发和分布式设置中尤其如此：数据库是系统中唯一一致的部分，因此“知道足够”可以进行一些检查。
另外，数据库所能完成的所有工作都是您不必重新实现应用程序的逻辑，并且DB的代码通常经常进行测试=＆gt;您可以选择将大量委派给数据库，以保持应用程序代码精益。
有人说，您不应过多地依赖DB的特定特定功能，并通过某些抽象界面将其从应用程序中脱离式功能，尽管我经常不同意：供应商特定的DB功能（例如示例中的特定检查）通常是该数据库所在的大部分添加值，而在20年的软件工程中，我几乎从未遇到过神话般的情况，在这种情况下，一个项目决定将其DB供应商交换为另一个（或他们做到了，他们确实从某些特定功能中受益=＆gt；
简而言之：
I guess it's a matter of opinion and trade off.
I favor not having checks defined both in the table definitions and performed on the application level, to avoid the duplication of the logic and improve maintainability (cf single responsibility principle).
For the field validity stuff, like a number being within a range or so, I tend to position those in the app itself, because the DB is often not capable to validate them all with precision (how to make sure a string is a valid email for example), and having all the checks in app also helps build consistent error messages.
OTOH, the DB is the only place where you can check for some invariants, like unicity of some column, consistency of the foreign/primary key relationship, transaction isolation, atomicity... This is especially true in a concurrent and distributed setting: the DB is the only part of the system that is strongly consistent, and therefore "knows enough" to do some of the checks.
Also, all the work that the DB can do is logic that you don't have to re-implement in your app, and the code of the DB is often battled-tested => you can choose to delegate a lot to your DB in order to keep your application code lean.
Some say that you should not depend too much on vendor-specific features of your DB and de-couple it from your application via some abstract interface, although I often disagree with that: vendor-specific DB features (like specific checks in your example) are usually where most of the added-value of that DB lies, and in 20 years of software engineering I've almost never encountered the mythical situation where a project decided to swap their DB vendor for another (or when they did, they precisely did it to benefit from some specific features => the abstraction was in the way and had to be refactored or removed, without providing any protection nor benefit).
In short: