5 ms·

You can make a mess out of any development system, and a knee-jerk reaction to throw out RDBMS guarantees is as sensible as ignoring Javascript because legacy j

You can make a mess out of any development system, and a knee-jerk reaction to throw out RDBMS guarantees is as sensible as ignoring Javascript because legacy jQuery made the UI really hard to understand and debug. I've worked on some large apps and almost always had separation of concerns - the database is ultimately responsible for data integrity. It will validate foreign keys, data ranges and types, but does not implement write business logic. This takes advantage of the amazing work put in to query planners but avoids most of the magic that triggers cause.

jstewartmobile 9y ago
Where did I say throw out RDBMS? MySQL does fk/pk restriction, transactions, field enforcement (contrary to article), and all of the other good ACID stuff just fine. I'm complaining about things like PL/SQL letting you send an email inside a trigger (http://www.orafaq.com/wiki/Send_mail_from_PL/SQL http://www.orafaq.com/wiki/Send_mail_from_PL/SQL), or Postgres's XML processing features. I don't think that belongs in the DB. It's easy to say "don't do that" to a small team, but when you get to the corporate shops that do Oracle, that becomes a much harder thing to enforce.
- snuxoll 9y ago
  There can be more to data integrity than foreign keys and transactions. We have an application to track and distribute work to employees, there can only be one active work item per item type on an account - we use MSSQL for this and originally enforced this with a CHECK constraint, which ultimately failed, we switched to a partial index (CREATE UNIQUE INDEX ON activities(account_id, activity_type) WHERE activity_state = 0) which works flawlessly. There is absolutely no sane way to implement this at the application layer, and even then we have additional applications and administrative work that may access this database directly so we can't rely on the application enforcing data integrity anyway. MySQL doesn't even support CHECK constraints, nor does it have partial indexes - while even SQL Server has supported partial (filtered) indexes since SQL Server 2008 (nearly a decade after PostgreSQL implemented them, but hey, still at least a decade before any of the MySQL family will have them). Should you put application logic in your database? No, probably not. But you should always make sure your database prevents ANY invalid data from being put into it, your little application won't be the only one touching that database forever in all likelihood.
  - Mouse47 9y ago
    >CREATE UNIQUE INDEX (filtered) Ha...that's a bit of a hack! I've had to do a similar trick for a unique constraint on columns where we didn't care if the value was null. SQL Server (for some reason) considers null a value in this case; in other words, there could only be one null value in the table for purposes of a unique constraint. E.g: create unique index on (col) where col is not null
    - snuxoll 9y ago
      Stuff like this always makes me glad PostgreSQL takes adherence to the SQL standard seriously, NULLS are supposed to be considered non-equal for unique indexes - probably some stupid design from the Sybase days that MS decided was easier to keep for compatibility than change, though.
  - jstewartmobile 9y ago
    My "little application"... a) a needlessly uncivil thing to write b) just the fact that you're (rudely) giving me the religion on CHECK constraints means you've probably never actually scaled a DB for high transaction volume. Once you have to start sharding and denormalizing for performance, we've left ACID territory, and most of the validation has to be in the application anyway.

jstewartmobile 9y ago

Where did I say throw out RDBMS? MySQL does fk/pk restriction, transactions, field enforcement (contrary to article), and all of the other good ACID stuff just fine. I'm complaining about things like PL/SQL letting you send an email inside a trigger (http://www.orafaq.com/wiki/Send_mail_from_PL/SQL http://www.orafaq.com/wiki/Send_mail_from_PL/SQL), or Postgres's XML processing features. I don't think that belongs in the DB. It's easy to say "don't do that" to a small team, but when you get to the corporate shops that do Oracle, that becomes a much harder thing to enforce.

snuxoll 9y ago
There can be more to data integrity than foreign keys and transactions. We have an application to track and distribute work to employees, there can only be one active work item per item type on an account - we use MSSQL for this and originally enforced this with a CHECK constraint, which ultimately failed, we switched to a partial index (CREATE UNIQUE INDEX ON activities(account_id, activity_type) WHERE activity_state = 0) which works flawlessly. There is absolutely no sane way to implement this at the application layer, and even then we have additional applications and administrative work that may access this database directly so we can't rely on the application enforcing data integrity anyway. MySQL doesn't even support CHECK constraints, nor does it have partial indexes - while even SQL Server has supported partial (filtered) indexes since SQL Server 2008 (nearly a decade after PostgreSQL implemented them, but hey, still at least a decade before any of the MySQL family will have them). Should you put application logic in your database? No, probably not. But you should always make sure your database prevents ANY invalid data from being put into it, your little application won't be the only one touching that database forever in all likelihood.
- Mouse47 9y ago
  >CREATE UNIQUE INDEX (filtered) Ha...that's a bit of a hack! I've had to do a similar trick for a unique constraint on columns where we didn't care if the value was null. SQL Server (for some reason) considers null a value in this case; in other words, there could only be one null value in the table for purposes of a unique constraint. E.g: create unique index on (col) where col is not null
  - snuxoll 9y ago
    Stuff like this always makes me glad PostgreSQL takes adherence to the SQL standard seriously, NULLS are supposed to be considered non-equal for unique indexes - probably some stupid design from the Sybase days that MS decided was easier to keep for compatibility than change, though.
- jstewartmobile 9y ago
  My "little application"... a) a needlessly uncivil thing to write b) just the fact that you're (rudely) giving me the religion on CHECK constraints means you've probably never actually scaled a DB for high transaction volume. Once you have to start sharding and denormalizing for performance, we've left ACID territory, and most of the validation has to be in the application anyway.