Arhwen

Introduce Magento Data Storage and EAV

1.Data Storage:

Magento database diagram:(http://www.magentocommerce.com/wiki/_media/doc/magento—sample_database_diagram.png?cache=cache)

how to save data to database:

e.g. a new module named:mymodule,it has a table named mymodule.

a. mymoduleetc/config.xml:

…

…

<global>

<models>

<mymodule>

<class>Bysoft_mymodule_Model</class>

<resourceModel>mymodule_mysql4</resourceModel>

</mymodule>

<mymodule_mysql4>

<class>Bysoft_mymodule_Model_Mysql4</class>

<entities>

<myfaq>

<table>mymodule</table>

</myfaq>

</entities>

</mymodule_mysql4>

</models>

…

…

b. mymodule/model/mymodel.php

…

…

public function _construct()

{

parent::_construct();

//Standard model initialization,

//@param string $resourceModel

$this->_init(‘mymodule/mymodel’);

}

…

…

c.mymodule/model/mysql4/mymodel.php

…

…

public function _construct()

{

// Note that the mymodule_id refers to the key field in your database table.

$this->_init(‘mymodule/mymodule’, ‘mymodule_id’);

}

…

…

d.add this code at where you want to save data.

//Standard model initialization

$saveModel = Mage::getModel(‘mymodule/mymodel’);

$saveModel ->setData($data);

$saveModel ->save();

2.something about EAV:

Entity-Attribute-Value model (EAV), also known as object-attribute-value model and open schema is a data model that is used in circumstances where the number of attributes (properties, parameters) that can be used to describe a thing (an “entity” or “object”) is potentially very 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_entity_type lists all the “kinds of entity” which exist in the system. E.g. customer, catalog_category, catalog_product, order, order_address, order_item, and so on – 25 different kinds.

That doesn’t mean that e.g. there actually is a customer yet. It means that “customer” is a kind of thing that can exist.

Then there are tables whose names finish _entity, such as customer_entity, catalog_product_entity or catalog_category_entity, which say “This is a specific thing that exists”. Each row refers to one entity, e.g. one particular customer or one particular product.

I think these are what the diagram calls “Primary tables” (coloured in the middle one of the three shades of green).

E.g. When a new customer is “created”, a new row gets created in customer_entity.

E.g. in catalog_product_entity, the table includes SKU for each product, and dates created and updated.

E.g. in catalog_category_entity, the table includes a number for the category, which is used elsewhere to refer to it, and dates created and updated.

As far as I’ve seen so far, every table that finishes off _entity has as its first column entity_id, an integer value which seems to be incremented for each new row – so that’s a unique ID for that customer or product or whatever.

(eav_entity has nothing in it yet in my install and I’m not sure what it does – bit puzzled by that one.)

Then there are tables whose job is to describe a relationship between one entity and another entity of a different type. On the diagram, these are a turquoisey blue, and called “Link Tables”.

catalog_product_website

tells which website(s) each product belongs to.

catalog_category_product

tells which category or categories each product belongs to.

An important one of these (I think) is eav_entity_attribute. I get the impression that its main purpose is to tell entities what kinds of attributes they can have, and to tell attributes what kind of entities they can belong to. There are a lot of these relationships – 618 rows on my install so far.

That brings us on to attributes.

An entity can have attributes. An attribute can be set to have a value (for a particular entity).

Attributes also have metadata – data about the data. The metadata might say things like “The value of this attribute is meant to be an image (so make sure the user puts in an image, not text or a number, and store it in the table where we’re storing images)”. Metadata can also say how an attribute should be displayed (e.g. appearing in admin as a drop-down list).

eav_attribute is the list of all possible attributes – 578 pre-created ones, and attribute_id numbers higher than that for the ones you created yourself (if you did create any). (The table also contains lots of true/false variables for what you can do with the attributes – e.g. “is_comparable”. That’s an example of metadata.)

eav_attribute_option_value has all the pre-created individual values of (created?) attributes. If you’ve set up an attribute to use a drop-down list, the ingredients of the drop-down list are all here.

eav_attribute_option includes the sort order of the individual values listed in eav_attribute_option_value, referring to them by their option_id and attribute_id. I imagine one use of this is that when an attribute is wanted, Magento would search here on attribute_id, pull out all the ones matching that id, and thus know both what the possible values were and in what order to display them.

eav_attribute_set has a load of entries where attribute_set_name is Default, and any names (in my case one name so far) of a created “attribute set”.

Wherever you see a table finishing _entity, you’ll probably also see some others with a similar first-part-of-name. E.g. along with customer_entity, you’ve also got customer_entity_datetime, customer_entity_decimal, customer_entity_int, customer_entity_text and customer_entity_varchar.

The diagram calls these “DataType Based Tables” and colours them the palest of the three greens.

This makes sense to me now that I’ve read the bit on Wikipedia about value storage in EAV:

So the tables with names finishing _varchar, _decimal, and so on are the various tables for storing any values which come as those particular types of data. (In the ”catalog_products” table-collection, some tables are evidently meant for storing images too. By way of contrast: customers, orders & some other entity types don’t need images, so don’t have any tables for storing them.)

(I’m not sure yet why we have both _varchar and _text tables, or what criteria were used to determine which attributes go into which of those two tables.)

It’s in the nature of the EAV model that you can mix up values for different attributes in the same table. For each value, another column in the same row tells you “what question it’s answering”, i.e. which attribute it is which currently has this value (for this entity).

For instance, catalog_category_entity_varchar includes both category names and their URL-creation “slugs”. Those are different attributes – but the values for both are suitable for being put into a varchar field, so they both end up in the same table.

If you want to go poking around, take a look in catalog_category_entity_varchar. (Create a category or two first if you haven’t already.) You can probably spot which value is meant to be the category title and which is meant to be a “URL slug” visually anyway, because one probably looks like A Title With Caps and one probably doesn’t (and may have hyphens – though it depends what you personally put in earlier). Then look at the number next to each of those in the attribute_id column. In my install (and I think probably standard, though it doesn’t matter here as this is only an example anyway), all the URL slugs have attribute_id 24 and all the titles have attribute_id 27. And sure enough, if you go and look in the eav_attribute table, attribute_id 24 corresponds to attribute_code “url_key”, and attribute_id 27 corresponds to attribute_code “meta_title”.