The Magento Commerce platform records and organizes a wide variety of valuable commerce data across hundreds of tables... but how is that data generated? What exactly causes a new row to be inserted into one of the Core Magento Tables? How are actions such as making a purchase or creating an account recorded into the Magento database? To answer these questions and hopefully many more, let's look at an example:
Clothes4U is a clothing retailer with both an online, and a brick and mortar presence. It uses Magento Community Edition behind its website to gather and organize data.
It's September 22nd, 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 Magento admin panel, clicks the "Add Product" button, 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 new Magento product for "Straight Leg Jeans," and then a third for "V-Neck T-Shirt," inserting the second and third lines below into the
entity_id– This is the primary key of the
catalog_product_entitytable, meaning every row of the table must have a different
entity_idon this table can only be associated with one product, and each product can only be associated with one
entity_id= 205, is the new row created for "Throwback Bellbottoms." Wherever
entity_id= 205 appears in the Magento platform, it will be referring to the product "Throwback Bellbottoms"
entity_type_id– Magento 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.
catalog_product_entitytable, each row has the same entity type: product. In Magento, the
entity_type_idfor product is 4, which is why all three of the new products created return 4 for this column.
attribute_set_id– Attribute sets are used to identify products that have the same of descriptors.
attribute_set_id. The third item, "V-Neck T-Shirt" has a different
attribute_set_idbecause it would not have the same descriptors as the pants; shirts do not have waistlines or inseams.
sku- These are unique values assigned to each product by the user when creating a new product in Magento.
created_at- This column returns the timestamp of when each product was created
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. She enters her credentials and clicks submit, resulting in the following new entry on the customer_entity table:
entity_id- Just like the prior table,
entity_idis the primary key of the
customer_entitytable, she was assigned
entity_id= 214. Throughout all tables, the customer identified as
entity_id= 214 will always refer to the user Sammy Customer
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 the
customer_entitytable will be a customer, and Magento defines customers as
entity_type_id1 by default
created_at– This column returns the timestamp for when each user joined
With her account creation finished, Sammy Customer is ready to start making her purchase. As she navigates the website, she adds two pairs of the "Throwback Bellbottoms" and one "V-Neck T-Shirt" to her cart. Satisfied with her selections, she moves to checkout and submits her order, creating the following entry on the sales_flat_order table:
entity_id– this is the primary key of the
sales_flat_ordertable, the order was assigned
customer_id– This column is the unique identifier of the customer who placed this particular order
customer_idassociated with this order is 214, which is Sammy Customer's
subtotal– This column is the total amount charged to a customer for the order
created_at– This column returns the timestamp for when each order was created
In addition to the single row on the Sales_flat_order table, when Sammy Customer submits her order, a row for each unique item in that order is inserted into the sales_flat_order_item table:
item_id– This column is the primary key of the
name– This column is the name of the product
product_id– This column is the unique identifier of the product to which this row is referring
product_id= 205 because "Throwback Bellbottoms" have an
entity_idof 205 on the
order_id- This column is the
entity_idof the order that contains these particular order items
order_id= 227 because they are both part of the order placed by Sammy Customer, which has
entity_id= 227 on the
qty_ordered– This column is the number of units of the product that are included in this specific order
price– This column is the price of a single unit of the order item
subtotalfrom Sammy Customer's order in the
sales_flat_ordertable was 94.85, which is the sum of 2 pairs of "Throwback Bellbottoms" at $39.95 each and 1 "V-Neck T-Shirt" at $14.95.