Friday , January 18 2019
Home >> All tutorial >> Folder Structure and Database System in Magento (part 2)

Folder Structure and Database System in Magento (part 2)

Magento tutorial for beginners : In the part 1, Magento tutorial team have introduce to you the folder structure in Magento in Folders Structure and Database System in Magento (part 1) . To day, we will finish this tutorial by part 2: Magento data base system

Introduce Magento Database System 

There is some differences between Magento with orthers platform such as Shopify, Wo-commerce,  the database model that Magento uses is the Entity–attribute–value model (EAV) . Entity–attribute–value model has the biggest advantage is  its flexibility to use the property, which is very vital to an E-commerce website nowadays.

According Wiki: Entity–attribute–value model (EAV) is a data model to describe entities where the number of attributes (properties, parameters) that can be used to describe them is potentially vast, but the number that will actually apply to a given entity is relatively modest. In mathematics, this model is known as a sparse matrix. EAV is also known as object–attribute–value model, vertical database model and open schema.

Todday, Magento tutorial team will introduce to all of you the database of several main modules of Magento namely: Customer, Catalog and Sales.

1. Customer Database in Magento

As you can see clearly in the following image, Customer database in Magento is keeped under EAV model includes some following tables:

dattabase system

dattabase system

dattabase system

In detail, you can see the table customer_entity is the one storing. Customer_entity table is where to keep every information about the customers including following factors:

  • entity_id: primary key
  • email: the email address of customers
  • create_at, update_at: The detail about time created, time last updated the  customer information.
  • is_active: active status, active or inactive
  • website_id, store_id, group_id: foreign keys linking to corresponding tables: core_website, core_store_id, customer_group.
  • entity_type_id: primary key of table eav_entity_type
  • attribute_set_id: primary key of table eav_attribute_set

Tables  customer_entity_datetime, customer_entity_decimal, customer_entity_int, customer_entity_text, customer_entity_varchar stores value of the attributes categorized by corresponding  data type. In this table, these are some  fields that you have to remember:

  • entity_type_id: primary key of table eav_entity_type
  • attribute_id: primary key of table eav_attribute.
  • entity_id: primary key of table customer_entity

The tables for customer address have a similar storage method with customer_address_entity as the primary table, moreover there is the field parent_id to make a link to table customer_entity.

2. Catalog Database in Magento

In Magento platform, the database of module catalog can best express the way to use EAV model. Data of catalog module is separated in to 2 sections: product and category.

dattabase system
database system

The method to store the category table is as same as to that of customer, the primary table is catalog_category_entity, secondary tables is ones with such prefix as catalog_category. It should be noted from this table that parent_id is to store information about the relationship between categories (parent category).

The role of Tables catalog_category_product is to illustrate how is product and category related with each other, with its help, you can see which product is in which category.

Database for product has a large amount of data, product database is very important for an e-commerce platform like Magento, Shopify, Wo-commerce. Similar to customer database, the primary storing table is catalog_product_entity including the following tables:

  • entity_id (primary key)
  • create_at, update_at: time created, time last updated for customer
  • entity_type_id: primary key of table eav_entity_type
  • attribute_set_id: primary key of table eav_attribute_set
  • type_id: product type
  • sku: product code

Tables with prefix catalog_product_entity store value of product attributes.

Tables catalog_product_link express the relationships between products, in which relationship types are stored in catalog_product_link_type, the values of relationship attributes are stored in the other table with corresponding prefix.

Tables catalog_product_option contains information of options that product may have. These table is linked to catalog_product_bundle_option

Table catalog_product_website shows the relationship between product and website, product in Magento is created by separate websites.

Table catalog_product_index….

Besides, there are tables  catalog_category_flat_store_x, catalog_product_flat_1 (x=1,2,3….) to show the relationship between category, product with store. But, in my opinion, this is a disadvantage of Magento when using EAV model. Whenever you create a new store, other two similar tables is created automatically, This feture is not good for websites with big number of server and stores which cannot serve memory requirements.


3. Sales Database in Magento

The module sales database saves necessary information for purchasing process including: Shipment, Quote, Order, Invoice, Credit memo. There is a difference from Customer and Catalog in Sales database, tables in module Sales is stored in relationship database model. For a easy way to understan, you can download a PDF format here:


The detail information about each file in Sales modules:

–      Quote: Contains order information when a customer or admin creates a shopping cart. Table sales_flat_quote is primary table. Tables sales_flat_quote_address, sales_flat_quote_item, sales_flat_quote_payment store detailed information about order address, items in the order, payment methods.

–      Order: Contains order information after the customer confirms the order. Similar to Quote, primary table is sales_flat_order, secondary table store other necessary information.

–      Invoice: This is place for storing invoice information after the order is processed. Primary table is sales_flat_invoice, table sales_flat_invoice_item stores detailed information of the items in the order, table sales_flat_invoice_grid stores necessary information for report and analysis.

–      Shipment & Creditmemo: These table is built like an invoice, including order information after admin process (shipment or cancellation) primary table is sales_flat_shipment, sales_flat_creditmemo. The system details are like that of Invoice.

–      There are also these tables that you need to know:

  • sales_order_status: Contain Information of order status list
  • sales_order_tax: Contain Information of  cases that tax on the order
  • salesrule: Contain  Information of conditions, promotion campaigns for each order.
  • sales_recurring_profile: …

Thank you for follow our Magento tutorial for beginners, continue subscribe to receive latest Magento tutorial from us.^^

Here is the library of Magento Download for FREE (all versions)

Your Magento website needs a powerful Magento ERP for stock control.


magento erp

About Champ

Heap | Mobile and Web Analytics