I thought I’d post this since I’ve had the opportunity to work in depth with the WordPress database in one of my more recent projects. This ERD is what I came up with through my observation by doing a fresh install and looking at what changed with each post / page / link / category is added.
The documentation I could find in the WordPress codec showed the ERD for version 2.5, and it has changed since.
I’ve omitted the columns of the tables – this is just a simple diagram to give you an overview of the relationships between the tables.
If you understand what ERDs are at all, most of the tables names should be descriptive enough for you to understand what is stored in them, and why they are related to other tables… but I thought the wp_terms_taxonomy and wp_terms_relationships stuff need a little bit more explaination.
So here is what I observed. (I shall go thru all the tables anyway)
Let’s start with the simplest.
- wp_options – WordPress options (blog name, description, permalink settings etc)
- wp_users – For each user you have in the database, you have one row here. Simple yeah? This table stores only the very commonly used fields
- wp_usermeta – To store other information about the user, like your name details, settings, and preferences. These are stored in a meta_key and meta_value pair
- wp_comments – One comment, one row. Each row is linked to one post.
- wp_posts – Stores the bulk of your WordPress data – your posts and pages content
- wp_postmeta – Like usermeta, this table stores additional information for each post, like your custom fields for each post.
- wp_links – Information on the links you have defined in WordPress admin -> Links section
- wp_terms – Stores a list of terms used in the database. A term can be a tag, a link category, or a post category
- wp_terms_taxonomy – Each row here is references a row in wp_terms, and basically it provides information about what this term is used for. In the case, say where you have a link category and a post category with the same phrase, you could have two rows in this table pointing to the same row in wp_terms.
- wp_terms_relationship – This table just links up the wp_posts and wp_links tables to the wp_terms_taxonomy table. Technically, things can go wrong here if you link up the wrong things, e.g. linking up a wp_posts row to a wp_terms_taxonomy row that defines a link category, so beware.
With this database design, the WordPress admin module does a lot of work to make sure the terms, taxonomy and relationships tables do not get messed up. The “three way relationship” you see between wp_terms_relationship, wp_links, and wp_posts is intentional – each wp_terms_relationship references to only EITHER a wp_posts record OR a wp_links record.
For example, it edits and appends a “-2″ to the slug for the term when you edit an existing link category to match an existing post category. However, when you create a new link category with the same name an existing post category, no new terms are created, only new taxonomy. There are still some small bugs in the way this is managed, but I must say the implementation has been fantastic so far!
If you are just starting out developing functionality for WordPress or doing heavy tweaking of codes, I hope this has given you a better overview of how things work in the backend!
