Database Structure and Naming in Venddor IO

Understanding Database Architecture

The database architecture in Venddor IO follows specific conventions for table naming, column structure, and relationship management. Understanding these conventions is essential for effective development and customization.

Defining Table Names

When laying out tables in the Venddor IO database, the naming convention is straightforward:

Primary Entity Tables: If a table holds core data for a particular entity, it is named in the plural form of that entity. So, for a table holding primary details about items in a shop, it would be named 'products'.
Supplementary or Child Entity Tables: If a table is dedicated to auxiliary information or is linked to a subsidiary entity, it's termed as 'entity_suffix'. Here, the suffix is in plural. For instance, a table detailing prices of products will be labeled 'product_prices'.

An intricate relationship among tables is illustrated below:

products - Stands for the fundamental data on products.
product_features - Denotes features applicable to products.
product_feature_variants - Specifies diverse feature variants.
product_feature_variant_descriptions - Indicates the multi-lingual aspects of feature variant descriptions for each language.

For deeper dependencies, only the final suffix in a table's name remains plural.

Utilizing Table Prefixes

At the time of installation, Venddor IO lets you set a unique prefix for all its tables. By default, this prefix is set as 'venddor_'.

When dealing with database functions in Venddor IO, the symbol ?: acts as a placeholder, representing this table prefix in SQL queries. Thus, every SQL query involving a table name should be prefixed with this placeholder ?:.

Here's how you'd implement it:

$items = db_get_array(
    'SELECT * FROM ?:products LEFT JOIN ?:product_popularity USING(product_id)'
);

Defining Primary Key Columns

In Venddor IO, primary and foreign key fields are prefixed with the entity's name for clarity. For instance, the unique primary key field in the 'products' table is labeled 'product_id'. For consistency and clarity when customizing Venddor IO, it's recommended to adhere to this naming convention.

Handling Multi-Language Data

Certain database entities in Venddor IO might possess attributes that vary based on language. A classic case is products having descriptions and titles in multiple languages. Here's how Venddor IO manages such multi-lingual fields:

An auxiliary table is established, typically named 'entity_descriptions' (like 'product_descriptions' for products).
This table will include columns referencing the main entity's primary key, in this scenario, 'product_id'.
A 'lang_code' column, generally of type CHAR(2), will hold language codes.
Subsequent columns in this table store multi-lingual data fields. In our instance, these could be 'product' (product name) and 'full_description'.

The PHP code drives the functioning logic of such tables. Using the 'product_descriptions' as a reference:

For storing details about a fresh product, the DESCR_SL constant's value (indicative of the language selected for product editing) is saved to the 'lang_code' field.
For data retrieval in the client interface, the main table is linked with the multi-language table based on the CART_LANGUAGE constant, reflecting the user-selected language.

Here's a SQL query illustration:

SELECT products.*, product_descriptions.* FROM products
INNER JOIN product_descriptions
    ON product_descriptions.product_id = products.product_id
    AND product_descriptions.lang_code = 'en'

Selection of Table Storage Engine

While Venddor IO predominantly employs MyISAM for its tables, transitioning to InnoDB is an option without hindering Venddor IO's performance. InnoDB offers several superior features compared to MyISAM:

Foreign key constraints
Enhanced indexing in newer MySQL editions
Support for transactions

However, with InnoDB's structure, certain query types might be slower compared to MyISAM. Additionally, migrating to InnoDB might impact data deletion and modification orders in Venddor IO due to the architecture's foreign constraints.

In Venddor IO, PHP code takes charge of updating and deleting interconnected entities. Hence, when implementing cascading data updates and removals, it's advisable to use PHP code.

Avoiding Logic Implementation in Databases

Venddor IO recommends against embedding logic directly in the database. Some servers might not provide the necessary MySQL privileges for triggers or stored procedures. Moreover, any logic integrated into the database can overlap or conflict with what's already defined in PHP code.