Data Access Layer, Best Practices

I'm looking for input on the best way to refactor the data access layer (DAL) in my PHP based web app. I follow an MVC pattern: PHP/HTML/CSS/etc. views on the front end, PHP controllers/services in the middle, and a PHP DAL sitting on top of a relational database in the model. Pretty standard stuff. Things are working fine, but my DAL is getting large (codesmell?) and becoming a bit unwieldy.

My DAL contains almost all of the logic to interface with my database and is full of functions that look like this:

function getUser($user_id) {
   $statement = "select id, name from users where user_id=:user_id";
   PDO builds statement and fetchs results as an array
   return $array_of_results_generated_by_PDO_fetch_method;
}

Notes:

• The logic in my controller only interacts with the model using functions like the above in the DAL
• I am not using a framework (I'm of the opinion that PHP is a templating language and there's no need to inject complexity via a framework)
• I generally use PHP as a procedural language and tend to shy away from its OOP approach (I enjoy OOP development but prefer to keep that complexity out of PHP)

What approaches have you taken when your DAL has reached 开发者_如何学Cthis point? Do I have a fundamental design problem? Do I simply need to chop my DAL into a number of smaller files (logically divide it)? Thanks.

In terms of your architecture, each table in your DB should (generally) have its own model (PHP class), if you're not doing this, then I'd say you have a code smell.

If you have each table as a model, then I wouldn't worry about the amount of code in each class. It's good to have "fat models" and "skinny controllers".

If you wanted to thin out some code, you could use a lightweight data object wrapper, something like PEAR's DB_DataObject. A few examples of what a DB_DataObject model might look like:

$user = new User;
$user->get($user_id);

$user = new User;
$user->name = 'foo';
$user->find();
while($user->fetch()) {
...
}

The benefit of using something like DB_DataObject is you abstract away a lot of the low level PDO stuff and your class implementation will focus more on your business logic.

Maybe digressing a bit from the actual question asked, but you might be interested in a series of presentations by Toon Koppelaars, quite competent Oracle DB chap, about software architecture, known (or should I alas say, unknown) as "The Helsinki declaration".

See http://thehelsinkideclaration.blogspot.com/2009/03/helsinki-declaration-observation-1.html

It touches, perhaps a bit sideways, but heavily at any rate, on the subject that your question is really about.