OOP design: How to incorporate DB handling into application objects

Question

This is a design problem I face regularly and I'd like to find some general insights about the subject. The code provided here is just an example.

In the design phase it's easy to decide you need an object:

User
==========
Unique ID
Login name
Password
Full name

And it's easy to convert it into a database object:

CREATE TABLE user (
    user_id INT NOT NULL PRIMARY KEY,
    username VARCHAR(15) NOT NULL UNIQUE,
    password_hash CHAR(32) NOT NULL,
    full_name VARCHAR(50)
);

My doubts start at PHP level. The obvious conversion is:

<?php
class User{
    public $user_id, $username, $full_name;
}
?>

However, how should I fill in the actual values?

I can keep the class DB-agnostic:

<?php
class User{
    public $user_id, $username, $full_name;
    public function __construct($user_id, $username, $full_name){
        $this->user_id = $user_id;
        $this->username = $username;
        $this->full_name = $full_name;
    }
}
?>

But then I need to run the query somewhere else...

I can encapsulate it inside the class constructor:

<?php
class User{
    public $user_id, $username, $full_name;
    public function __construct($user_id){
        $sql = 'SELECT username, full_name FROM user WHERE user_id=?';
        $parameters = array($user_id);
        $res = get_row_from_db($sql, $parameters);

        $this->user_id = $user_id;
        $this->username = $res['username'];
        $this->full_name = $res['username'];
    }
}
?>

This looks elegant but it prevents me from doing lots of stuff with the class:

• Validate a user by username and password ($user_id is not known yet)
• Print user info from forum posts (I can't afford 100 queries to show 100 users)

Most likely, I need to define several classes but I'm not sure about how to organise it. One base class and many child classes? Independent classes? Single class with specific methods? Perhaps it's a well-known design pattern but I was taught procedural programming.

I'd also appreciate some ideas about:

• Handling a collection of users
• Storing info in session so DB doesn't need to be queried on every page request

==== FOR THE RECORDS====

I've tagged Gordon's reply as answer for it provides interesting reading. Whatever, it's worth noting that I've found a very illustrative code snippet in one of the user comments in the Object Serialization page of the PHP manual that can be summarized as follows:

• It uses one class.
• An instance represents a specific user.
• The constructor is fed with the user details.
• The class provides static methods for functionality that's required before being able to have an instance, e.g. fetching a user from DB by ID or name.
• User instances can be serialized as session data.

Not being an OOP guru, I've found it very simple yet clean and useful. OOP texts have a tendency to overcomplicate simple tasks and my daily work consists mainly in small projects.

Answer 1

It depends on your architecture. The four common Data Source Architectural Patterns can be found in Martin Fowler's Patterns of Enterprise Application Architecture:

• Table Data Gateway

  An object that acts as a Gateway to a database table. One instance handles all the rows in the table.

• Row Data Gateway

  An object that acts as a Gateway to a single record in a data source. There is one instance per row.

• Active Record

  An object that wraps a row in a database table or view, encapsulates the database access, and adds domain logic on that data.

• Data Mapper

  A layer of Mappers that moves data between objects and a database while keeping them independent of each other and the mapper itself.

Further patterns:

• http://martinfowler.com/eaaCatalog/index.html
• http://sourcemaking.com/design_patterns