Database layer conventions

Give each entity its own <Thing>Database class. Every query returns a data object built by a private fromRow mapper that uses the To:: helpers to convert each column, so a field's nullability matches its column. Admin queries expose ids; front queries do not. Reconcile data from different modules in PHP rather than joining their tables.

final class MemberDatabase {
    public static function findByEmail(string $email): ?MemberData {
        $row = Database::Row(
            'SELECT name, email, verified_at FROM member WHERE email = ?',
            [$email]
        );
        if ($row === null) {
            return null;
        }
        return self::fromRow($row);
    }

    private static function fromRow(array $row): MemberData {
        $name             = To::String($row['name']);
        $email            = To::String($row['email']);
        $optionalVerified = To::OptionalDateTime($row['verified_at']);

        return new MemberData($name, $email, $optionalVerified);
    }
}

See The database layer for the how-to and the Database module for the reference.

Table naming

A table name is singular, lowercase, and underscore-separated, and it begins with the name of the module that owns it. One member is a row in member; the tables that hang off it are member_auth, member_code and member_folder. The singular reads naturally against a single row, the lowercase and underscores avoid quoting and case surprises across databases, and the module prefix tells you at a glance which module owns the table.

member
member_auth
member_code
member_folder

Because the important part is on the left, a module's tables sort together as one block in any alphabetical listing, so a schema dump reads as grouped families rather than scattered names. See Naming for the general left-to-right ordering rule that this follows.

All SQL lives in a Database class

Every database access is in a class named <Something>Database. There is no SQL anywhere else: not in a page, a service, an Api handler, or a data object. If you need to read or write a table, add a named function to that table's Database class and call it. This keeps every column name and query in one place per entity.

Rows become objects through fromRow

Any function that fetches a row returns a data object built by the class's private fromRow mapper, using the To:: helpers to convert each column so a field's nullability matches its column. Callers never see a raw row or a column name. The worked how-to is The database layer.

A single-record function returns the object or throws

A function that fetches exactly one record promises exactly one: its return type is the data object, not a nullable. When the row is not found it throws DatabaseErrorNoRow rather than returning null. The signature alone tells the caller the value is present, so no null check follows.

static function Get(int $memberId): MemberData       // throws DatabaseErrorNoRow if absent
{
    return Database::SelectRow('member', 'MemberID', $memberId, MemberDatabase::fromRow(...));
}

A list function may return an empty array

A function that returns many records returns an array of data objects, and an empty array is a normal result, not an error. Never throw because a list came back empty; the caller handles zero rows the same way it handles any other count.

Optional lookups start with "optional"

If a single lookup may legitimately find nothing, the function name starts with optional and it returns DataType|null, never ?DataType. Any variable or data-object field that holds such a value also starts with optional, so a name without that prefix is guaranteed present and needs no null check.

static function optionalByEmail(string $email): MemberData|null
{
    $where = (object)['MemberEmail' => $email];
    return Database::SelectRowIfExistsWhere('member', $where, MemberDatabase::fromRow(...));
}

No cross-module joins

Never JOIN tables that belong to different modules. Only join tables inside the same module. When two modules' data must meet, gather ids from one and load the other in a bulk query, then cross-reference in PHP.

The reason is coupling. A join hard-wires two modules' tables into one statement, so neither can move. Keeping them apart lets modules stay loosely coupled and lets a growing site put different modules in different databases, where a join would be impossible. See Loose coupling for the principle and The database layer for the bulk-id pattern.

Throw for missing data, keep the happy path

Prefer throwing when expected data is missing. Then the body of a function reads as a straight happy path and the exception is caught high up, in the Api handlers and routing, rather than being repeated as a null check at every call site. Do not scatter null checks through the code. The one exception is a case where null is genuinely expected and you have real logic for it: that is exactly what the optional naming marks.

Next: Rendering pages.