product attribute banner

How Product Attribute are Stored in 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 for how they are storing data in the database, so I take a look in core files and study them. I’m playing with WooCommerce from a long time and gone through its table for the numerous number of time but didn’t think that there are developer/users who are looking for such kind of tutorial. So today I decided to put my Product Attribute knowledge into a single piece.

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 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 color 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 singles product field, attributes 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 query then let me know through comments so that I can reply one by one.

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: 24

Related Posts

26 Comments

  5. 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

  9. 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.

  10. 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.

  11. 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)

  12. 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.

  13. 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!

  14. 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.

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

Leave a Reply

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