Index

Relational Databases¶

Relational databases are widely used in business applications for storing structured data. They are particularly well-suited for managing large numbers of small, structured entities, where operations typically affect only a small subset of the data without impacting others. Unlike other database types, relational databases necessitate a predefined schema to ensure data integrity and consistency. This schema, managed by the Emakin system, defines the database structure and does not require, nor is it recommended, to be manually altered.

Schema Definition¶

A schema defines the structure of a database. It comprises tables, fields (columns), and the relationships between tables.

Schema Structure¶

A schema is a collection of tables within a database, sharing a common context. Each schema is uniquely identified by a prefix, ensuring differentiation from other schemas within the database. The schema name is integral to database queries.

Table Structure¶

A table is a structured set of rows, each row representing a record. Columns within a table are referred to as fields. Table names must be unique within their respective schema.

Field Definition¶

Fields represent the columns of a table. Each field possesses a name, a data type, and a size (where applicable). Supported data types include:

• Limited Text: Text field with a predefined size limit. Supports multiple languages.
• Integer Number: Integer values (e.g., 4, 10000, -10000).
• Floating Number: Decimal numbers (e.g., 4.5, 10000.5, -10000.5).
• Money: Currency values (e.g., 4.5, 10000.5, -10000.5).
• Yes/No: Boolean values (true/false).
• DateTime: Date and time values (e.g., 2020-01-01 12:00:00).
• Date: Date values (e.g., 2020-01-01).
• Time: Time values (e.g., 12:00:00).
• Unique Identifier: Universally Unique Identifiers (UUIDs) (e.g., 123e4567-e89b-12d3-a456-426614174000).
• Text: Text field with unlimited size.
• XML: XML data.
• Binary: Binary data.

Indexes¶

Indexes significantly accelerate data retrieval. They can be created on one or more fields within a table. Every table inherently includes a primary key index, acting as a unique identifier for each row. Additional indexes can be defined on other fields to further optimize query performance.

Relations¶

Relationships define how tables connect. They facilitate joining tables in queries to retrieve related data. Relationships are established through constraints on fields across tables. These constraints can range from simple equality to more complex multi-field comparisons.

For instance, targeting the Customers table, the following expressions would retrieve mapped data:

• Name -> Customers.Name
• Orders.Amount -> Orders.Amount
• Orders.Product.Name -> Products.Name

For detailed information on query expressions, please refer to the Query documentation.

Schema Sharing and Management¶

Multiple process definitions can contribute to the definition of a relational database schema. Each process definition specifies the tables and fields it utilizes. Upon process deployment, any tables and fields defined in the schema, but not yet present in the database, are automatically created.

Because multiple processes might share a schema, all tables and fields are consolidated into a single, shared schema. This shared schema is accessed by all process definitions referencing it.

Existing schemas can be imported from the database as a starting point. Imported schemas are editable; new tables and fields can be added, but existing ones cannot be deleted.