In the first part of this series on data in WordPress, I gave an overview of the WordPress database tables, and which tables are used to store what kind of data. In this second part, I’ll outline how WordPress manages the relationships between that data.
As you’ll see, WordPress uses three kinds of data relationship – one-to-one, one-to-many and many-to-many. I’ll look at each of these and what they mean for your WordPress site.
One-to-One Relationships
A one-to-one relationship is the simplest relationship of all – it simply means that one record is related to just one other. Data like this is generally stored within one table (although not always, as we’ll see later on 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
- user ID and username
The list could go on, but the main point is that any two records which are stored in different fields in one row of a table have a one-to-one relationship.
One-to-one relationships, however, are the least interesting kind of database relationship. So without further ado let’s move on to a relationship type used a lot by WordPress – one-to-many.
One-to-Many Relationships
One to many relationship are very common in databases, and are what makes a database more powerful than a ‘flat’ table such as a spreadsheet. These occur when one record has a relationship with more than one other record.
In WordPress, most one-to-many relationships are created by a link between two database tables, using a unique identifier to join the two. So for example, the post_id record will be unique to each post stored in the wp_posts table, but will also be used in the wp_comments table to identify which post each comment was posted to. This means that each post_id value will appear only once in the wp_posts table but could appear multiple times – or not at all – in the wp_comments table.
Some other examples in WordPress include:
- posts and post metadata
- posts and users
- users and user metadata
- taxonomies and taxonomy terms
Again, the list goes on. As this is an important topic in WordPress, let’s take a look at the one-to-many relationships in more detail.
One-to-Many Relationships Involving Posts
The table which is connected to the most other tables is the wp_posts table, and most of the relationships these connections create are one-to-many relationships.
As you can see in the diagram below, the wp_posts table is linked to four other tables:
In addition to this, posts can also have a one-to-many relationship with other posts, in the form of attachments or parent pages.
Let’s take a look at these relationships in more detail.
Posts-to-Posts
As explained 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 isn’t between what you would think of as posts, but instead is between posts and attachments or pages and other pages. After all, attachments and pages are post types.
An attachment 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 is attached (with apologies for the repetition!). This is a one-to-many relationship because each attachment will only have one parent, while each post could have many attachments. This will apply even if you’ve used the Media Manager to add an attachment to another post – only the post to which it was originally added will be stored in the database as its parent.
Posts can also have a one-to-many relationship with other posts when one page is specified as the parent of another. The record is stored against the child page, in the post_parent field – again this will be the post_id of the parent page. This is a one-to-many relationship because parent pages can have many child pages but child pages will only have one parent page.
When thinking about relationships between posts like this, it’s important to remember that when considering WordPress data handling, posts are not what you might normally refer to as posts. They include pages, attachments and other post types, all of which are stored in the wp_posts table.
Posts-to-Post Metadata
Post metadata is stored in its own table wp_postmeta. This table only includes four fields – for the post_id, the meta_id, the key and the value. One post can have many items of post metadata associated with it but each post metadata record will only be linked to one post.
Posts-to-Comments
Comments also have their own table, wp_comments. Each comment will relate to only one post while each post can have multiple comments stored against it. As with all of the other tables linked to wp_posts, the post_id field is used for the link (in the comment_post_id field in wp_comments).
The wp_comments table has 15 fields by default, to store data about the author, the author’s email address, the comment itself and its approval status. It’s also linked to the wp_users table, as we’ll see in the next section.
Comments also have their own metadata, stored in another table, which I’ll come to shortly.
Posts-to-Users
Each post is attached to the wp_users table via the user_id record, which is stored in the post_author field in wp_posts. This is a one-to-many relationship because each post has only one author but each user can be the author of multiple posts.
The relationship between posts and users is different rom that between posts and comments or metadata because its nature is of one user, multiple posts, not one posts multiple comments or mete data records. This is why the user_id field provides the link instead of the post_id field.
One-to-Many Relationships Not Involving Posts
There are also three relationships which are one-to-many and don’t involve posts. Two of these involve metadata – the relationship between comments and their metadata, and users and their metadata.
Users-to-User Metadata
WordPress stores additional metadata about users in the wp_usermeta table. This is generally used for non-standard data about users, such as metadata about superadmins in a multisite installation and metadata about the admin colors a user has selected if these aren’t the default.
Most data on users will be in the wp_users table. The two tables are linked via the user_id field and one user record will have multiple items of metadata associated with it, whereas each item of metadata will only apply to one user.
Comments-to-Comment Metadata
Again, most data about comments is stored in the wp_comments table along with the comment itself, but the wp_commentmeta table is used to store additional metadata such as data created by plugins like Akismet. The relationship is the same as between users and usermeta.
Comments-to-Users
The final one-to-many relationship is between comments and users. The wp_comments table includes the user_id field, which can be used to store the user ID when a logged-in user has posted a comment. Note that this field isn’t mandatory for those sites where users don’t have to be logged in to comment.
Many-to-Many Relationships
The final kind of database relationship is the one-to-many relationship, where the many link goes both ways. This is only used once in WordPress, for taxonomy terms (including categories and tags as these are simply taxonomies). One post can have many terms assigned to it and one term can be assigned to multiple posts.
WordPress creates this relationship in the same way as any other database with manay-to-many database: by using an interim table linking the two tables with the key data. This is the wp_term_relationships table, which links the wp_posts table to the wp_term_taxonomy table.
Note: This table also links the wp_links table to the wp_term_taxonomy table, as links behave in much the same way as posts – I’ll come to links shortly.
This is most easily explained by looking first at the two outlying tables. The wp_posts table contains data on each post, while the wp_term_taxonomy table contains data on each term, including the taxonomy it is in and its ID.
To create a link between a term in the wp_term_taxonomy table and a post in the wp_posts table, WordPress creates a record in the wp_term_relationships table, which includes the post_id and the term_id (stored as object_id and term_taxonomy_id respectively in the wp_term_relationships table). This means that the wp_term_relationships table can include multiple records for each post and multiple records for each term, creating the many-to-many relationship.
The diagram below show how this works:
- post 1 has terms 1 and 3
- post 2 has term 2
- post 3 has term 4
- post 4 has terms 1 and 3
You could also look at it the other way – for example, term 3 relates to posts 1 and 4.
However things don’t stop there. There is a fourth table, the wp_terms table. This holds data about each term, namely the name, slug and description for each term. Each term has only one record in the wp_terms table, so the relationship between that and the wp_term_taxonomy table is actually one-to-one. Theoretically speaking, there’s no reason this data couldn’t be held in the wp_term_taxonomy table, but it isn’t.
A Note on Links
Links, or the blogroll, is a feature of WordPress which is far less prominent than it once was. In fact, since version 3.5 links have been turned off in the admin by default; however, they do have their own table: the wp_links table.
This is very similar to the wp_posts table. After all, links are a content type very similar to posts) and have the same many-to-many relationship with taxonomy terms.
Summary
As we’ve seen, WordPress uses a variety of relationships to link data in 10 of its 11 database tables. The only table I haven’t mentioned here is wp_options, because that isn’t linked to any other tables, as it simply stores data about the site, rather than about content. I’ll cover that in more detail later in this series.
By understanding the one-to-one, one-to-many, and many-to-many relationships in WordPress, that can help you to identify ways to manipulate your data and write custom queries in your theme and/or plugins.
In the next part of this series, I’ll cover content types in more detail, looking at the types of content stored in the WordPress database and the similarities and differences between them.
Understanding and Working with Relationships Between Data in WordPress
Geen opmerkingen:
Een reactie posten