How data type is stored in the database

In Codejig Builder, each composite data type is mapped to a separate table in the underlying relational database. Fields within a composite type correspond to columns in the table, and each record in the table represents an instance of the type.

Key Database Concepts:

1. System Fields: Every composite type automatically includes essential fields like id, metaObject, and serialNumber. These fields ensure data integrity and facilitate system operations.

2. System Names and IDs: If a system name is defined for a field or type, it is reflected in the database as the name of the table or column. If no system name is specified, a unique identifier (like field2500008587209705051 or entity2500008587209705067) is used to name columns or tables. System name should include only lower-case English letters and underscore.

3. Persistence and Field Varieties:

   • Regular fields are mapped directly to database columns.
   • Collection fields are treated like relations in a normalized database, referencing another table for storing multiple values.
   • Shared fields retain the same field ID across different composite types but are stored individually in each table.
   • Dynamic fields allow flexibility by holding references to various composite types.

4. Handling of Transient Fields: Transient fields are not stored in the database. They exist temporarily in memory during data processing, making them useful for calculations and temporary data handling.

5. ORM Layer: The system uses an ORM (Object-Relational Mapping) layer to handle database operations, meaning developers do not need to interact directly with SQL queries. Instead, the ORM translates operations performed on composite types (such as saving or querying) into corresponding database actions.

Example:

A composite type like "Employee" with fields such as first_name, salary, and department will have a corresponding table (employee if a system name is defined). Each field maps to a column (e.g., first_name as VARCHAR, salary as DECIMAL), and fields referencing other composite types (e.g., department) are stored as foreign keys pointing to the related tables.

This relational structure makes it easy for developers to manage complex data without dealing directly with SQL. Codejig automates the mapping between composite types and database tables, ensuring that developers can focus on building the application rather than managing database complexities.