1

I asked this question a while back on SO (https://stackoverflow.com/questions/15506040/maximising-the-features-of-php-and-mysql-when-used-together) but it's a recurring topic, so I thought I'd ask in a different way over here.

Basically, we are stuck with the infamous php and MySQL combination (for various reasons), and are trying to produce applications which follow best-practice as much as possible. We notice that time and again, we are tightly coupling the application to the database and vice-versa, despite trying to adhere to SOLID principles.

One of the main reasons is to maximise the features of a relational database (such as strict datatypes, hierarchical data structures, relationships, enforcing foreign keys, firing triggers, etc.) This means that some of the application logic bleeds over into SQL. However, there are features that other RDBMS's offer which MySQL doesn't (such as CHECK constraint most notably) so, we end up using php to get round those.

This means we end up using php for some of things MySQL doesn't do well and we use MySQL to do things which aren't feasible in php.

So, even though every text book encourages programmers to keep things loosely coupled, does anyone have any examples of how to overcome limitations you have come across, or examples of other applications that mix the two up in a similar way?

  • I'm not sure I see the problem? Do you care about building stuff that works, or about following dogma? Has your approach actually proved problematic, or is this only about following what a textbook said? If it's the former, we can definitely help (but we'll need details), if it's the latter, we are getting into opinion territory and I'm not sure how helpful we can be. – yannis Aug 1 '13 at 10:33
  • @YannisRizos - of course I care about building stuff that works! My hang-up is that in order to get to a level where I assume I'm competent enough to do so, I rely on textbooks to give me the education I need. Then as user47507 says below, there are actually two text books - one for oo application development and one for RDBMS development. The result is that yes, it does prove problematic as I'm left with competing opinions on how to get something done. I was hoping for some examples of how to approach php + MySQL + pinch of salt to ease my transition from theory to practice! – boatingcow Aug 1 '13 at 12:49
  • Fair enough. Just keep in mind that the two domains (object oriented design & database development) aren't exactly compatible, getting them to work together is naturally troublesome. Simply put, there's no perfect solution, more often than not a good enough solution will be... good enough ;) For more, see: en.wikipedia.org/wiki/Object-relational_impedance_mismatch – yannis Aug 1 '13 at 13:59
1

According to this answer, you can solve the CHECK CONSTRAINT problem in the database layer:

https://stackoverflow.com/a/14248038


Keep in mind that the best practices for an OOP project assume that your application reside in the oop code, and that the database exists to store data from those objects (through a vendor provided or custom ORM layer). The expectation is that SQL is used mainly to translate objects into the database for permanent storage, not for application logic.

There are also best practices for SQL applications. Many applications are driven almost entirely by a database, with heavy use of stored procedures, triggers, views and other SQL features which are coupled to that database. In this type of application, the php code is mainly fetching data and handling user input, handling less of the application logic.

Both PHP/MySQL are widely used for rapid development, but neither would be mistaken for the 'head of the class' in best practice support in OOP/SQL respectively. This sometimes causes the pain you describe when implementing features, as you have to mix the two types of applications mentioned above (which then couples the application together). If that results in an acceptable application with better business results, it may be worth the tradeoff.

In the end, if the application can perform reliably and implement the required features under the current workflow, then the best choice is to save the architecture discussion for the next application. If this can not be done is a reasonable manner, then consider what type of architecture you wish to have and work to migrate the application.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.