Thoughts on SQL Antipatterns

Just watched the presentation about SQL Antipatterns and got one thought to share.

The presentation is good in general and I had encountered virtually all of the situations and in the end we came to the same solutions as the author recommends.

I have one disagreement about the last idea with “Model has an ActiveRecord”. It has a very significant downside.

The power of web frameworks is that you can easily create CRUD actions. You only need to provide the name of the db table and define some rules over input values (validators). You get this power because Models there inherit ActiveRecord class (Yii framework) and the ActiveRecord itself has already fixed rules how to CRUD records through PDO.

If you change the Model to represent multiple db tables as in this presentation then you suddenly lose that ability and have to handcraft the CRUD logic yourself. So you lose all the power of the framework.

There is a nice solution to this problem but you need PostgreSQL or Oracle (maybe there is an analogue in MySQL, I’m not sure). These DBMS allow creating “editable views” which behave just like physical tables but you define the CRUD logic inside the database. So your app thinks it’s a plain table and can leverage the full power of MVC framework while business logic stays in the database where it should be.

The implementation is as simple as that:

  1. Create a view with the things you need
  2. Create triggers on UPDATE, INSERT and DELETE for that view that will handle the business logic

 

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.