Every website should connect to database to store and retrieve data or information stored in database that stores information about that website. WordPress is no different and is powered by a MySQL database back end. This database stores all of the data for your website, including your content, users, links, metadata, settings, and more. This chapter covers how data is stored, what data is stored, and how to work with that data in WordPress to help you build amazing websites.
WordPress Database Schema:
The fresh installation of WordPress contains 11 database tables and WordPress achievement itself on being very lightweight and the database is the foundation for this. The database structure is designed to be very minimal yet allow for endless flexibility when developing and designing for WordPress. To understand the database schema, it helps to view a database diagram, WordPress database structure and the tables created during a standard WordPress installation. Keep in mind that plugins and themes have the ability to create custom tables. WordPress Multi-site also creates additional tables so your WordPress database may contain more tables than just the default WordPress tables.
When a new major release of WordPress is launched, a few database changes are usually made. These changes are usually very minor, such as changing a table field data type or removing a field that is no longer in use. Backward compatibility is a major focus for the WordPress development community so any changes made to the database are highly scrutinized and will rarely affect active plugins and themes.
The table structure in WordPress is very consistent. Each table in your database contains a unique ID field, which is the primary key of the table. Each table also contains one or more indexes on fields, which improves the speed of data retrieval when executing queries against the data. As you saw in Chapter 5, each trip through the Loop in a theme is going to generate at least one, and perhaps several, queries to extract posts, pages, and their related metadata or comments.
The most important field in every table is the unique ID field. This field is not always named ID, but is an auto-increment field used to give each record in the table a unique identifier. For example, when you install fresh WordPress, a default post is created titled “Hello world!”. Because this is the first post created in the wp_posts table, the ID for this post is 1.
Each post is given a unique ID that can be used to load post-specific information and can also be used as the joining field against other tables in the database. There is one caveat to this, and that is post revisions, attachments, and custom post types. Each one of these entries is saved as a new record in the wp_posts table, so they each get their own unique ID, which means your published post IDs may not be sequential. For example, your first post may have an ID of 1, whereas your second post may have an ID of 15. It all depends on how many additional entries have been created between each post.
WordPress Database TABLE DETAILS:
11 database tables have been created for WordPress, Following is a list of those tables and details on what data they store:
1. wp_commentmeta — Contains all metadata for comments.
2. wp_comments — Contains all comments within WordPress. Individual comments are linked back to posts through a post ID.
3. wp_links — Contains all links added via the Link Manager section.
4. wp_options — Stores all website options defined under the Settings SubPanel. Also stores plugin options, active plugins and themes, and more.
5. wp_postmeta — Contains all post metadata (custom fields).
6. wp_posts — Contains posts of all types (default and custom post types), pages, media records, and revisions. Under most circumstances, this is the largest table in the database.
7. wp_terms — Contains all taxonomy terms defined for your website, mapping their text descriptions to term numbers that can be used as unique indexes into other tables.
8. wp_term_relationships — Joins taxonomy terms with content, providing a membership table. It maps a term such as a tag or category name to the page or post that references it.
9. wp_term_taxonomy — Defines the taxonomy to which each term is assigned. This table allows you to have categories and tags with the same name, placing them in different named taxonomies.
10. wp_users — Contains all users created in your website (login, password, e-mail).
11. wp_usermeta — Contains metadata for users (first/last name, nickname, user level, and so on).
Each database table has a specific purpose within WordPress. The next section breaks down some of the more common tables and looks at some examples of working with each.