[Infographic] Magento 1 vs Magento 2 Database Structure

Starting from the early 2015 when our development team was working on the first instance of UB Data Migration tool, we had chance to dive in and explore Magento 2, especially the differences between Magento 1 and Magento 2 database. And we see there are a significant number of changes in the database structure that are worth highlighting.

It took us pretty much of time to update the changes in accordance with Magento 2 development process. However it’s beneficial to our internal product development indeed. And we think this would also be good to anyone new to Magento 2 who wants to become familiar with the key changes in Magento 2. So we end up with the idea to create an infographic that puts those findings together.

The infographic is formatted to give a quick overview of key changes in Magento 2 database structures, compared to Magento 1. Please note the comparison is for quick reference rather than detailing every aspect of the database.

(View graphic in full size)

Feel free to embed and share this infographic on your site, copy and paste the code below:

<img src=”//static.ubertheme.com/infographic/magento-1-vs-magento-2-database-structure-infographic.png” />Magento 1 vs Magento 2 Database Structure -- An infographic by <a href=”https://www.ubertheme.com”>Ubertheme</a>

A short note regarding key changes in Magento CE 2.x database

For your convenience, we include below the significant changes in Magento CE 2.x database that you see in the infographic:

 Websites, Stores, Store Views
 1. The ‘core_website’ table was replaced with ‘store_website’ (The structure was changed)
2. The ‘core_store’ table was replaced with ‘store’ (The structure was not changed)
3. The ‘core_store_group’ table was replaced with ‘store_group’ (The structure was not changed)
Core Product Attributes, Custom Product Attributes
1. A new field ‘is_required_in_admin_store’ was added to the ‘catalog_eav_attribute’ table
2. Two new fields ‘attribute_group_code’ & ‘tab_group_code’ were added to the ‘eav_attribute_group’ table
3. The product attribute ‘msrp_enabled’ was replaced with ‘msrp’. Magento 2 deleted 2 core attributes ‘group_price’, ‘msrp_enabled’
and the ‘group_price’ data was converted to ‘tier_price’
4. The category attribute ‘thumbnail’ was deleted
Categories
1. Structure of tables’ changes:
-- The ‘entity_type_id’ field was deleted
-- Some FOREIGN KEYs were changed
2. The ‘core_url_rewrite’ table was changed to ‘url_rewrite’ and structure was changed.
Products
1. Have more than 44 related tables

2. The structure of tables was changed:
-- The enity_type_id field was deleted
-- Some FOREING KEYs were changed

3. Product Images: A new field ‘enity_id’ was added to the ‘catalog_product_entity_media_gallery_value’ table

4. The catalog_product_index_tier_price table changes:
-- The value_id, all_groups, qty, value fields were deleted
-- The min_price field was added

5. Stock:
+ The ‘website_id’ field was added to the ‘cataloginventory_stock’, ‘cataloginventory_stock_item’ tables
+ The ‘core_url_rewrite’ table was changed to ‘url_rewrite’ (structure was changed)

Customers
1. The ‘customer_entity’, ‘customer_address_entity ‘tables removed the ‘entity_type_id’, ‘attribute_set_id’ fields

2. The ‘customer_address_entity_datetime’, ‘customer_address_entity_decimal’, ‘customer_address_entity_int’, ‘customer_address_entity_text’, ‘customer_address_entity_varchar’, ‘customer_entity_datetime’, ‘customer_entity_decimal’, ‘customer_entity_int’, ‘customer_entity_text’, ‘customer_entity_varchar’ tables removed the ‘entity_type_id’ field

3. The ‘customer_eav_attribute’ table:
-- The ‘is_used_for_customer_segment’ field was deleted
-- Data model value was changed. For instance, the customer/attribute_data_postcode was replaced with Magento\\Customer\\Model\\Attribute\\Data\\Postcode

4. EAV of customer data structure was changed, some attributes were moved to the main table:
-- Some new fields ‘created_in’, ‘firstname’, ‘middlename’, ‘lastname’, ‘password_hash’, ‘rp_token’, ‘rp_token_created_at’, ‘prefix’, ‘suffix’, ‘dob’, ‘default_billing’, ‘default_shipping’, ‘taxvat’, ‘confirmation’, ‘gender’ were added to the ‘customer_entity’ table

5. EAV of customer_address_entity data structure was changed, some attributes were moved from the child tables to the main table `customer_address_entity`:
-- Country_id, firstname, lastname, middlename, street, telephone, city, fax, company, country_id, postcode, prefix, region, region_id, suffix, vat_id, vat_is_valid, vat_request_date, vat_request_id, vat_request_success

6. Magento CE 2.x changed the method of hashing passwords from md5() to sha256(). Magento 2 still supports the option md5() providing that the string ‘:0’ must be appended to the end of ‘password_hash’
Magento 2 also supports CLI command to upgrade all password_hash using md5() to sha256(): php -f bin/magento customer:hash:upgrade

Sale Orders, Sale Quote, Sale Invoices, Sale Payments, Sale Shipments, Bestseller information, Sales Rules & Coupons,
The name of some tables in Sales database structure were changed (removed the “_flat” in the table name)

