Wordpress database structure

Prerequisites

You must have Wordpress installed in your local machine or in a server and access to PhpMyAdmin which gives access to your MySQL database.

Default tables in the database

When we have just installed wordpress, we will have the 12 default tables created in the database

If you already had your wordpress website for some time, it is possible that you may have plugins already installed and you may have additional tables in the database. You can also use the Adminer plugin to get access to the database. It has notable features like backing up the database. We can go through the default wordpress database one by one.

Default tables in the wordpress database

• wp_users
• wp_usersmeta
• wp_options
• wp_posts
• wp_postmeta
• wp_terms
• wp_termmeta
• wp_terms_relationships
• wp_term_taxonomy
• wp_comments
• wp_commentsmeta
• wp_links

WP_USERS

This table will contain the users of the website. It holds unique information about the users, simple it helps in User Management.

User Roles

There are 5 user roles in a standard installation of Wordpress and 6 user roles in a multi-site installation. Role based access is nothing but providing access only to limited features and functionalities based on the user role.

• Admin

It is the highest level of user role. The users with this role can do anything on the website.

• Super Admin

It is specific to multi-site installation. Multi-site means multiple wordpress sites managed by one Wordpress installation.

• Editor

Editor is someone who has access to managing and publishing posts including the posts of other users.

• Author

Author is someone who can only manage and publish their own posts

• Contributor

A contributor is someone who can write and manage their own posts but cannot publish them.

• Subscriber

A subscriber who can only manage their profile

Fields in the wp_users table

You can see 10 fields in the wp_users table

• ID - unique identifier of the user
• user_login - username
• user_pass - encrypted password of the user
• user_nicename - display name for the user
• user_email - user email address
• user_url - url of the user ... for example user's website
• user_registered - date and time when the user registered
• user_activation_key - used for resetting passwords
• user_status - used to indicate a spam user
• display_name - name which is to be publicly used on the site

WP_USERMETA

Additional information to the users are stored here. Other user profile fields for a user in the dashboard are stored here.

• umeta_id - unique id
• user_id - Reference to the wp_users table
• meta_key - identifying key for the type of data
• meta_value - the actual data

WP_OPTIONS

All the website settings are stored here. We can find the _theme being used, plugins, temporarily cached data and widgets _ by using this table. Plugins and themes will store their settings data here only when they don't create separate tables for themselves.

• option_id - unique id
• options_name - identifying key for the type of data
• options_value - actual data, here the data is serialized
• autoload - controls if the options is automatically loaded

WP_POSTS

It stores content including posts, pages, menu items, media attachments that the website uses.

• ID - unique id
• post_author - the userid of the user who created it (Reference to the wp_users table)
• post_date - date and time of creation
• post_date_gmt - GMT date and time creation
• post_content - It will have the content for the post, including HTML, links, shortcodes and other content
• post_title - title of the post
• post_excerpt - a short description about the post
• post_status - status of the post like pending, published, draft etc..

• comment_status - it defines whether comments are allowed for the post

• ping_status:

It defines whether the post allow ping and trackbacks. Trackback is a way to notify other blogging systems that we have linked to their blogging content.

• post_password - optional password to view the post
• post_name - URL friendly slug of the post title
• to_ping - list of URLs WordPress should send pingbacks to when updated.
• pinged - a list of URLs WordPress has sent pingbacks to when updated
• post_modified - time and date the post was last modified on
• post_modified_gmt - GMT time and date when the post was last modified
• post_parent - creates a relationship between this post and the other when the other post is an extension or attachment to another type
• guid- Global Unique Indentifier, it is the permanent URL to the post, not the permalink version.
• post_type - defines the content type
• post_mime_type - it is used only for attachments, the MIME type of the uploaded file.
• comment_count - it will have the total number of comments, pingbacks and tracebacks

WP_POSTMETA

It contains additional information about the posts. It stores information in key/pair values.

• meta_id - unique id
• post_id - Reference to the wp_posts table
• meta_key - key to identify the type of data
• meta_value - the actual data value

WP_COMMENTS

This table stores the comments about each post. Any additional comment information can be stored in the wp_commentmeta table.

• comment_ID - unique id
• comment_post_ID - Reference to the wp_posts table
• comment_author - Name of the comment author
• comment_author_email - It will have the email of the comment author
• comment_author_url - URL of the comment author
• comment_author_IP - It holds the IP address of the comment author
• comment_date - Date and time of the posted comment
• comment_data_GMT - GMT date and time of the posted comment
• comment_content - It will hold the actual comment text
• comment_karma - It is not used by Wordpress, but by plugins to help manage comments
• comment_approved - Holds status about comment approval
• comment_agent - From where the comment was posted for example: from the browser, OS etc..
• comment_type - Type of comment whether its a comment, pingback or trackback
• comment_parent - refers to another comment when this comment is a reply (Reference to the user_id column in the wp_users table).

WP_COMMENTMETA

It store additional information about the comments.

• meta_id - unique id
• comment_id - Reference to the wp_comments table
• meta_key - key to identify the type of data
• meta_value - actual value of data

WP_TERMS

In wordpress, it classifies posts and custom post types in various ways. While creating a post, we will be adding tags and categories to it. It will be helpful to group or categorise content together.

• term_id - unique id
• name - name of the term
• slug - URL friendly SLUG of the name
• term_group - it is the ability for the themes and plugins to group terms together to use aliases. Not used by Wordpress core itself.

WP_TERM_TAXONOMY

Taxonomy is the grouping mechanism for some posts. For example food in a restaurant can be classified into Italian, Chinese etc.. ,. The words Chinese and Italian are terms in our taxonomy.

• term_taxonomy_id - unique id
• term_id - reference to the wp_terms table
• taxonomy - the slug of the taxonomy
• description - description of the term used in this taxonomy
• parent - ID of a parent term. Used for hierarchical taxonomies like Categories 8 count - number of post objects assigned to the term for this taxonomy

WP_TERM_RELATIONSHIPS

Normally post exists in the wp_posts table and when we assign categories and tags, this is the table which records that information. Each row defines a relationship between a post in wp_posts table and a term of the taxonomy in the wp_term_taxonomy table.

• object_id - unique id
• term_taxonomy_id - Reference to the wp_terms_relationships table
• term_order - ordering of terms for an object

WP_TERMMETA

Term meta table allows us to store additional information similar to the post meta and the comments meta table

WP_LINKS

Wordpress has a feature called Blogroll. It was basically used to manage all the links that pointed to other sites.

• link_id - unique id
• link_url - URL of the link
• link_name - name of the link
• link_image - URL of the image somehow related to the link
• link_description - It holds the description about the link
• link_visible - Defines whether the link is public or private
• link_owner - user id of the user who created it. Reference to the wp_users table
• link_rating - adding a rating between 0-10 for the link.
• link_updated - Date and time for the last link update
• link_rel - relationship of the link
• link_notes - notes about the link