My considerations about Db models and RESTful application

I’ve worked on more than one project using complicate DB layers and HTTP client/server systems (e.g. RESTful apps) to interface with the DB server.

My opinion: when working on a project (especially a team project), easy solutions are better then clever ones. OK, OK, a design pattern is an elegant solution to a common problem, but before doing that, keep in mind two basic design principles: KISS (Keep It Simple, Stupid) and YAGNI (You Ain’t Gonna Need It).

A DB layer is the ground of an application, and it must be simple, solid and extensible.

My idea: Simple, maintainable and extensible Model classes for DB access

Make a class for each DB table, all of them extending a common abstract one (for future extensions). Each class has its own methods to insert/retrieve/delete/update the data. Of course do not make a method for each operations, group them using multi-purpose methods and class constants in arrays. Methods accepts params as array and return data using arrays (so easy to deal with). Common insert/delete/update return data in the same format (e.g. numbers of records updated, or exception, no different values for each method). All the input and return params must be documented with PHPDoc comments including all the possible values of an input array key . You can write a constant for each column in the db, so you do not have to look at the DB to know what’s the column name, just use the IDE autocompletition for everything.
e.g.

$newsModel = News::getInstance();
/* get news of the category id=2, limited by 5 and ordered by date.
Join with parent table and select with columns are needed. */
$rows = $newModel->getRecords(array(
  News::FILTER_CATEGORY = 2,
  News::ORDER_CREATED_DESC,
  News::LIMIT => 5,
  News::JOIN => News::JOIN_AUTHORS,
  News::COLUMNS => array(News::FIELD_TITLE, Authors::FIELD_NAME)
));
foreach ($rows as $row) {
   //echo $row[News::FIELD_TITLE]; //piece of news title
   //echo $row[Authors::FIELD_NAME]; //author name (joined)
];

}

Semplicity and Maintainability

One db table => only one class. All the logic inside the models. If I alter the db table, easy to change the centralized logic and costants.
From the controller, I write the code to access the database using IDE autocompletition in a few seconds.
If I want to add another condition to the query, just add to the array. To add a support to something new to the class (new filter, new join, new column), just add the constants, the logic and update the PHPDoc comments.

Caching advantage

This approach make very easy to implement a cache system with automatic tagging and cache invalidation by using wrappers and magic methods [see post about it ].

Extensibility, e.g. move to a client/server via HTTP (web service)

– You want to switch from Zend_Db to Doctrine / NoSQL or optimize the queries ? no problems, just replace the inside logic. the methods still have same input/output. Much easier if you have written unit testing. You sure don’t have to write another layer to dynamically switch between database (that requires a huge effort, in contrast with YAGNI ).

– You want to move the database to a different server and use HTTP calls to get the data (like RESTful apps) ? I have a elegant, don’t-repeat-yourself and easy solution with PHP 5.3. Make the getInstance() of the super class returning a wrapper (you can do that, or alternatively, use a external factory class) that through magic methods, makes the HTTP calls to the server passing [class name, method name, array of params] and decode the response into an array (just one class for all models !). The server will use the same db models classes (shared libs) and implement the other side of the logic with a correspondent one class ($model = new $modelName(); return $model->$methodName($params);). So, basically you code as you are using a local db, but the request is actually going through HTTP to another server. You can switch back to the local db approeach by just changing the abstract DB model class to return the db instance instead of the wrapper.

Drawbacks ?

Most of the application can use this approach and do not need over-engineering of the DB layer. Please follow with your comments with any problems you see in this approach.