Deeper Understanding of Data Relationships in the WordPress Database

This site has previously written an article introducing WordPress data tables, which introduces you to WordPress data tables and which data table stores which type of data, in this article, I will introduce you to these data in the WordPress DatabaseHow are they related to each other in. Before we begin, let's explain a few relationships between data tables. Since database relationships are abstract, let's use a few common social relationships as examples.

• One-to-one: a husband can have only one wife and a wife can have only one husband.
• One to many: a son can have only one father, and a father can have many sons.
• Many-to-many: a teacher can have many students and a student can have many teachers.

The above 3 kinds of database relations are used in WordPress, so how do these three kinds of data relations actually work in WordPress? Let's take a look at them together.

One-to-One One-to-One relationship

A one-to-one relationship is the simplest of all data relationships (male-female relationships as well), where one piece of data corresponds to another piece of data and only one other piece of data, and the other piece of data does the same for that piece of data.

One-to-one relationships in WordPress include:

• Article ID and Article Content
• Article title and article content
• Article ID and Article Tags
• Comment ID and comment content
• User ID and User Name

These data are often stored in a data table in the same line, in fact, this is not strictly a one-to-one relationship in the database, in order to facilitate your understanding, here to take these relationships to give an example, we can understand it.

The one-to-one relationship is the simplest and most basic relationship, and without going into much detail, the most important and most used relationship in WordPress is the one-to-many relationship.

One-to-Many One-to-many relationship

One-to-many relationships are very common in all database systems, and it is because of this one-to-many relationship that the database system becomes powerful, not like Excel, the data can only be flat. And this powerful and convenient to reflect in a data has more than one data needs to be associated with the time.

In WordPress, a one-to-many relationship is realized by creating links between two data tables with unique IDs.post_id exist wp_posts data table instead of each article. In the comments datasheet wp_comments post_id is used to determine which post a comment was made on. This means that each post ID can only appear once per post, but can appear multiple times in the comments of that post, and if there are no comments on that post, then that post ID won't appear once.

Other similar examples in WordPress are listed below:

• Articles and article metadata
• Articles and Users
• Users and user metadata
• Taxonomy and taxonomy projects

There are many more such relationships, too many to list here.

One-to-many relationships involving articles

The most linked data table is wp_posts data table, and almost all links are one-to-many relationships. As in the following table.wp_posts Linked 4 different data tables.

Articles can have one-to-many relationships with other articles, and it is through this relationship that attachments to articles and parent articles are created.

Here's an in-depth look at the many-to-many relationship between articles and articles.

Article to Article

Like mentioned above, in the wp_posts The data stored in a data table can have a one-to-many relationship with itself, however, this relationship is not embodied in the literal sense of article-to-article, but rather article-to-attachment and page-to-page, with attachments and pages also being article types.

The attachment to the article is atwp_posts The data table is organized by post_parentfield, the value of which is the ID of the attached post (a bit convoluted). This is a one-to-many relationship, where an attachment record can only have one post parent field, whereas an article can have many attachments.

Articles can also have a one-to-many relationship, where one page can be the parent of several other pages, and this relationship is stored in the post_parent field, - the value is the parent article's post_id, a parent page can have many child pages, while a child page can have only one parent page.

When considering data relationships in WordPress, posts are not literal posts, but also include pages, attachments and other custom post types.

Article-to-article metadata

Article custom fields stored in his own data wp_postmetaThe data table contains only four fields, namely post_id, meta_id, meta_key and meta_value. an article can contain multiple custom fields, while a custom field can only correspond to one article.

Articles to comments

Comments also have their own data sheet wp_comments.. Each comment can only correspond to one post, while one post can have many comments. And other links to wp_postsAs with the data in the data table, the data table is linked to the article data table using the article ID.

wp_comments The datasheet has 15 fields by default, which are used to hold author, author email address, comment information and review status, and the datasheet is also linked to the wp_users Data Sheet.

Comments also have their own metadata, stored in a separate table.

Articles for users

Each post is appended to the wp_users In the table, by user_id record, the name of the stored field is post_authorfields.wp_posts, which is a one-to-many relationship, where an article can only have one author, and an author can publish multiple articles.

The relationship between articles and authors is not quite the same as the relationship between articles and comments, where an article is one author, multiple articles, and the main table is authors, while comments and metadata is one article, the main table is articles, and multiple comments or metadata.

That's why the article goes through user_id to maintain the link, rather than post_id Fields.

One-to-many relationships beyond the article

In addition to articles, there are 3 one-to-many relationships, two between a comment and his metadata, and one between a user and his metadata.

User-to-User Metadata

WordPress stores additional data about the user in the wp_usermeta In the data table, this table is usually used to store some non-core WordPress data, such as dashboard color scheme, user contact information, and so on.

WordPress users' core data is stored in the wp_users In the datasheet, these two tables are passed through theuser_id fields are connected, a user can have many metadata messages, and a metadata message can only correspond to one user.

Comment-to-comment metadata

The core data for comments is stored in the wp_comments data table, additional data is stored in the wp_commentmeta The data table is used to store some additional information about the comment, such as the source of the comment, the number of likes for the comment created by the comment plugin, etc. This relationship is similar to the relationship between the user and the user's metadata.

Comments to users

The last one-to-many relationship is that between a review and a user, thewp_comments The data table has a user_id Field that stores the user ID of the commenting user when the commenting user is logged in, and is empty when the comment author is not logged in.

Many-to-Many Many-to-Many relationships

The last type of database relationship in WordPress is the many-to-many relationship, which is used only once in WordPrss - taxonomy items (including the default taxonomies and tags, and custom taxonomy items), where a single post can have more than one category, and at the same time, more than one post can be inside a category.

As with other application datasheets, WordPress implements this relationship with an additional datasheet, which is the wp_term_relationships data table, which is linked by the record IDs of each table. wp_postsData sheets and wp_term_taxonomy Data Sheet.

It's actually quite understandable. wp_posts The data table contains the data for each of the files, the wp_term_taxonomy The data table contains the relationship between the article and the taxonomy, including the taxonomy item and its ID, and a many-to-many relationship is established by mapping the article ID to the taxonomy item ID.

In order to create wp_term_taxonomy Records in the data table andwp_posts posts in the datasheet, WordPress in thewp_term_relationshipsA record has been added to the table that contains thepost_id respond in singing term_id (based on)object_id respond in singingterm_taxonomy_id are saved separately in the manner of wp_term_relationships in the table). This means thatwp_term_relationships The table can record many pieces of data for articles as well as many articles for taxonomy items, and this is how the way between articles and categories is established.

Representing it graphically is:

• Article 1 has 1 and 3
• Articles 2 Yes 2
• Articles 3 Yes 4
• Article 4 with 1 and 3

You can also look at the table in a different way, for example, category item 3 contains articles 1 and 4.

Don't get excited. It's not over here. There's a fourth data sheet-- wp_terms This table holds information about the taxonomy items, i.e., the taxonomy name, alias, and description, for each taxonomy item in the wp_terms There is only one record in the datasheet, which means that this table and the wp_term_taxonomy The data table is a one-to-one relationship; in fact, the data from this table can be put directly into the wp_term_taxonomyin the datasheet, but WordPress doesn't do that, probably for reasons of making the data structure clearer.

summarize

As we can see, WordPress has 10 out of 11 data tables that are all related to each other, and the only table that isn't related to any other data table is the wp_optionsdata table, because the table stores data about the site, not the content of the site, about the details of the table, I will take time to analyze for you separately.

Understanding one-to-one, one-to-many, and many-to-many relationships in WordPress can help us better analyze and work with data in plugins and themes.