Every WordPress website uses a MySQL database in the background. For a better understanding of the basic functionality of WordPress, it is worth taking a look at the structure of the WordPress database and the structure of the WordPress tables.
What does WordPress need a database for?
Any form of content such as posts, static pages, categories, keywords or comments are stored in the WordPress database. Likewise of course the user accounts, WordPress settings as well as theme and plugin options. Entries are also made in the WordPress database tables for uploaded media.
As an end user, you rarely come into contact with the database. When it comes to backing up and relocating a website, the WordPress tables must be backed up or migrated, but there are a large number of plugins to easily handle these cases.
The database is provided by the web host on the server. WordPress supports MySQL or MariaDB as a database system. When installing WordPress, you must enter the access data for the server's database. With the WordPress installation, all required tables are then created in the database.
The tables of the WordPress database at a glance
The WordPress database consists of several tables. Each table defines different fields for storing data.
For example, the table for comments has the fields comment_ID , comment_author, and comment_date . The fields can also be thought of as columns. Each new comment is saved as a new line in the table.
Now let's take a closer look at all WordPress tables.
WordPress database diagram
The diagram shows the initial WordPress tables. You may find additional tables in your database that were created by plugins. By default, WordPress uses the prefix wp_ , unless another prefix was defined during installation.
The wp_posts table is the center of the WordPress database.
All posts and static pages are saved here. The table therefore contains most of the fields, including the title, author, creation date and content of the post. The post_type field is also very important to differentiate between the types of content.
The table is also used for less visible content. Information about each uploaded file in the media library is stored as an attachment in wp_posts . WordPress menus also use the table to store the navigation links.
In addition, the cached revisions of a post and the customizer changesets can be found in the table. Changesets are the new feature in WordPress 4.9 with which customizer options can be planned and published later .
In addition to the standard post types, every custom post type is also deposited in wp_posts .
Additional information for individual posts can be saved in wp_postmeta . For example, the user-defined fields are saved there, ie pretty much every plug-in with its own metaboxes uses this table in the background.
WordPress has several tables for metadata, which I will introduce in the following. These are all structured according to the same principle - a unique Meta ID, two fields for Meta Key & Value and a link to the table that is supplemented (in this case wp_posts ).
The assignment of a page template is saved as a Postmeta, for example:
With this structure, any number of metadata can be created for posts.
wp_terms and wp_termmeta
The basic information for all categories and keywords is stored in wp_terms . Custom taxonomies added by plugins can also be found here.
For example, if you add a new category, the ID, name and URL will be saved:
|27||WordPress tutorials||wordpress tutorials|
wp_termmeta works exactly like wp_postmeta and allows the storage of additional metadata for the terms.
wp_term_taxonomy saves further information about the term and its taxonomy (category, keyword or custom). The table first determines whether a term is a category or a keyword.
The term_id field refers to the wp_terms table. In addition, the description of the term is recorded with description , the hierarchy of terms with parent and the number of linked posts with count .
|34||27||category||Description of the category||0||2|
|35||48||post_tag||Description of the keyword||0||4th|
This table is used to link posts (article, page) with the taxonomies (category, keywords). object_id is the ID of the post , while term_taxonomy_id refers to the entry in wp_term_taxonomy .
wp_comments and wp_commentmeta
All comments are kept here, so it's pretty self-explanatory. If a logged-in user comments, the comment is linked to the user ID. The table wp_commentmeta has the same structure as wp_postmeta .
wp_users and wp_usermeta
As the name suggests, this table contains all users of the WordPress installation. Therefore username, email and password are saved here.
Here , too, wp_usermeta works in the same way as the other tables for metadata. A lot of data such as first and last name, but also user authorizations, are outsourced here.
All settings that can be configured in the WordPress backend are recorded in wp_options , including the options in the customizer and the transients cache . Themes and plugins also use the table when adding settings with the Settings API or Theme_Mods API.
Nice to know: In contrast to WordPress menus and other content, widgets are also saved in wp_options .
The table wp_links comes from the time when WordPress was still a pure blog software. This is where the links from the Blogroll, which was very popular at the time, are saved. The links feature has now been completely removed from the WordPress core, but can be added again with the Link Manager plugin.
Even if this post doesn't contain any specific tips, it is sometimes quite useful to have the basic structure and table structure of the WordPress database in mind. Hope this article has contributed to a better understanding.