Storing Data in Adobe Commerce
The Adobe Commerce platform records and organizes a wide variety of valuable commerce data across hundreds of tables. This topic describes:
- how that data is generated
- what causes a new row to be inserted into one of the Core Commerce Tables
- how actions such as making a purchase or creating an account are recorded into the Adobe Commerce database
To discuss these concepts, refer to the following example:
Clothes4U is a clothing retailer with both an online and brick and mortar presences. It uses Magento Open Source behind its website to gather and organize data.
catalog\_product\_entity
It is September 22, and Clothes4U is rolling out three new items to its Fall line: Throwback Bellbottoms, Straight Leg Jeans, and V-Neck T-Shirts. A Clothes4U employee opens their Commerce Admin, clicks Add Product, and enters all the information for Throwback Bellbottoms.
Satisfied with all the settings for Throwback Bellbottoms, the employee clicks Save, which inserts the first line below into the catalog_product_entity table. The employee repeats the process, creating another Commerce product for Straight Leg Jeans, and then a third for V-Neck T-Shirt, inserting the second and third lines below into the catalog_product_entity table:
entity\_identity\_type\_idattribute\_set\_idskucreated\_atentity_id– This is the primary key of thecatalog_product_entitytable, meaning every row of the table must have a differententity_id. Eachentity_idon this table can only be associated with one product, and each product can only be associated with oneentity_id- The top line of the table above,
entity_id= 205, is the new row created for “Throwback Bellbottoms.” Whereverentity_id= 205 appears in the Commerce platform, it is referring to the product “Throwback Bellbottoms”
- The top line of the table above,
entity_type_id– Commerce has multiple categories of objects (like customers, addresses, and products to name a few), and this column is used to denote the category into which this particular row falls.- This being the
catalog_product_entitytable, each row has the same entity type: product. In Adobe Commerce, theentity_type_idfor product is 4, which is why all three of the new products created return 4 for this column.
- This being the
attribute_set_id– Attribute sets are used to identify products that have the same of descriptors.- The top two rows of the table are the
Throwback BellbottomsandStraight Leg Jeansproducts, both of which are pants. These products would have the same descriptors (for example, name, inseam, waistline), and therefore have the sameattribute_set_id. The third item,V-Neck T-Shirthas a differentattribute_set_idbecause it would not have the same descriptors as the pants; shirts do not have waistlines or inseams.
- The top two rows of the table are the
sku- These are unique values assigned to each product by the user when creating a product in Adobe Commerce.created_at- This column returns the timestamp of when each product was created
customer\_entity
Shortly after the addition of the three new products, a new customer, Sammy Customer, visits Clothes4U’s website for the first time. Since Clothes4U does not allow guest orders, Sammy Customer must first create an account on the website. Customer enters required credentials and clicks submit, resulting in the following new entry on the customer\_entity table:
entity identity type idemailcreated at2141sammy.customer@gmail.com2016/09/23 15:27:12entity_id- Just like the prior table,entity_idis the primary key of thecustomer_entitytable.- When
Sammy Customercreated an account and the row above was written to thecustomer_entitytable, customer was assignedentity_id= 214. Throughout all tables, the customer identified asentity_id= 214 always refers to the user Sammy Customer
- When
entity_type_id– This column identifies which type of entity is being listed in this table, and functions the same way as it does in thecatalog_product_entitytable- Every row on the
customer_entitytable is a customer, and Commerce defines customers asentity_type_id1 by default
- Every row on the
email– this field is populated by the email that a new customer enters when making their accountcreated_at– This column returns the timestamp for when each user joined
sales\_flat\_order (or Sales\_order if you have Adobe Commerce 2.x
With the account creation finished, Sammy Customer is ready to start making a purchase. On the website, the customer adds two pairs of the Throwback Bellbottoms and one V-Neck T-Shirt to the cart. Satisfied with the selections, the customer moves to checkout and submits the order, creating the following entry on the sales flat order table:
entity idcustomer id**subtotalcreated atentity_id– this is the primary key of thesales_flat_ordertable.- When Sammy Customer placed this order and the row above was written to the
sales_flat_ordertable, the order was assignedentity_id= 227.
- When Sammy Customer placed this order and the row above was written to the
customer_id– This column is the unique identifier of the customer who placed this particular order- The
customer_idassociated with this order is 214, which is Sammy Customer’sentity_idon thecustomer_entitytable.
- The
subtotal– This column is the total amount charged to a customer for the order- The two pairs of “Throwback Bellbottoms” and the “V-Neck T-Shirt” cost $94.85 dollars in total
created_at– This column returns the timestamp for when each order was created
sales\_flat\_order\_item ( or Sales\_order\_item
(if you have Commerce 2.0 or later)
In addition to the single row on the Sales\_flat\_order table, when Sammy Customer submits the order, a row for each unique item in that order is inserted into the sales\_flat\_order\_item table:
item\_idnameproduct\_idorder\_idqty\_orderedpriceThrowback BellbottomsV-Neck T-Shirtitem_id– This column is the primary key of thesales_flat_order_itemtableSammy Customer’s order has created two lines on this table because the order contained two distinct products
name– This column is the name of the productproduct_id– This column is the unique identifier of the product to which this row is referring- The first row above has
product_id= 205 becauseThrowback Bellbottomshave anentity_idof 205 on thecatalog_product_entitytable
- The first row above has
order_id- This column is theentity_idof the order that contains these particular order items- Both rows above have
order_id= 227 because they are both part of the order placed bySammy Customer, which hasentity_id= 227 on thesales_flat_ordertable
- Both rows above have
qty_ordered– This column is the number of units of the product that are included in this specific orderSammy Customer’s order contained two pairs ofThrowback Bellbottoms
price– This column is the price of a single unit of the order item- The
subtotalfromSammy Customer’s order in thesales_flat_ordertable was 94.85, which is the sum of two pairs ofThrowback Bellbottomsat $39.95 each and 1V-Neck T-Shirtat $14.95.
- The