Wordpress database structure
Tags: wordpress
Published: 2021-03-13 08:04:36

__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

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tnh7pqlusfgmg772vro4.png)

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