PHP: One giant database class or several smaller classes?

Question

For the current app I am writing I have elected to place all database functionality into a single class, as it allows me to keep the database code away from the business logic and easily replace the database code if we ever have need to switch to another DBMS. However, recently my database class has become rather large (EDIT for info: approximately 53k), and I'm worried about the speed of parsing this file due to its volume, since it generally must be parsed for each request.

Typically only one, or maybe two, different "Types" of database calls are made (e.g., user system calls, asset system calls, map system calls, session system calls, etc) at any given time, so one option I was considering was breaking the tasks into a series of database object "slices" and then dynamically loading those at run time based on the functions requests.

On the other hand, I'm worried that doing this would either (a) lead to a large amount of parallel execution in memory (i.e., each slice now has a query method, an independent query log, etc) as well as forcing me to modify all the existing code to point to the new, smaller objects or (b) cause relative performance losses as I back-hack in this functionality to work with the code that's already written (e.g., have each slice point back to the parent's querying functions as well as the performance hit to be incurred by suddenly using __call all over the place instead of direct method access).

What is the more correct course of action in this scenario?

EDIT For More Info: The file is approximately 53kb with about 2,350 lines at present (and it is not done), although this may be considered skewed as I use an expanded SQL model for readability, e.g.

SELECT
    foo,
    bar,
    baz
FROM
    someTable st
    LEFT JOIN someOtherTable sot
        ON st.id = sot.stId
WHERE
    cond > otherCond

There are 70 querying functions, each one performing some unique task, with very little overlap (If I need two startlingly similar result sets I can simply ignore what I don't need each time and reuse the same queries).

EDIT: Example function:

public function alarm_getActiveAlarmsByAsset($tier, $id) {
    if (    !Redacted::checkNumber($tier, $id) 
        ||  $id < 0 
        ||  $tier > Redacted::ASSET_LOWEST_TIER 
        ||  $tier < Redacted::ASSET_TIER_CUSTOMER
    ) {
        return false;
    }

    $sql = "
        SELECT
            alarmId,
            alarmTime,
            server,
            source,
            reason,
            train,
            server_sites.siteId AS `siteId`
        FROM
            alarm_alarms
    ";

    $join = '';

    switch ($tier) {
        case Redacted::ASSET_TIER_CUSTOMER:
            $join = '
                LEFT JOIN red_campus
                    ON red_campus.campId = red_site.campId
            ';
        case Redacted::ASSET_TIER_CAMPUS:
            $join = '
                LEFT JOIN red_site
                    ON red_site.siteId = server_sites.siteId
            ' . $join;
        case Redacted::ASSET_TIER_SITE:
            $join = '
                LEFT JOIN server_sites
                    ON server_sites.servId = alarm_alarms.server
            ' . $join;
    }
    $table = isset(self::$dbTierMap[$tier + 1]) ? self::$dbTierMap[$tier + 1]['table'] : 'server_sites';
    $field = isset(self::$dbTierMap[$tier + 1]) ? self::$dbTierMap[$tier + 1]['parent'] : 'site';
    $sql .= $join . "
        WHERE
                ackId IS NULL
            AND {$table}.{$field}Id = {$id}
    ";

    $r = $this->query($sql);

    if (!$r) {
        return false;
    }

    $alarms = array();
    while ($alarm = mysql_fetch_assoc($r)) {
        $alarms[] = $alarm;
    }
    return $alarms;
}

Answer 1

its difficult to tell without knowing db scheme, but its always easier to maintain several classes than one big one(e g one class per logical entity/db table)

you shouldn't worry about parse time as the request to the database(and any network request) will always take longer

Answer 2

If you are worried about parsing time, and not code quality, you could use some compiler cache, such as APC.

However, if you have a big class, it's likely that you should refactor it into smaller ones just for readability. If you can't easily find what you need in the class (to modify it, for example), then it's probably too big to maintain.
If you decide to refactor, remember that maintainability and code quality are far more important than execution speed (database I/O is slower than executing code anyway).

Answer 3

Normally it is easier to maintain many small classes. For MySQL (mostly used with PHP) there are several class generators available:

sourceforge: PHP Class Generator

sourceforge: php Class Generator - PCG

Perhaps you can find new ideas there.

Answer 4

As far as I understand, your database class basically holds all possible queries, hardcoded, that are made in the entire app?

A better way to achieve a database layer abstraction is by abstracting the queries into an internally consistent format, and use models and database adapters to turns these into actual SQL. E.g.:

$customer = $Customer->find(array(
    'fields'    => array('name', 'id'),
    'condition' => array('ssn' => $ssn)
));

The Customer class maps to a specific table, and if necessary could map the schema to different columns:

class Customer extends BaseModel {
    public $table = 'xe_a3_cstmr';
    public $schema = array(
        'name' => 'ze_42_nm',
        …
    );
}

The BaseModel turns these abstracted queries into real SQL, depending on which database it needs to talk to:

SELECT `xe_a3_cstmr`.`ze_42_nm`, `xe_a3_cstmr`.`…` FROM `xe_a3_cstmr`
WHERE `xe_a3_cstmr`.`ssn` = 123235;

This way your database layer will not grow exponentially with every new query you need to make in your app, while still maintaing flexibility to map queries to different databases. This is known as ORM. Not to mention that this actually provides much easier abstraction. Instead of needing to rewrite every single hardcoded query, you just need to write a different database adapter.

As Mewp suggested up in the comments, a good place to start would be Doctrine.