Exploring WooCommerce Product Attribute Storage in WordPress Database

Wondering how WooCommerce stores the product attributes information in the database? In this tutorial, I have taken a deep drive in WooComerce and database which will give you a brief idea of how product attribute are organized in WordPress Tables.

Whenever I install any new plugin I have a great curiosity as to how they are storing data in the database, so I take a look at core files and study them. I've been playing with WooCommerce for a long time and have gone through its table numerous times but didn't think that there were developers/users who were looking for such kind of tutorial. So I decided to put my Product Attribute knowledge into a single piece today.

A product attribute is a characteristic that defines a particular product and will affect a consumer's purchase decision. WooCommerce uses the WordPress default tables to store the entire product attribute.

Let's take an example, of Woo Product Happy Ninja

Woo Product

In the above product, there are 2 attributes namely Size and Color now let see how it is stored and from where they are fetched.

wp_term_taxonomy

The entire attribute category is stored in this table with a prefix.pa_ For example, if we add Color then it will be stored as:

wp_term_taxonomy data
wp_terms

The entire attribute's value is stored in this table with their name and slug. So for example, if we have Black, Blue, Seal Brown colors then it will be stored as:

wp_terms data
wp_term_relationships

It stores the mapping of the post/product ID with the term ID, So, for example, the above product Happy Ninja has attribute color with values Blue and White then it will be stored as:

product attribute data are stored

wp_postmeta

WooCommerce also keep all the assigned attribute name as a serialized way in postmeta table under meta_key => _product_attributes, like this:

wp-postmeta product data

So in the frontend or single product field, attribute names are displayed from _product_attributes meta_key and the values are displayed from wp_terms.name, (obviously with relational data).

additional_info

The relations table will look like this:

wp table relationship

Hope you all enjoyed this article and if you have any queries then let me know through comments so that I can reply one by one.

If you need extensive guidance on how to manage product categories, tags, and attributes, please refer to the official WooCommerce documentation. Additionally, if you wish to gain a deeper understanding of the database structure of WooCommerce, we recommend checking out our article "Decoding Product Data: Part 1 of the WooCommerce DB Series."

Raunak Gupta

Raunak Gupta

I'm Raunak Gupta, a seasoned software developer with over 9 years of experience in a wide range of programming languages, frameworks, and tools. I started my journey as a WordPress & CakePHP developer in 2014, diving deep into the world of OOPs, Request handling, and SEO. Along the way, I crafted numerous dazzling WooCommerce stores, tamed payment gateways, optimized for full filament functionality, and achieved ultra-low latency for lightning-fast load times. My expertise extends to BI tools, website builders, DevOps, and team leadership. I like to help upcoming developers, so I share my experience through this blog and by assisting fellow developers on Stack Overflow, where I've earned a stellar reputation with over 10k+ points of recognition.

Articles: 29

34 Comments

  1. hello
    i have a website which have more than 15.000 product with more than 2.000 product’s attribute , i think its impossible to import product with csv file or xml file , or its better to say , its impossible to make xml or csv file, whats your idea? what’s a best way? please guide me
    thanks

  2. I really can’t believe how great this site is. Keep up the good work. I’m going to tell all my friends about this place.

  3. Nicе post. I waѕ checking continuously tһis blog and I am
    inspired! Very useful info particularly the final
    рart 🙂 I deal with such info a ⅼot. I used to be looking for this particular info for a very long time.
    Thanks and good luck.

  4. Hii, This was a great article.
    It would be great if you could explain where are the customer details stored in woocommerce database.
    Thanks.

    • Hi Dhyey,
      You can get that from wp_postmeta table use this query to get the details, meta_key value’s are self explanatory
      SELECT * FROM `wp_postmeta` WHERE `post_id` = 123 (replace 123 by your Order ID)

      If you want to know the User ID/Customer ID then use this query
      SELECT * FROM `wp_postmeta` WHERE `post_id` = 123 AND `meta_key` = '_customer_user' (If the value of meta_value is 0 zero then order was placed by Guest)

  5. Where categories and subcategories attributes of a product is stored? I want to store that when an order is placed, I have to keep the info that from which category particular order is.
    Sorry for bad English.

  6. Hello I would like to know that How could I do for the below ones
    color size count
    red small 3
    cyan medium 55
    blue big 2
    Please help me!

  7. Hi, great article, looked at every other table. Im new to woocommerce and wordpress. Is it possible to get all the information in one table? Like the product id, name, price and its attributes? I would like to create a search table based on the attributes and bring up the products in a table which the name could hyperlink to the actual detail product view page?

    • Hi @Nimeshin,

      I won’t say it is not possible, but even you create a table like that it would be in normalization form.

  8. Thanks a lot!!!
    I had problems with renamed Attribut-Slugs which had no values anymore and your article saved me the total backup-process!

  9. Hello,

    This is a great article and I have followed and removed almost all of my headaches.

    Let me give you a little background about my situation first. My customer have a WooCommerce site which need to be imported 350mb xml data (generated by an ERP system) on a weekly basis. The xml file contains 260 products and those are linked +500k variants. +500k variants is insane and I still have a doubt that all those are functional and useful. But my job is to import all of them on the website, not to ask a question 🙂

    I wrote a php script which executes query strings into MySQL database directly since importing this giant file via WP importer is impossible. All of the data properly saved into the above 5 tables. Additionally, attributes combination properly stored in `wp_posts` table as well.

    My only issue left is, product variables change only appears (takes effect) on the product frontend view when I hit the update button in the dashboard. I still confused and couldn’t find a solution for it 🙁

    Could you tell me how can I refresh/regenerate all those variable linking to the product? Or what I could missed during the process?

    Thanks a lot

    • Hi again,

      I found a solution for this issue that clears transient data related to the posts when they need to take changes immediately.

      // Deleting post transients
      $transients = array(
      '_transient_timeout_wc_product_children_',
      '_transient_wc_product_children_',
      '_transient_timeout_wc_var_prices_',
      '_transient_wc_var_prices_',
      '_transient_timeout_wc_related_',
      '_transient_wc_related_'
      );

      foreach ($transients as $option_name) {
      delete_option($option_name.strval($post_id));
      }

      Thanks a lot

    • Hi Emily, of course! The wp_terms table stores attribute values. For instance, if you are selling rings with two attributes – Size and Material, and you sell golden and silver rings ranging from size 16 to 20, then gold, silver, 16, 17, 18, 19 and 20 will be saved in the wp_terms table.

  10. I’ve been working with WooCommerce for a while too. One tip I’d like to share is to keep attribute names consistent for easy management.

Leave a Reply

Your email address will not be published. Required fields are marked *