Data Model

The data model resides in a database of type mySQL or MariaDB.

The created data model is the product of the application definition as entered by the user.

The data model is fully normalised. Tables may have a parent child relationship, and refential integrity is automatically maintained. The parent-child relationship may be nested, with no limit.

    All defined tables in the user interface represent a database table.

    These are the table names that are reserved, which the generator will not permit the user to use :

    • tbl_notifications
    • tbl_notification_types
    • tbl_notification_contacts
    • tbl_notification_recipients
    • tbl_statlog
    • tbl_tracelog
    • tbl_self_registration
    • tbl_credentials_refresh
    • tbl_static_lists

    Every table has five reserved field names that the generator will not permit the user to define :

    • row_id : The integer row identifier, which is auto-incremented.
    • vch_last_created_by : The user name that created the row.
    • vch_last_edited_by : The user name that last edited the row.
    • dt_create_date : The timestamp at which the row was created.
    • dt_last_modified_date : The timestamp at which the row was last modified.

    Each table may may optionally be configured to have a corresponding Audit table, where transaction audits are stored.o

    Audit tables are automatically named are [table-name]_aud.

    Field type mappings.

UI Field Type Database Field Type
number integer
number, precision decimal, precision
varchar varchar
datetime timestamp

    One view is created for every database table.

    Row select for data display is performed against the view, not the table.

    Row select for data search is performed against the view, not the table.

    All insert, update and delete operations are performed using a stored procedure.

    All data inserts and updates use triggers to set the row creation time, row modification time, and the user name that creates or modifies a row.

    Triggers can be overridden and customised, provided the generated trigger code is preserved in the override.