Database Design: Selecting and limiting Favorites

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 with开发者_StackOverflow社区in 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?

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.

You would do this in PHP.

Just do a SELECT COUNT(*) FROM members_products WHERE member_id = 3 before inserting.