This is the 12th day of my participation in the More text Challenge. For details, see more text Challenge
Understanding and Working with Relationships Between Data in WordPress
Understand and use the relationships between data in WordPress
In the first part of the series, the authors gave an overview of the WordPress database tables and which tables are used to store which data. The second part will cover how WordPress manages relationships between data
As you can see, WordPress uses three types of data relationships — one-to-one, one-to-many, and many-to-many. Here’s a look at each and what they mean for your WordPress site.
One-to-one Relationships
A one-to-one relationship is the simplest relationship – it simply means that one record is related to another. Such data is usually stored in a table (although this is not always the case, as we will see later in this tutorial)
Examples of one-to-one relationships in WordPress include:
- Post ID and Post Content
- Post title and Post Content
- Post ID and Post slug
- Comment ID and comment Content
- The user ID and username
The list could continue, but the point is that any two records stored in different fields in a row of the table have a one-to-one relationship.
However, one-to-one relationships are the least interesting type of database relationship. So let’s move on to one of the most common types of one-to-many relationships in WordPress.
One-to-many Relationships
One-to-many relationships are very common in databases, and they make a database more powerful than “flat” tables such as spreadsheets. This is used when a record is related to multiple other records.
In WordPress, most one-to-many relationships are created by linking two database tables, using unique identifiers to connect the two together. For example, the post_id record is unique for each post stored in the WP_Posts table, but in the WP_Comments table post_id is also used to identify which post each comment was posted to. This means that each post_ID value appears only once in the WP_posts table, but may appear multiple times (or not at all) in the WP_Comments table.
Examples in WordPress include:
- Posts and post the metadata
- Posts and users
- Users and user metadata
- Taxonomies and taxonomy terms
- Taxonomy Terms and term metadata
Again, there could be more to this list. Since this is an important topic in WordPress, let’s take a closer look at the next one-to-many relationship.
A one-to-many relationship with Posts
Posts corresponds to the WP_Posts table, and it’s associated with most tables, and most of the relationships are one-to-many.
In the diagram below, the WP_posts table links these four other tables
In addition, posts can establish one-to-many relationships with other posts, such as attachments or parent Pages.
Posts and PostsPosts-to-Posts
As mentioned above, data stored in the WP_Posts table can have a one-to-many relationship with other data in the same table. However, this relationship is not between articles as is commonly thought, but between articles and attachments or pages and other pages.
After all, attachments and pages are also Post types. Posts, attachments, pages, and Custom Post types are all Posts, just different types
Attachments stored in the WP_posts table will have a record in the post_parent field, which will be the ID of the post to which the attachment was attached (sorry for the duplication!). . This is a one-to-many relationship because each attachment has only one parent Post, and each post may have many attachments. This applies even if the attachment has been added to another post using the media manager — only the post that originally added the attachment is stored in the database as its parent.
Posts can also have one-to-many relationships with other Posts when you specify one page as the parent of another. The record is stored in the post_parent field of the child page with the value post_ID of the parent page. This is a one-to-many relationship because a parent page can have many child pages, but a child page has only one parent page.
When thinking about the relationship between posts like this, it’s important to remember that posts are not what you normally call “posts” when you’re doing WordPress data processing. They include pages, attachments, and other post types, all of which are stored in the WP_Posts table.
Posts and Post metadata —Posts-to-Post Metadata
Post metadata is stored in its own table, WP_PostMeta. This table contains only four fields: post_id, meta_id, key, and value. A POST can have many POST metadata items associated with it, but each POST metadata record will be linked to only one post.
Posts and Comments — post-to-comments
Comments also have their own table, Wp_comments. Each comment is associated with only one post, and each post can correspond to multiple comments. As with all other tables linked to WP_posts, the post_id field is used for linking (the COMMENT_post_id field for WP_comments).
By default, the WP_Comments table has 15 fields to store data about the author, the author’s E-mail, the comment itself, and its approval status. It also links to the WP_Users table, as you’ll see in the next section.
Comments also have their own metadata, which is stored in a separate table, as described later.
Posts and Users – Posts – to – the Users
Each post is appended to the WP_Users table by user_id, which is stored in the POST_AUTHOR field of WP_posts. This is a one-to-many relationship because there is only one author per post, but each user can be the author of multiple posts.
The relationship between post and user is different from the relationship between post and comments or metadata because of the nature of multiple posts per user, rather than multiple comments or metadata records per post. This is why the user_id field, not post_id, provides the link.
A one-to-many relationship for non-posts
There are also three one-to-many relationships that have nothing to do with Posts. Two have to do with metadata: comments and their metadata, and the relationship between users and their metadata.
Users and user metadata —Users-to-User Metadata
WordPress stores the user’s additional metadata in the WP_UserMeta table. Typically used for nonstandard data about users, such as metadata about superAdmins in a multi-site installation, metadata about the administration background color (if not the default) selected by the user.
The user’s data is usually stored in the WP_Users table. The two tables are linked through the user_id field, where a user record has multiple metadata items associated with it; A metadata item applies to only one user.
Comments and comment metadata —Comments-to-Comment Metadata
Again, most of the data for comments is stored in the WP_Comments table, but the WP_Commentmeta table can store additional metadata, such as the creation time in the Akismet plug-in.
The relationship is the same as between users and user metadata.
Reviews and users —Comments-to-Users
The final one-to-many relationship is reviews and users. The WP_comments table includes the USER_ID field, which is used to store the user ID of the logged in user who posted the comment. Note that this field is not mandatory for sites that do not require user login to comment.
Term and term Metadata
The Wp_termMeta table stores metadata for a term. Similarly, there are multiple metadata for a term.
Many-to-many relationships
The post and term
The final type of database relationship is a many-to-many relationship, where the many links go both ways. This was used only once in WordPress: taxonomy terms (includes taxonomy and tags, which are simple taxonomies).
A post can be assigned many terms, and one term can be assigned to multiple posts.
WordPress creates many-to-many relationships the same way other database implementations do: by linking key data in two tables using temporary tables. Wp_term_relationships, which links wP_posts to wP_terM_taxonomy.
Note: This table will alsowp_linksBracelet receivedwp_term_taxonomyTable, because Links behaves much the same as posts – I’ll get to links later.
This is easily explained by looking first at two peripheral tables. The WP_posts table contains data for each post and the WP_TERM_taxonomy table contains data for each term. Wp_term_taxonomy includes “terM_taxonomy_id”, “term_id”, “taxonomy”, “description”, parent, “Term_taxonomy_id” is the same as “term_id” in wp_trems table. “term_id” is unique to the primary key of wp_trems table. It is unique to the combination of “term_id” and “taxonomy”. Term_taxonomy_id is used as term_id, but its meaning should be cleared).
To link a term in the WP_TERM_taxonomy table to a POST in the WP_posts table, WordPress creates a record in the WP_TERM_relationships table. These include post_id and term_id (stored as object_id and term_taxonomy_id in the WP_terM_relationships table, respectively). This means that the WP_TERM_Relationships table can contain multiple records per post and multiple records per term, creating many-to-many relationships.
The following image shows how it works:
In this example, POST has the following terms:
post 1Terms 1 and 3post 2A term 2post 3A term of 4post 4Terms 1 and 3
There’s another way to look at it — for example, term 3 is associated with Posts 1 and 4.
But things didn’t stop there. The fourth table is wp_terms. It holds data for each term, namely the name, slug, and description of each term. Each term has only one record in the WP_TERMS table, so the relationship between the term and the WP_TERM_taxonomy table is actually one-to-one. Theoretically, there’s no reason why you can’t keep data in the WP_TERM_taxonomy table, but that’s not the case.
Before WP4.2, different taxonomy could share a single term, but this caused deletion and other problems, so we fixed this later to share terms split and keep term and Wp_term_taxonomy one-to-one.
Links to illustrate
Link, or Blogroll, is a WordPress feature that is much less prominent than it used to be. In fact, by default starting with version 3.5, links are closed in administrators; However, they do have their own table: the WP_links table.
It is very similar to the WP_Posts table. After all, a link is a content type very similar to post and has the same many-to-many relationship as a taxonomy item (term).
The profile
As we’ve seen, WordPress uses multiple relational links to link data for 11 of the 12 tables. The only one not mentioned is WP_Options, because it does not link to any other tables, and it only stores data about the site rather than content data, which will be covered in more detail later in this series.
Understanding the one-to-one, one-to-many, and many-to-many relationships in WordPress can help you determine how to manipulate data and write custom queries in themes or plug-ins.
The next part of this series will cover content types in more detail, examine content types stored in the WordPress database, and the similarities and differences between the two,