WordPress is the most popular CMS written in PHP for making websites and blogs. It's popular because of its flexibility and extensibility, from normal websites to portals like event management system, forums, job portals can be built with WordPress and the reason for this is WordPress database structure.
This post is written for the developers to understand how we can create anything in WordPress. so if you wish to learn WordPress from a developers perspective, read the full article.
Note: WordPress uses MySQL/MariaDB database which is open-source, popular, scalable RDBMS.
History
Let's start our article with a little bit of history. WordPress started the journey with blogs website in mind so the WordPress database structure revolves around categories, posts, links, comments and users. In WordPress 2.0, Static pages support is added by changing posts table slightly (post_type column in posts table is introduced).
By WordPress 2.5, WordPress revamped its database structure which is almost similar to what we see in the latest versions. This revamp introduces new WordPress terminology.
WordPress Database Structure (ER Diagram)
Before going to WordPress terminology let's have quick look to the ER diagram of the database structure.
If you don't have an idea about database terms such as schema, primary key foreign key. Please learn database first as it is the basic knowledge you need to learn to understand the later things.
Explanation
Let's think about the blogging system and the main entities for it. The blog system consists of:
- Posts: Articles which are written in the blog.
- Author: the Posts are written by authors (we can say users who uses blog system)
- Administrator & Moderators (again users of the system)
- Categories & Tags: which are used to group the similar, related post together which results in organized content.
If you read the table names in the above diagram, we can easily conclude
wp_posts ---> are used for saving articles.
wp_users ---> for storing author information (and all other users of the system)
but what about categories, before WordPress 2.5, there is a table called categories which is removed and now they are store in the terms table.
One thing is still remaining What about tags? where they are stored?
As WordPress support pages in WordPress 2.0 by adding one more column in posts table (post_type) similarly tags and categories support are given by adding new table "wp_term_taxonomy"
Lets go through the each table.
wp_posts
The post could be anything differentiated with post_type which could be "object in English grammar" for example book, event, tickets etc.
WordPress uses it for storing post, pages, menu items, and uploaded file information (Meda Library)
wp_term_taxonomy
The WordPress uses taxonomies as something which is used to group the object (posts) the examples of which can be categories, tags in case of article, categories, genres in case of the book, location in case of event etc.
WordPress uses this table to store category and tags with their term_id.
wp_terms
The WordPress uses terms as one of the objects/values of taxonomy for example for category taxonomy contains terms like entertainment, technology etc., location taxonomies terms could be cities such as Mumbai, London, new york etc.
WordPress uses it to store menu names, category and tag values.
PS: there could be introduced category_type as we did in wp_posts but WordPress core developers give more flexibility of many terms could be assigned to many taxonomies.
wp_users
Users are anyone who uses the system, accesses the system in case of the blog there are many users such as writers, administrator, moderators etc.
Now three main tables are discussed let's review other quickly
wp_terms_relationship
The posts can have many terms which are linked to the terms by wp_terms_relationships table where object_id is ID from post table and term_taxonomoy_id is primary key of wp_term_taxonomy table.
wp_comments
The post has comments this table is also open for extension (see comment_type column). This table could be used to store ratings of the posts or votes reviews anything which has one - many relationships with posts.
WordPress uses this table to store comments by default.
wp_options
This table is used for saving general settings by site_url, home_url,active theme etc. it can be used by plugin developers to save their settings. the table structure has option_name, option_value, and autoload in which option_value data type is a blob and autoload is a special column which tells WordPress about whether setting should be autoloader at time of loading WordPress or not. we should make autoload to 1 if our setting is used mostly in all pages and it doesn't consume much memory.
wp_links
This table holds the information about links which is now depreciated.
If you go back to the ER diagram we have mostly covered all tables except three which are called as metatables. but before going to them let's understand the concept of metatables and why they are needed
Let's understand this with an example, Book Information System -- where books information is shown
We have decided the following book Information needed to be stored in the system.
- Book title
- Description
- Book Author
- ISBN
- Publisher
- no of pages
Let's try to make this fit in our WordPress database structure, we decided title and description will go in post_title and post_content respectively.
Author and Publisher are good candidates for taxonomies as (it will be easier to query specific author or publisher books).
ISBN, no of pages are specific to a single book. there is one possibility we can create another table called book_details to store this info into that.
wait, here is the WordPress metatables concept come into pictures we could store additional data about the entity into its meta table in case of our example we could store the ISBN and no of pages as the following in post_meta table
it will be interpreted like this
book id 1 has a key (meta name) called ISBN and its value (meta value) is Xyz.
book id 1 has a key (meta name ) called no_of_pages and its value(meta value ) is 120.
WordPress gives us 4 meta tables
- wp_postmeta
- wp_termmeta
- wp_usermeta
- wp_commentmeta
if you look at any of these metatables you will find similar structure the foreign key for the respective entity, meta_name and meta_value.
Conclusion
There is no doubt WordPress flexibility comes from the database, let's summarize what makes WordPress flexible and extensible.
- Extra Type column in the posts, comments table (you can introduce your own type)
- Taxonomies table intro which helps you to create a category like structures which can be also hierarchical which helps to group the posts.
- MetaTables which is used to store additional information of entities.
You can visit this How to Fit Job Portal Application into wordpress database to more understand how we think about the customer requirement in WordPress way
Thank you guys for reading the article. Please provide your suggestion/queries about the article in the comments. Please let me know if you want me to cover any other topic in this WordPress tutorial series.
Comments
Post a Comment