Web Cash

While reading some forums this morning, I came upon an interesting question. Where is the category information stored in the Wordpress database?

I opened up phpmyadmin, expecting to see a “categories” table. No dice. After a few minutes I figured it out – and I decided it might be a good idea to explain how the Wordpress database is structured.

When I first opened the database, I was impressed with its size. It’s made up of only ten tables. The other CMS I’ve used (CMS Made Simple) uses dozens of tables – so this seemed pretty compact.

Part of how this is done is that things are compressed into the pre-existing tables, rather than given their own tables. As a result you’ve got a more compact, more efficient database structure – but not necessarily the most intuitive structure.

To the right, you’ll see an map of the basic database structure. In the middle is the most important table – wp_posts.

Post Information – wp_posts

This is the heart and sole of your Wordpress blog. It contains the basic information about each post and this is how other information is linked to your posts.

There is a lot of information here, but the major fields are ID, post_content, and post_title. The ID is used to link your post to other fields (like comments), and the content and title fields make up the actual post.

That’s what is read and displayed for the user to read.

Comments – wp_comments

This table is connected to the wp_posts table, and it contains all of the comment and trackback information for your blog.

Each comment has a “comment_post_ID” field – which matches up with the individual ID of the post it is attached to. There are a number of other fields that contain the comment data – like comment_author, comment_author_url, and comment_content.

Category and Tag Information – wp_terms

The original question was, “Where is the category information stored?”

This used to be in a “category” field in the wp_posts table. However, a more efficient way to do this was to create a new table with all the category information and a third table to link a post with a category – since each post can have multiple categories.

Each category and tag is defined in the wp_terms field. This includes the name, an id, and a description. In the wp_terms_taxonomy field, each “term” is defined as either a tag or a category.

Then, the wp_terms_relationships includes two numbers – a term id and a post id. By grabbing every term that uses a post’s id, Wordpress can immediately identify all of the appropriate categories and tags to use.

For performance sake (but not necessarily readability), the tags and categories have been made synonymous in the database. They’re all “terms” – which cuts down on the number of queries Wordpress needs to make to build a page.

Non-sequitors – wp_links, wp_users, and wp_options

The last few tables are non-sequitors. They aren’t attached to the wp_posts table, and they define random things about your blog.

For example, wp_links contains the information about links that are displayed in your “Links” section… if you use it.

The wp_users table identifies the user accounts that are registered for your blog. Chances are that that includes you – and no one else. The wp_usermeta table is attached to this, and it includes some options for each user (like whether or not they use the WYSIWYG editor for writing posts).

The only really important table here is wp_options.

This defines all of the major options of your blog – like the url, the title, the description, etc. Anything you set in the Dashboard is stored in a row in this table.

This also stores all of the plugin and widget information. Wordpress uses a nifty trick to store widgets – it simply serializes them and stores them in a textfield in the database. Then, it can put the widget back together with by deserializing it.

This way a widget can be stored and reconstructed from one database row in one table, rather than creating a whole new table for each widget. It makes the information in the database pretty unintelligible – but it cuts down massively on the number of queries needed to create and use the widgets.

Start Messing Around

The purpose of this, of course, is to understand the database structure so that you can mess around with it yourself. That is, after all, the point of open source programs.

So lurk around in the database a bit. This is very useful information for writing plug-ins that need to interact with the Wordpress database.

Tags: database, plugin, structure, wordpress