Tutorials

Tutorials about HTML, CSS, PHP, Javascript, and Photoshop

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that has been used in the blog.
  • Archives
    Archives Contains a list of blog posts that were created previously.
  • Login

Understanding and Working with Relationships Between Data in WordPress

by in Photoshop
  • Font size: Larger Smaller
  • Hits: 5313
  • 0 Comments
  • Subscribe to this entry
  • Print
5313

In the first part of this seriesOn data in WordPress, I gave an overview of the WordPress database tables, and which tables are used to store what kind of data. I'll outline how WordPress manages the relationships between that data, In this second part

One-to-many and many-to-many, As you'll see, WordPress uses three kinds of data relationship - one-to-one. 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

Are the least interesting kind of database relationship, One-to-one relationships, however. 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.  

Using a unique identifier to join the two, most one-to-many relationships are created by a link between two database tables, In WordPress. The, So for examplePost_idRecord will be unique to each post stored in theWp_postsTable, but will also be used in theWp_commentsTable to identify which post each comment was posted to. This means that eachPost_idValue will appear only once in theWp_postsTable but could appear multiple times - or not at all - in theWp_commentsTable

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 theWp_postsTable, and most of the relationships these connections create are one-to-many relationships

As you can see in the diagram below, theWp_postsTable is linked to four other tables:

Posts can also have a one-to-many relationship with other posts, in the form of attachments or parent pages, In addition to this

Let's take a look at these relationships in more detail

Posts-to-Posts

Data stored in the, As explained aboveWp_postsThis relationship isn't between what you would think of as posts, table can have a one-to-many relationship with other data in the same table; however, but instead is between posts and attachments or pages and other pages. After all, attachments and pages are post types

An attachment stored in theWp_postsTable will have a record in thePost_parentField, 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. In the, The record is stored against the child pagePost_parentField - again this will be thePost_idOf 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

It's important to remember that when considering WordPress data handling, When thinking about relationships between posts like this, posts are not what you might normally refer to as posts. All of which are stored in the, attachments and other post types, They include pagesWp_postsTable

Posts-to-Post Metadata

Post metadata is stored in its own tableWp_postmetaThis table only includes four fields - for thePost_idTheMeta_idThe 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 tableWp_commentsEach 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 toWp_postsThePost_idField is used for the link (in theComment_post_idField inWp_comments)

TheWp_commentsThe comment itself and its approval status, the author's email address, table has 15 fields by default, to store data about the author. It's also linked to theWp_usersTable, as we'll see in the next section

Which I'll come to shortly, stored in another table, Comments also have their own metadata

Posts-to-Users

Each post is attached to theWp_usersTable via theUser_idWhich is stored in the, recordPost_authorField inWp_postsThis 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, not one posts multiple comments or mete data records, multiple posts. This is why theUser_idField provides the link instead of thePost_idField

One-to-Many Relationships Not Involving Posts

There are also three relationships which are one-to-many and don't involve posts. And users and their metadata, Two of these involve metadata - the relationship between comments and their metadata

Users-to-User Metadata

WordPress stores additional metadata about users in theWp_usermetaTable. 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 theWp_usersTable. The two tables are linked via theUser_idField 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 theWp_commentsTable along with the comment itself, but theWp_commentmetaTable 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. TheWp_commentsTable includes theUser_idWhich can be used to store the user ID when a logged-in user has posted a comment, field. 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

Where the many link goes both ways, The final kind of database relationship is the one-to-many relationship. For taxonomy terms (including categories and tags as these are simply taxonomies), This is only used once in WordPress. 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 theWp_term_relationshipsWhich links the, tableWp_postsTable to theWp_term_taxonomyTable.  

Note: This table also links theWp_linksTable to theWp_term_taxonomyTable, 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. TheWp_postsTable contains data on each post, while theWp_term_taxonomyIncluding the taxonomy it is in and its ID, table contains data on each term.  

To create a link between a term in theWp_term_taxonomyTable and a post in theWp_postsWordPress creates a record in the, tableWp_term_relationshipsTable, which includes thePost_idAnd theTerm_id(stored asObject_idAndTerm_taxonomy_idRespectively in theWp_term_relationshipsTable). This means that theWp_term_relationshipsCreating the many-to-many relationship, table can include multiple records for each post and multiple records for each term

The diagram below show how this works:

The posts have the following terms:, In the example above
  • Post 1 has terms 1 and 3
  • Post 2 has term 2
  • Post 3 has term 4
  • Post 4 has terms 1 and 3

Term 3 relates to posts 1 and 4, You could also look at it the other way - for example

However things don't stop there. The, There is a fourth tableWp_termsTable. This holds data about each term, namely the name, slug and description for each term. Each term has only one record in theWp_termsSo the relationship between that and the, tableWp_term_taxonomyTable is actually one-to-one. There's no reason this data couldn't be held in the, Theoretically speakingWp_term_taxonomyBut it isn't, table

A Note on Links

Is a feature of WordPress which is far less prominent than it once was, Links, or the blogroll. Since version 3, In fact. 5 links have been turned off in the admin by default; however, they do have their own table: theWp_linksTable.  

This is very similar to theWp_postsTable. Links are a content type very similar to posts) and have the same many-to-many relationship with taxonomy terms, After all

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 isWp_optionsAs it simply stores data about the site, rather than about content, because that isn't linked to any other tables. I'll cover that in more detail later in this series

By understanding the one-to-one, one-to-many, that can help you to identify ways to manipulate your data and write custom queries in your theme and/or plugins, and many-to-many relationships in WordPress

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, In the next part of this series

Read more: Understanding and Working with Relationships Between Data in WordPress

Seo optimization from 3D Web Design Seo.
0
Trackback URL for this blog entry.

Comments

  • No comments made yet. Be the first to submit a comment

Leave your comment

Guest Tuesday, 24 November 2020

Testimonial

Thank you so much! We are very happy with our new website. It is easy to use and all of our customers tell us, they love it.

Contact Us

  • 13245 Atlantic Blvd. #4352
    Jacksonville, FL 32225
  • 904-240-5823