Magento Database

Magento Database Scheme

At the moment, Magento database schema contains over 230 tables . Most of them are used for EAV -Paradigm which offers advantages on extention and customization of Magento, but makes Magento scheme extremely complicated.

However, the administators and the developer have to deal with Magento database, so the information about it is of utmost importance.

Tables Explanations

core_config_data – configuration of installation parameters is done here.
directory_country_region – regions for countries. If not there, they will not be handled as mandatory fields on the front end automatically.
directory_country_format – country specific address formats.

Useful DB Queries

Unfortunately the build-in reporting engine of Magentio is pretty weak. So if you seriously use Magento in production withouth a back-end ERP system, you are possibly have to code a lot of raw SQL queries to determine the information you need.

Costs and turnover

This SQL Statement list cumulates the mothly nett turnover, variable costs (currently defined costs in your shop). It also calulates your contribution margin and profit margin in percent.

The calculation is also based on invoice date.


SELECT YEAR( invoice.created_at ) AS Year,
 MONTHNAME( invoice.created_at ) AS Month,
 sum( invoice_item.qty_shipped ) AS pieces,
 count(invoice.increment_id) as positions,
 format(sum( invoice_item.row_total ),2) AS Turonover nett,
 format(sum( attr.value * (invoice_item.qty_ordered -qty_refunded)),2) AS costs of product,
 format(sum( invoice_item.row_total - attr.value ),2) AS <span>contribution margin</span>,
 format(sum( invoice_item.row_total - attr.value ) / sum( invoice_item.row_total ) *100,2) AS margin
FROM sales_order_entity invoice,
sales_order_entity_int invoice_int,
sales_flat_order_item invoice_item
LEFT JOIN catalog_product_entity_decimal attr ON (invoice_item.product_id=attr.entity_id)
WHERE invoice.entity_type_id =16
AND invoice_int.entity_id = invoice.entity_id
AND invoice_int.attribute_id=312
AND invoice_item.order_id = invoice_int.value
AND invoice_item.qty_shipped >0
AND attr.attribute_id =64
GROUP BY YEAR( invoice.created_at ), MONTHNAME( invoice.created_at )
ORDER BY invoice.created_at

Magento Database