数据库设计：选择和限制收藏夹
现在在数据库中，我有一个 Members 表和一个 Products 表，以及一个连接 Favorites 表，该表由来自 Members 的主外键组成。 strong>成员和产品表。我要求对会员可以放入收藏夹的产品数量进行限制，最多为 5 个。
此限制从何而来？它是在数据库（MySQL）中完成的，因此将成为我现有模式的一部分吗？或者这是一个可以用 PHP 之类的东西来完成的编程功能吗？
Right now in a database I have a Members table and a Products table with a joining Favorites table that consists of primary foreign keys from both the Members and Products tables. I have a requirement to place a restriction on amount of products that a member can place in their favorites at 5.
Where can this restriction come from? Is it something done within the database (MySQL) and hence would be part of my existing schema? Or is this a programming function that could be accomplished with something like PHP?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
然而，问题已经得到解答，因为您正在寻求理解......
数据库的想法是，所有此类对数据的限制和约束都放置在数据库本身中（作为一个独立的单元）。数据约束应该在数据库中，而不仅仅是在应用程序中。 ISO/IEC/ANSI SQL 提供了多种类型的约束，用于不同的目的：
外键约束，用于引用完整性（以及性能；开放架构合规性等）
检查约束，用于检查其他列的数据值，并禁止违规
规则约束，禁止超出范围的数据或指定精确的数据值格式
您的规则是经典的简单规则或检查。数据库和数据库设计的正确答案是规则或检查，而不是代码。
这并不是说应用程序不应该检查计数，并避免尝试无效的操作。这只是明智之举。而且这不是重复，而是在更高级别停止无效操作，从而节省资源使用。如果完整性是在外部（由开发人员编写的应用程序代码中）进行管理的，那么数据库中的数据就无法依赖。可以依赖服务器内部实现的规则，它们对所有应用程序或应用程序组件强制执行。
但免费软件 Non-SQL 不具备标准 SQL 的基础知识。没有检查或规则。因此，数据库中数据的完整性完全取决于开发人员：他们的质量、知识、一致性等。MySQL
/PHP 的正确答案是代码。在尝试插入的每个位置。
The question has been answered, however, since you are seeking understanding ...
The idea with Databases is that all such such limits and Constraints on data are placed in the Database itself (as a self-contained unit). Data Constraints should be in the Database, not only in the app. ISO/IEC/ANSI SQL provide several types of Constraints, for different purposes:
FOREIGN KEY Constraints, for Referential Integrity (as well as performance; Open Architecture compliance, etc)
CHECK Constraints, to check against data values of other columns, and disallow violations
RULE Constraints, to disallow data that is out-of-range or specify exact data value formats
Yours is a classic simple RULE or CHECK. And the correct answer for Database and Database Design is a RULE or CHECK, not code.
That is not to say that the app should not check the count, and avoid attempting an invalid action. That is just good sense. And it is not a repetition, it is stopping invalid actions at a higher level, which saves resource use. And data in the Db cannot be relied upon, if the integrity is managed outside, in app code, written by developers. The rules implemented inside the server can be relied upon, they are enforced for all apps or app components.
But the freeware Non-SQLs do not have the basics of Standard-SQL. No Checks or Rules. Therefore the integrity of data in the database relies solely on the developer: their quality, knowledge, consistency, etc.
And the correct answer for MySQL/PHP is code. In every location that attempts that insert.
您可以在 PHP 中执行此操作。
只需在插入之前执行
SELECT COUNT(*) FROM Members_products WHERE member_id = 3即可。You would do this in PHP.
Just do a
SELECT COUNT(*) FROM members_products WHERE member_id = 3before inserting.