Magento is a powerful e-commerce content management software and offers a multitude of very advanced features in the field of online sales, it is created on the basis of the Zend framework in 2008. It is one of the best CMS e -commerce with today more than 250000 online shops that generate no less than 25 billion dollars each year.
There are 2 editions of Magento a free and open source and other paid. The open-source edition known as Magento Community Edition (CE) is a free version where users are free to modify the software configuration to meet their specific needs. While Magento Commerce Edition known by Enterprise Edition (EE) is the paid version of Magento. It is more efficient and offers significant improvements in performance, customer management, catalogs, commercial animation, etc.
Its main features are: Site Management, Mobile Commerce, Marketing Tools and Promotions, Multi-Linguistics, SEO Optimization, Ordering, Payment, Transport Tracking, Order Management, Customer Service, Customer Area, Product Catalog Management (physical or virtual), URL rewriting, easily creating multiple front offices for a single back office.
Today Magento claims more than 150,000 websites created and thousands of extensions that allow e-merchants to build and customize their shop according to their needs, freeing themselves from technical constraints.
In this article, we will see how to install Magento for windows and reverse engineer its database using ERBuilder Data Modeler.
To install Magento on your windows desktop you have to:
1- Download and install WAMP Server from here: http://www.wampserver.com/
2- Download Magento CE from the offcial website: https://magento.com/tech-resources/download
3- Extract the zip folder and copy the folder to WAMP installation path ‘WWW’ folder
4- Now, run ‘PHPMyAdmin‘ and create a new database e.g. ‘magento’
5- Now, start WAMP localhost and click in the ‘Projects’ section on the folder you extracted before. Magento Installation Wizard’ will start.
After you install and setup Magento, you will need to download and install ERBuilder Data Modeler to reverse engineer Magento database. (Free Trial For 15 days)
After installation, run ERBuilder and go to "Database | Manage connections" to setup a connection to your Magento database.
The project file can be downloaded here.
The Magento data model below is the result of reverse engineering the Magento CE 2.1.3 database using ERBuilder data modeler.
Magento CE 2.1.3 consists of 315 tables. Below is the description of the most important tables with their attributes.
■ eav_attributes table:
This table is the main table, where all the attributes created from Manage Attributes and other magento attributes stored.
● attribute_id: this is the primary key
● entity_type_id: his is a foreign key of table eav_entity_type. This basically contains id of entity type like customer or product or category etc. i.e, this particular attribute belongs to which entity
● attribute_code: this is a unique code used to identity an attribute. Basically, a human understandable unique id other than the primary key
● backend_model this usually has the path of a model class. These model classes are used when saving attribute value in database. Like for example saving of product media images requires some operating to happen before and after save. So those operations are defined in these classes
● backend_type: this specifies that data type of the attribute. Magento has 6 types static, datetime, int, text, varchar. The static type is special. The attributes with static type are stored in main entity table
● backend_table: this is used when attribute values are to be stored in some special table, rather than default Magento tables
● backend_label this has the label of the attribute as shown in admin.
● fontend_model: this class is used to change the display format of the values if required. For example, Mage_Eav_Model_Entity_Attribute_Frontend_Datetime has a getValue() which is used to format dates
● fontend_input: this sets how an attribute should be displayed on frontend. Whether it should be a text or dropdown or date etc
● fontend_label: this is the label used in frontend when the attribute is displayed
● source_model: this is a path to a model, from which values are taken. For example, if attribute type is select. Then the options in the dropdown menu are shown from this class
● is_required: weather is compulsory for admin to fill up this value
● is_user_defined: weather is user-generated attribute or Magento system attributes
● default_value: any default value of an attribute
● is_unique: if the values of these attributes should be unique
● note: any comments or personal note
■ eav_entity_table table:
This table stores the entity type id, of the different entities in Magento. It has many columns, the important columns:
● entity_type_id: this is the primary key of the table, and also used as the foreign key in entity_type_id column in eav_attribute table
● entity_type_code: these are unique codes use to specifies varies entities for wich the EAV table structure is used
● entity_model: the base Magento model class for that entity
● attribute_model: there are attribute_model classes. If no attribute model class is given the default eav/attribute is taken
● entity_table: the based entity database table name, where the required attributes of are entity are stored
■ eav_attribute_set table
This table is used to store the attribute sets we created from Manage Attribute Sets in Magento admin.
● attribute_set_id: this is the primary key of the table
● entity_type_id: this is a foreign key to the table eav_entity_type. This associates the attribute set name to the entity type.
● attribute_set_name: the name of the attribute set
● sort_order: order in which attribute sets are shown in admin.
■ eav_attribute_group table
This table is used to store the group that we create inside an attribute set.
● attribute_group_id: primary key
● attribute_set_id: foreign key of the eav_attribute_set table
● attribute_group_name: name of the group
● sort_order: display order of groups
■ eav_attribute_lable table
This table is used to store the values of labels when we use the multiple store option. We know that magento supports multiple stores. The eav_attribute table only allows us to store values of frontend_lable and backend_lable. So, the labels for other stores are stored in this table.
● attribute_label_id: primary key
● attribute_id: foreign key of eav_attribute table
● store_id: foreign key of store table.
● value: is the actual label
ERBuilder provides all the features you would expect from a professional database modeling and design tool, it’s extremely easy to use and understand. Developed for beginners and expert database designers alike, it is fairly simple to familiarize yourself with.