Database in Magento 2

Magento 2 database is a highly complex system used to store data pertaining to customers, orders, and configurations. It uses MySQL or MariaDB with hundreds of tables, indexes, and foreign key constraints. It's designed to provide a scalable and high-performance data storage solution that can handle large amounts of data and complex queries.

Before Adobe Magento version 2.3, developers wrote PHP scripts to change database structures. But now, Magento 2 uses XML to write db_shema scripts. The structure of the tables and the foreign-key relationships are mentioned in a file named db_shcema.xml.

The file is located in <Module_Vendor>/<Module_Name>/etc/db_schema.xml.

The following code creates a table named “declarative table”. Each <column /> attribute gives information about the table’s columns. The <constraint /> attribute is used to mention the primary or foreign key constraints.

<schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                 xsi:noNamespaceSchemaLocation="urn:magento:framework:Setup/Declaration/Schema/etc/schema.xsd">
    <table name="declarative_table">
        <column xsi:type="int" name="id_column" padding="10" unsigned="true" nullable="false" comment="Entity Id"/>
        <column xsi:type="int" name="severity" padding="10" unsigned="true" nullable="false" comment="Severity code"/>
        <column xsi:type="varchar" name="title" nullable="false" length="255" comment="Title"/>
       <column xsi:type="timestamp" name="time_occurred" padding="10" comment="Time of event"/>
        <constraint xsi:type="primary" referenceId="PRIMARY">
            <column name="id_column"/>
        </constraint>
    </table>
</schema>

[Source: Magento 2 DevDocs]

When creating a new table, remember to create a db_schema_whitelist.json file. It can be created with the following command:

php bin/magento setup:db-declaration:generate-whitelist
--module-name=VendorName_ModuleName

It is important to regenerate the db_schema_whitelist.json file after adding a new column, removing a column, or renaming the table. The db_schema_whitelist.json file provides a history of all tables, columns, and keys that are added using declarative schema.

Now, one might think regular relational tables (flat tables) are all that will be necessary for a system like Magento 2. But a crucial component of Magento 2 is its extensibility. EAV helps to achieve that in a very clean and elegant way.

EAV model in Magento 2:

Entity-Attribute-Value is a database model that is used to store product data in a flexible way in Magento 2. It is used to store the data of products, customers, orders, and categories.

EAV consists of three types of tables:

1. Entity: The entity table consists of basic information about each entity. The entities in Magento 2 are customer, customer_address, catalog_category, catalog_product, order, invoice, creditmemo, shipment, and rma_item (only available in Adobe Commerce). 
2. Attribute: The attribute table maps to the entity table and stores a certain number of attributes that relate to the entity table. For an entity customer, the attributes could be first name, last name, address, and so on. 
3. Value: This stores the value of the attributes. For the attribute ‘first name’, the value could be ‘John’. Value tables in Magento 2 are stored based on their SQL data types.

Let’s say a product requires a new custom attribute. The traditional way to carry out this task would be to add a new column in the schema. Every time an ‘entity’ requires an extension, you are required to edit tables. Furthermore, not every row of the new extended column has to have value inside it.

A neater way to do this would be to add a new attribute in the attribute table and give values to only the entities that need them. Magento 2 leverages the idea of the EAV model to store multi-store and multi-website data.

Extension attributes in Magento 2:

Another attribute you can use is the extension attribute in Magento 2. They use more complex data types than custom attributes and do not appear in the admin.

To use an extension attribute, you must create <Module>/etc/extension_attributes.xml

<config>
    <extension_attributes for="Path\To\Interface">
        <attribute code="name_of_attribute" type="datatype">
           <resources>
              <resource ref="permission"/>
           </resources>
           <join reference_table="" reference_field="" join_on_field="">
              <field>fieldname</field>
           </join>
        </attribute>
    </extension_attributes>
</config>

The main differences between extension attributes and EAV in Magento 2 are:

1. Usage: Extension attributes are primarily used to add custom attributes to existing data entities, such as products, customers, or orders. Extension attributes allow you to extend the functionality of existing entities without modifying their core structure. EAV is used to handle entities with a large number of attributes that can vary greatly between individual entities of the same type. It is used when you need a highly flexible and dynamic data structure.
2. Querying: Extension attributes are easier to query and access than EAV attributes, as they are stored directly in the entity's main database table. EAV attributes require more complex queries to access the data.
3. Complexity: EAV is more complex than extension attributes and requires additional database tables to store the attribute data.

In general, extension attributes are recommended for simple use cases where you need to add a few additional fields to an existing entity, while EAV is recommended for more complex use cases where you need to store a large number of attributes with varying data types.

That’s it! I hope you got a brief idea about how databases work in Magento 2. This is just the start, and for more clarity, I recommend you read the Magento 2 DevDocs.