Are there any benefits to using MySQL for anything other than as a 'dumb' data-store?

Question

The reason I ask is that we'd like to use a certain CHECK constraint which MySQL currently doesn't suport. Without this type of constraint in place, the whole reason for using foreign keys and referential integrity seems to diminish as the application code takes on more of the database's responsibilities.

If we were to create a 'dumb' data model and move all of the referential integrity checking to a layer in the application code, then potentially testing could be simpler as referential integrity errors would be trapped in the application rather than the db. It could also potentially speed up development of new modules, as they wouldn't necessarily have to be referentially complete (is that a term?) before testing.

So, are there any other benefits to sticking with a 'proper' data model in MySQL and keeping foreign keys and 'ON UPDATE CASCADE' statements, etc?

Or, should we ditch MySQL and move to something else?!

Thanks!

Answer 1

Some developers advocate having no business logic at all in the database -- your dumb data-store. So that is definitely a valid strategy.

What concerns me about moving the constraints (and other business logic) out of the database is that it is more difficult to enforce constraints everywhere. Every single developer in every single application can violate that constraint. The DBA can't help, either.

So, I lean toward having these rules in the database itself.

Answer 2

You can emulate a check constraint in other ways

• Trigger
• FK to a single row table
• Single value enum

It's inconvenient, but not the end of the world.

Not all constraints can or should be done in the application anyway. Uniqueness? Zero-sum checks (eg balance = zero before account is deactivated).

It's your data and your mess to clean up when it goes wrong...