Get Familiar with WordPress Database Structure

Posted on August 17, 2007
Filed Under WordPress Database, WordPress Development |

For those who are interested in WordPress development and want to tap the power of customization, it’s good to learn the inside of WordPress. The database structure in WordPress is quite simple. There are only 10 tables. The following tables are used for WP 2.2:

wp_users
wp_usermeta
wp_categories
wp_posts
wp_postmeta
wp_comments
wp_post2cat
wp_links
wp_link2cat
wp_options

In WP 2.1 or earlier versions, a table “wp_linkcategories” is used to store link categories. Table “wp_link2cat” is new to WP 2.2 or later.

Table wp_users stores all user data. It have the following columns:

ID
user_login
user_pass
user_nicename
user_email
user_url
user_registered
user_activation_key
user_status
display_name

Table wp_usermeta stores additional data related to users. The following columns are in the table:

umeta_id: primary key
user_id: foreign key from wp_users
meta_key
meta_value

Table wp_categories stores categories for posts and links (blogrolls). It has the following columns:

cat_ID: primary key
cat_name: category name
category_nicename: category’s slag name
category_description: category description
category_parent: foreign key of subcategory, 0 if it is top level category
category_count: the count of posts in this category

Table wp_posts is the major table for storing posts and pages. Columns in this table are:

ID: primary key
post_author: foreign key from user table
post_date
post_date_gmt
post_content
post_title: the title of the article
post_category
post_excerpt: optional excerpt enter by author
post_status
comment_status
ping_status
post_password
post_name: the slug name
to_ping
pinged
post_modified
post_modified_gmt
post_content_filtered
post_parent
guid
menu_order
post_type
post_mime_type
comment_count

Table wp_postmeta stores optional meta data for each post. It has 4 columns:

meta_id
post_id
meta_key
meta_value

Table wp_comments stores all comments. The following are the columns in it:

comment_ID: primary key
comment_post_ID: foreign of form post
comment_author: commenter’s name
comment_author_email: email addrress entered by commenter
comment_author_url: web entered by commenter
comment_author_IP: commenter’s IP detected by the system
comment_date: date and time
comment_date_gmt: date and time gmt format
comment_content: the comment
comment_karma: always 0
comment_approved: 1 of approved
comment_agent: the commenter’s borwser detected by the system
comment_type: if is trackback, the type shows ‘trackback’
comment_parent: 0 if no parent
user_id

Table wp_post2cat is the bridging table to resolve many to many relationship between post and cat. It has 3 columns:

rel_id
post_id
category_id

Table wp_links is for blogrolls. The following are the columns:

link_id: primary ley
link_url: blogroll url
link_name: blogroll name
link_image
link_target
link_category: blogroll type foreign key
link_description
link_visible
link_owner
link_rating
link_updated
link_rel
link_notes
link_rss

Table wp_link2cat is the mapping table between blogrolls and categories. It is a new table added to wp 2.2. It has 3 columns:

rel_id
link_id
category_id

Talbe wp_linkcategories is old table in WP 2.0 or earlier. It was dropped in later versions. It has the following columns.

cat_id: primary key
cat_name: name of blogroll type
auto_toggle
show_images
show_description
show_rating
show_updated
sort_order
sort_desc
text_before_link
text_after_link
text_after_all
list_limit

Table wp_options stores Site configure data such as site blog title, all that can be set in admin panel. Plugins configuration data is also stored in it. The following columns are in this table:

option_id
blog_id
option_name
option_can_override
option_type
option_value
option_width
option_height
option_description
option_admin_level
autoload

Comments

Leave a Reply




*
To prove you're a person (not a spam script), type the security word shown in the picture. Click on the picture to hear an audio file of the word.
Click to hear an audio file of the anti-spam word