DB Design Patterns

Does anyone actually use DB Design Patterns that required it for one of your projects/work ?

I’ve been reading up on some patterns and they seem very interesting that you can separate your queries from your PHP. The only down side I see that it becomes very abstract and may not always be needed.

In fact, for most classes I create I usually just embed it within the method itself and return;

Some of which I’ve been reading on are Ad Hoc, Mapper and so on…

What do you guys think about separating the logic between PHP and your queries a step further?

Most of my queries are abstracted from my direct code by my database abstraction layer (e.g. Doctrine) and I barely ever get to use the direct sql.

The only time recently I’ve got my hands dirty with the SQL was when I need to create some pretty advanced queries for reporting on various metrics and just found it too difficult to get the abstraction layer to do it exactly as I wanted it.

Do you find any advantages of separating your sql from your PHP?

Most PHP developers have knowledge of SQL (at a professional level). How much knowledge and if you want them touching the SQL is the question that arises next.

If you automate those queries, in OOP, you end up writing code 10 times faster, and your code looks much simpler and gets allot easier to maintain / extend.

Ex, if you only use DTOs in your system (like active record or something), you can add caching (memcache / apc) to your entire site by extending one class.

That’s a very good point about extending for memcache / apc. Would save a butt load of time.

Question Vali…

I haven’t had a chance to fully mess with APC but would if you were to implement apc, what would be the need to extend from the sql class when you can just edit the sql class? Are you doing this to have both options (cache and non cached)?

Or am I missing something here?

You are missing something.

You need to keep your classes small and well defined.

Example:

  • Your DB class should do nothing but send queries to the database.

$rows = DB::selectRows("SELECT * FROM foo");
$row = DB::selectRow("SELECT * FROM foo LIMIT 1");
$id = DB::insert("INSERT INTO foo VALUES ...");

The reason to pass through the DB class:

  • if you ever want add a master/slave configuration, all you do is extend your DB class.

For caching, you usually cache your objects.
Example:


class dto {
  function __constructor ($user_id) {
    $this->data = DB::selectRow("SELECT * FROM ".get_called_class()." WHERE id = '" . $user_id . "'";
  }
}

class User extends dto {

}
# SELECT * FROM user WHERE id = 5;
$user = new User(5);
# SELECT * FROM user WHERE id = 5;
$user = new User(5);

Cached:


# code your own APC class
class cache extends APC {

}
class dto {
  function __constructor ($user_id) {
    $this->data = DB::selectRow("SELECT * FROM ".get_called_class()." WHERE id = '" . $user_id . "'";
  }
}

class cachedDTO extends dto {
  function __constructor ($user_id) {
    $this->data = cache::get(get_called_class() . '-' . $user_id);
    if (!$this->data) {
      parent::_constrctor($user_id);
    }
  }
}

class User extends cachedDTO {

}
# SELECT * FROM user WHERE id = 5;
$user = new User(5);
# returns it from the cache
$user = new User(5);

Got it, thanks for the example. It makes perfect sense now.