Database Structure

Requirements for TYPO3 managed tables

The database tables used by TYPO3 CMS can be divided into two rough categories: those that are used by the system and are invisible to the users and those that will be managed via the TYPO3 CMS backend.

There are certain requirements for such managed tables:

  • The table must be configured in the global TCA array. This will tell TYPO3 CMS things like the table name, features you have configured, the fields of the table and how to render these in the backend, relations to other tables, etc.
  • The table must contain at least these fields:
    • "uid" - an auto-incremented integer, PRIMARY key, for the table, containing the unique ID of the record in the table.
    • "pid" - an integer pointing to the "uid" of the page (record from "pages" table) to which the record belongs.
    • other typical fields include:
      • A "title" field holding the records title as seen in the backend.
      • A "description" field holding a description displayed in WEB > List view.
      • A "crdate" field holding the creation time of the record.
      • A "tstamp" field holding the last modification time of the record.
      • A "sorting" field holding an order if records are sorted manually.
      • A "deleted" field which tells TYPO3 CMS that the record is deleted (in effect implementing a "soft delete" feature; records with a "deleted" field are not truly deleted from the database).
      • A "hidden" or "disabled" field for records which exist but should not be used (e.g. disabled backend users, content not visible in the frontend).

Note

Except for the "uid" and "pid" fields, all other fields do not fill a role automatically as soon as they exist. Their existence must be declared in the TCA configuration. This means that such fields can also be named freely, the above are the default names TYPO3 uses - for consistency it is recommended to name them that way.

The "pages" table

The "pages" table has a very special status. It is the backbone of TYPO3 CMS, as it provides the hierarchical page structure into which all other TYPO3 CMS managed records are positioned.

Standard pages are quite literally web site pages in the frontend. But they can also be storage spaces in the backend, very much like folders on a hard disk. For any record, the "pid" field contains a reference to the page where that record is stored. For pages, the "pid" fields behaves as a reference to their parent pages.

The special "root" page has three unique properties: its pid is 0 (zero), only admin-users can access records on it and these records have to be explicitly configured to reside in the root page. (Usually table records may only be created on a real page.)

Other tables

The tables which are not managed via the TYPO3 CMS backend fill various roles. Some of the most common are:

  • MM relations: when tables are related using a many-to-many relationship, another table must hold these relations. Examples are the table storing relations between categories and categorized records ("sys_category_record_mm") or the table storing relations between files and their various usages in pages, content elements, etc. ("sys_file_reference"). The latter is an interesting example, because it does actually appear in the backend, although only as part of inline records.
  • cache: when a cache is defined as using the database as a cache backend, TYPO3 CMS will automatically create the relevant cache tables.
  • system information: there exist tables storing information about sessions, both frontend and backend ("fe_sessions" and "be_sessions" respectively), a table for a central registry ("sys_registry") and quite a few others.

All these tables are not subject to the uid/pid constraint mentioned above, but they may have such fields if it is convenient for whatever reason.

There is no way such tables can be managed via the TYPO3 CMS backend unless a specific module provides a form of access to it. For example, the SYSTEM > Log module provides an interface to browse records from the "sys_log" table.