1. Sales Orders tables changes:
-- The ‘sales_flat_order table’ was replaced with ‘sales_order’ (the structure was changed)
-- The ‘sales_flat_order_address’ was replaced with ‘sales_order_address’ (the structure was changed)
-- The ‘sales_flat_order_grid’ was replaced with ‘sales_order_grid’
-- The ‘sales_flat_order_item’ was replaced with ‘sales_order_item’ (the structure was changed)
-- The ‘sales_flat_order_status_history’ was replaced with ‘sales_order_status_history’
-- The ‘sales_order_status_state’ (the structure was changed)
-- The ‘sales_order_tax_item’ (the structure was changed)

2. Max length changes in the `sales_order` table:
+ ‘store_name’, ‘shipping_method’, ‘x_forwarded_for’ have new max length 32 chars.
+ ‘applied_rule_ids has new max length chars 128
+ ‘weight’ has new max length 12

3. The `sales_order_item` table changes:
+ Magento 2 changed the method to save value of the `weee_tax_applied` field to database: serialize() -> json_encode()
+ ‘weight’, ‘row_weight’ have new max length value 12 chars

4. Sales Quote:
-- The ‘sales_flat_quote’ was replaced with ‘quote’ (the structure was changed)
-- ‘sales_flat_quote_address’ was replaced with ‘quote_address’ (the structure was changed)
-- ‘sales_flat_quote_address_item’ was replaced with ‘quote_address_item’ (the structure was changed)
-- ‘sales_flat_quote_item’ was replaced with ‘quote_item’ (the structure was changed)
-- ‘sales_flat_quote_item_option’ was replaced with ‘quote_item_option’
-- ‘sales_flat_quote_payment’ was replaced with ‘quote_payment’ (the structure was changed)
-- ‘sales_flat_quote_shipping_rate’ was replaced with ‘quote_shipping_rate’

-- In the `quote_address ` table:
+ ‘region’, ‘shipping_method’, ‘city’ have new max length 40 chars
+ ‘firstname’, ‘lastname’, ‘postcode’, ‘telephone’, ‘fax’ have new max length 20 chars
+ ‘country_id’ has new max length 30 chars
+ ‘weight’ has new max length 12 chars
+ ‘address_type’ has new max length 10 chars

5. Sales Payments:
-- ‘sales_flat_order_payment’ was replaced with ‘sales_order_payment’ (the structure was changed)
-- The `sales_order_payment` table has some fields ‘po_number’, ‘cc_number_enc’ with max length 32 chars

6. Sales Invoices:
-- ‘sales_flat_invoice’ was replaced with ‘sales_invoice’ (the structure was changed)
-- ‘sales_flat_invoice_comment’ was replaced with ‘sales_invoice_comment’
-- ‘sales_flat_invoice_grid’ was replaced with ‘sales_invoice_grid’ (the structure was changed)
-- ‘sales_flat_invoice_item’ was replaced with ‘sales_invoice_item’ (the structure was changed)

7. Sales Shipments
-- ‘sales_flat_shipment’ was replaced with ‘sales_shipment’
-- ‘sales_flat_shipment_comment’ was replaced with ‘sales_shipment_comment’
-- ‘sales_flat_shipment_grid’ was replaced with ‘sales_shipment_grid’ (the structure was changed)
-- ‘sales_flat_shipment_item’ was replaced with ‘sales_shipment_item’
-- ‘sales_flat_shipment_track’ was replaced with ‘sales_shipment_track’

8. In the `sales_invoice_item` table, Magento 2 changed the method to save value of the `weee_tax_applied` field to database: serialize() -> json_encode() function.

9. In the `sales_shipment_item` table, the `weight` field has max length 12 chars

10. Sales Credit Memo: Name of tables was changed (the string “_flat” was deleted)

-- The `sales_creditmemo_grid` table: some new fields and value are required in Magento 2 like ‘updated_at’, ‘customer_name’
-- In the `sales_creditmemo_item` table, Magento 2 changed the method to save the value of `weee_tax_applied` field to database: serialize() -> json_encode() function.

11. Sales Rules: Sales Rules Models Class Name changes:
-- The ‘salesrule/rule_condition_product_found’ was replaced with ‘Magento\SalesRule\Model\Rule\Condition\Product\Found’
-- The ‘salesrule/rule_condition_product_subselect’ was replaced with ‘Magento\SalesRule\Model\Rule\Condition\Product\Subselect’
-- The ‘salesrule/rule_condition_product_combine’ was replaced with ‘Magento\SalesRule\Model\Rule\Condition\Product\Combine’
-- The ‘salesrule/rule_condition_product’ was replaced with ‘Magento\SalesRule\Model\Rule\Condition\Product’
-- The ‘salesrule/rule_condition_combine’ was replaced with ‘Magento\SalesRule\Model\Rule\Condition\Combine’
-- The ‘salesrule/rule_condition_address’ was replaced with ‘Magento\SalesRule\Model\Rule\Condition\Address’

12. Magento 2 added new tables for sales data ‘sales_sequence_meta’ and ‘sequece_tables’ (default table in a clean Magento installation). Magento 2 will automatically generate ‘Sequence_tables’ related to ‘sales_sequence_meta’ (for instance: “sequence_invoice_0, sequence_order_0…)

Catalog Rules
Catalog Rule Model Class Name changes:

-- The ‘catalogrule/rule_condition_combine’ was replaced with ‘Magento\CatalogRule\Model\Rule\Condition\Combine’
-- The ‘catalogrule/rule_condition_product’ was replaced with ‘Magento\CatalogRule\Model\Rule\Condition\Product’
-- The ‘catalogrule/rule_action_collection’ was replaced with ‘Magento\CatalogRule\Model\Rule\Action\Collection’

 

Written By

Comments