Magento Database Design

Posted: August 26, 2011 in Uncategorized

The Magento database is a very complex one with over 200 tables (250 to be exact) and used a resource.


Resources which are usually defined under any global xml tag of any config file  play the role of managing Magento database connections.

 Magento Models  

All models extand the base Mage_Core_Model_Absract class and this class is very helpful when models save  themselves to the database. How do models save themselves ? Good question I thought you’d never ask ! Well when saving,  the model calls up its own resource singleton and pass itself($this) to the resource’s  save method and thereafter the resource is scripted to collect any values from the models  internal_data array which and prepare an insert or update statement using those values.

EAV Design

The Entity attribute Value design is very different from tradition database designs in that it works in a  vertical modeling instead of a horizontal modeling. This design allows for you to have meta-data about the  attribute which makes it define each attribute in more detailed manner.


Magento defines Entities as being a core thing that is being modeled in other words they are objects that can  be defined like a product which can be defined using attributes and by default Magento comes with 26 entity  types installed. Entities also extend resources. An entity basically works with models to save data to a number of different tables.

Entity Attributes

Entity attributes serve as instructions for saving and loading attribute information and have a lot more  meta-data then a normal object property. They specify as to which table the data will be  saved and are used for displaying information on the frontend.


When working with databases there comes a time when you have to write sql queries to retrieve data from the database, and since we have already discussed entities which are designed to load one item/record at a time, this then means we have a problem since we would have to know the records primary ID value to load it.

So what if you want to search the database to match a criteria ? A simple select statement with a where clause using a join is probably at the tip of your mind right !. Well since we are dealing with entities things are not all cut and dry because not all data that belongs to an entity is stored in one Table, “but that could be solved by the JOIN” you say ! yes it could but another problem to consider is that if we upgrade Magento to a newer version we are not guaranteed it will stay the same and this is the essence of collections .

Collections come in two varieties

  • Resource Collections
  • Entity Collections
AddAttributeToFilter is probably the most useful method of a collection because this method takes an attribute code and a condition (as demonstrated below).
$product = Mage :: getModel(‘catalog/products’) -> getcollection();
foreach($products as $_prod) {
In the example above we have a condition that is a simple string, which can also be an array. You can see in the second line of code that we are using the method  “addAttributeToFilter” which takes the attribute code ‘9999’.
During this Magento tour we will discuss in further details the database, the modules and more.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s