WordPress – Database Schema

Summary: Learning about WordPress Database Schema, Tables and its each column.

A brand new WordPress website has 12 tables.

wp_posts
wp_postmeta
wp_options
wp_users
wp_usermeta
wp_term_taxonomy
wp_terms
wp_term_relationships
wp_links
wp_comments
wp_commentmeta
wp_termmeta

Tables are often associated with one another since various pieces of data may be related.

wp_post

The wp_post table stores all data related to the content of a WordPress website. This includes things such as posts, pages, and page revisions. Also stored here are navigation menu items, media files, images, and attachments. Content data used by plugins also sit in this table.

Post_type is a table column within wp_post that categorizes the above-mentioned data. It allows users to request specific types of data using a database query. This makes post_type the most important column in the wp_post table. The post_type column shows the type of each row – a post, page, attachment, nav_menu_item, or another type. This variety of content gives the table a flexible nature.

ID: Unique identifier for each post.
post_author: User ID of the author.
post_date: The date and time when the post was created.
post_content: The main content body of the post.
post_title: The title of the post.
post_status: Status of the post (e.g., publish, draft, pending).
post_type: Type of post (e.g., post, page, attachment).

wp_postmeta

Every post contains a set of data called metadata. Storage of this information takes place in the wp_postmeta table. Certain plugins may also add their data to this table. wp_postmeta is an adjunct to the wp_post table. It is a vertical table that stores the data using key/value pairs.

meta_id: Unique identifier for each metadata entry.
post_id: The ID of the post to which the metadata is associated.
meta_key: The name of the metadata field.
meta_value: The value of the metadata field.

wp_options

wp_options is one of the most important WordPress tables. This table stores all the options set under Administration > Settings panel. The settings stored here include the URL, the title, installed plugins, and more. The majority of plugins store their settings here, too. Also stored in this table are all the WordPress dashboard settings. Unlike other tables, the wp_options table does not relate to any other table.

option_id: Unique identifier for each option.
option_name: The name of the option.
option_value: The value for the option.
autoload: Indicates whether this option should be loaded automatically by WordPress.

wp_users

This table holds the list of all registered users on our WordPress site together with their basic information. This includes login, password (encrypted), email address, registration time, display name, status, and activation key (when necessary).

ID: Unique identifier for each user.
user_login: The username for the user.
user_pass: The hashed password for the user.
user_email: The email address of the user.
user_registered: The date and time when the user registered.
display_name: The name displayed publicly for the user.

wp_usermeta

Metadata is a set of additional data about other data. As noted, the wp_user table stores the basic information about the site users. The wp_usermeta table holds additional data that is not stored in the wp_uset table. This includes, for instance, the users’ last names.

umeta_id: Unique identifier for each user metadata entry.
user_id: The ID of the user associated with the metadata.
meta_key: The key name for the metadata field.
meta_value: The value for the metadata field.

wp_terms

The wp_terms table stores the categories for posts and links and the tags for posts. Terms are items of the system used to classify objects. For instance, it’s possible to classify posts and custom posts in different ways. When creating a post there is the option to add a category and tags to it. Those are examples of taxonomy – a system of classifying and grouping things. The wp_term table contains a column called “slug.” A slug is a tag of a specific post.

term_id: Unique identifier for each term.
name: The name of the term (e.g., category name).
slug: A URL-friendly version of the term name.
term_group: Used for grouping terms together, but rarely used in WordPress.

wp_termmeta

This is a new table introduced in WordPress 4.4. It stores additional information about terms (data not stored in the wp_terms table). It’s possible to access the term meta using the following functions: add_term_meta, get_term_meta, update_term_meta, and delete_term_meta.

meta_id: Unique identifier for each metadata entry.
term_id: The ID of the term associated with the metadata.
meta_key: The key name for the metadata field.
meta_value: The value for the metadata field.

wp_term_taxonomy

As the name indicates, this table describes the taxonomy for the entries in the wp_term table. The taxonomy could be a category, link, or tag. The structure of this table allows we to use the same term for different taxonomies. For instance, the term “database” could be a category for posts and also a custom taxonomy for a custom post type.

term_taxonomy_id: Unique identifier for each term taxonomy entry.
term_id: The ID of the term associated with the taxonomy.
taxonomy: The type of taxonomy (e.g., category, post_tag).
count: The number of objects (posts) associated with the term in this taxonomy.

wp_term_relationships

This is where WordPress stores the critical data as regards using taxonomies. The wp_post table stores the post data. The wp_term_relationship table records the set category and tags. It defines a relationship between a post (object) in the wp_post table and a term of a specific taxonomy in the wp_term_taxonomy table. For example, this post is associated with a category and several tags. The wp_term_relationship table is responsible for maintaining that association.

object_id: The ID of the post (or custom post type) associated with the term.
term_taxonomy_id: The ID from wp_term_taxonomy that links to the wp_terms table.
term_order: The order of the term for the object.

wp_comments

Wp_comments stores all the comments left on our WordPress posts or pages. It also holds information about the author of the comment. This information may include name, email address, and comment type (simple, pingback, or trackback).

comment_ID: Unique identifier for each comment.
comment_post_ID: The ID of the post the comment is associated with.
comment_author: The name of the comment author.
comment_author_email: The email of the comment author.
comment_date: The date and time when the comment was posted.
comment_content: The content of the comment.
comment_approved: Approval status of the comment (e.g., 1 for approved, 0 for pending).

wp_commentmeta

wp_commentmeta contains additional information about each comment.

meta_id: Unique identifier for each metadata entry.
comment_id: The ID of the comment associated with the metadata.
meta_key: The key name for the metadata field.
meta_value: The value for the metadata field.

wp_links

Links are like letters of recommendation. A blogroll is a list of all external links, most often displayed in a website’s sidebar. Blogrolls were very popular up until a few years ago. The idea was for website owners to make smart decisions when linking to another website. However, some site owners abused the blogroll feature and so WordPress removed it. The wp_links table is the storage area for all data related to blogrolls and links. It’s now quite unusual to find the wp_links table since blogrolls are not used anymore.

link_id: A unique identifier for each link (primary key).
link_url: The URL of the external website.
link_name: The name or title of the link, usually displayed to users as the anchor text.
link_image: The URL to an image associated with the link. This was used to display a link button or banner image.
link_target: Defines how the link should open when clicked. Common values include _blank (open in a new tab/window) or _self (open in the same tab/window).
link_description: A description or extra information about the link, which might be displayed as a tooltip or alongside the link.
link_visible: Indicates whether the link is visible (Y) or hidden (N).
link_owner: The user ID of the person who added the link, linking back to the wp_users table.
link_rating: A numeric rating for the link, which could be used to rank or sort links (not commonly used).
link_updated: The date and time when the link was last updated.
link_rel: The rel attribute for the link, often used for SEO purposes to specify the relationship between the linking page and the linked page (e.g., nofollow, noopener).
link_notes: Extra notes or comments about the link.
link_rss: The URL to an RSS feed associated with the link, if applicable.