Where Are WooCommerce Products Stored in Database Schema Structure?

In recent times one of the most common questions asked by WooCommerce developers would be how the products are stored in the database structure.

There can be many reasons for which this question arises from the developers. It could be when they are creating a website for a client and at first, they create a staging website for them and once it is finalized they want to migrate it to the production site. Here, to avoid creating the products again from the admin dashboard, a simpler way is to copy the database table in which the products are stored from the staging website to the production site.

Another situation would be when a developer wants to make some custom changes to the products like adding some additional fields to the products or adding any custom taxonomies for the products, etc.

So to help developers to understand the WooCommerce products database structure or WooCommerce product database schema, we will be discussing the relevant tables in this post. It also includes the tables which store the product categories, tags, product variations, etc.

Relationship Between The WooCommerce Database Schema Tables

Here is a small diagram that explains the relation between the tables:

To start with, the WooCommerce products are mainly stored in two default WordPress tables wp_posts and wp_postmeta. Let us discuss the tables in detail.

wp_posts Database Table

It is one of the most important WordPress tables that stores all the contents like text, revisions, menu items, media attachments, and any custom items. WooCommerce products are one of the custom items know as custom post types. It is stored in the table with post_type as product and product_variation.

The primary key of this table is the ID column which is auto-incremented. This ID is generally referred to as a post id which is used to fetch data for the required post_type.

To fetch the data from the wp_posts table you can use the get_posts() WordPress function which retrieves an array of the latest posts, or posts matching the given criteria. So to fetch the products you can use the below code:

$args = array(
  'numberposts' => 10,
  'post_type'   => 'product'
);
 
$latest_books = get_posts( $args );

wp_postmeta Database Table

This table is an extension to the wp_posts table. It stores all the metadata for the post types. So for post type product, it stores the data like the price, weight, product inventory data, etc. The link between the wp_posts table and the wp_postmeta table is the post id.

To store the data in this table and to fetch the data there are some default WordPress functions available which you can use.

1. add_post_meta() – Adds a meta field to the given post.
2. update_post_meta() – Updates a post meta field based on the given post ID.
3. get_post_meta() – Retrieves a post meta field for the given post ID.
4. delete_post_meta() – Deletes a post meta field for the given post ID.

Apart from the basic data for the custom post types, there are other options available in WordPress like categories and tags which are used to group a similar set of items. So for products also there is an option to add categories, tags, and also different attributes like size, color, etc.

The data for these items are also stored in default WordPress tables like wp_terms, wp_termmeta, wp_term_taxonomy and wp_term_relationships. Let us discuss them in detail:

wp_terms Database Table

The wp_terms table stores categories for posts and pages and tags for posts. The posts here also include the custom post type added. Links related to categories are also present here. The wp_terms table shares a relationship with wp_term_taxonomy and wp_term_relationships table.

For products post type, the type of the product like simple, variable, grouped is also stored in this table. The term_id is used as a unique identifier for each term added.

To retrieve the terms from the database for a given taxonomy, you can use the get_terms() WordPress function.

wp_term_taxonomy Database Table

The wp_term_taxonomy table stores descriptions of categories, tags, and certain links associated with categories. The data in this table is linked to the terms in the wp_terms table using the term_id.

wp_term_relationships Database Table

The wp_term_relationships table helps maintain relationships between the posts and the terms. Like it stores the relationship between the product post and the categories and tags for that product. The object_id is the post id for the product and the term_taxonomy_id is the id linked to the categories and tags in the wp_term_taxonomy table.

wp_termmeta Database Table

This table is an extension to the wp_terms table. It stores all the metadata for terms. Like for categories. it stores additional meta of the product count for each category or any additional option which is added while creating a category.

To store the data in this table and to fetch the data there are some default WordPress functions available which you can use.

1. add_term_meta() – Adds metadata to a term.
2. update_term_meta() – Updates term metadata.
3. get_term_meta() – Retrieves metadata for a term.
4. delete_term_meta() – Removes metadata matching criteria from a term.

This post explains the basic database structure of WooCommerce products. Hope this helps the developers as well as the owners of the website to understand the tables in which the products are stored. Let us know if you have any further queries about it.