In the previous issue, we have discussed Folder Structure in Magento. Today let’s continue to the latter part of Lesson 3 in Magento Tutorial.
Database System in Magento
Unlike other platforms, Magento uses the database model EAV. The strength of this model is its flexibility to use the property, which is very important to an Ecommerce website. This model will be introduced in detail in Lesson 19.
In this section, we will learn about the database of some main modules of Magento including Customer, Catalog and Sales.
1. Customer
Customer database in Magento is stored under EAV model includes some following tables:
Of which, the table customer_entity is the one storing. Customer_entity table is where to store main information about customer including following fields:
- entity_id: primary key
- email: customer’s email
- create_at, update_at: time created, time last updated for customer
- is_active: active status
- 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 contains value of the attributes categorized by corresponding data type. In this table, these are some noteworthy fields:
- 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.
[yellowbox] Exclusive information: Worried about messy stock? Magento Inventory Management helps you get rid of wasting hours on counting stock and maximize time for selling! [/yellowbox]
2. Catalog
In Magento, the database of module catalog can best express the way to use EAV model. Data of this module is divided in to 2 sections: category and product.
The way to store the category table is similar 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).
Tables catalog_category_product is to show the relationship between product and category, which product is in which category.
Database for product is fairly big, which is necessary for an e-commerce platform such as Magento. Similar to customer, 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. However this is a weakness of Magento when using EAV model. Whenever a new store is created, other two similar tables is autogenerated, which is bad for websites with large number of stores and server which cannot serve memory requirements.
3. Sales
The database for module sales stores necessary information for purchasing including: Quote, Order, Invoice, Shipment, Creditmemo. Unlike Customer and Catalog, tables in module Sales is stored in relationship database model. For a visual illustration, you can download a PDF file at this link:
http://www.magentocommerce.com/?ACT=25&fid=40&aid=9563_38jYypduC0XKiAfzF4Zi&board_id=1
– 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: 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 designed like invoice, containing 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:
- sales_order_status: Information of order status list
- sales_order_tax: Information of cases that tax on the order
- salesrule: Information of conditions, promotion campaigns for each order.
- sales_recurring_profile: …
This is the end of Lesson 3. See you again in the next Magento Open Course lesson from Magestore!
