Tables

Nomenclature

All tables are accompanied by a unique prefix, defined at the time of installation. This is done to differentiate tables created by the system from those created by the user or a different framework, thus avoiding conflicts. This prefix is ​​generally made up of three letters followed by a hyphen.

Tables are named with the singular name of the corresponding service or subservice, while relational tables are named with the names of both. This allows for quick identification of related services.

So, starting with, the xmp prefix will be used for system tables, and if you want to add the table document, it will be stored as xmp_document. If there is another table, let's say xmp_comment, and you want to establish a relationship between the two, it will be called xmp_document_comment, where the first identifier will be related to the document, and the second to the comment.

Fields

The fields in each table are in English and follow this order:

1. Identifier
2. External identifiers
3. Authorship information
4. Identification data
5. Table-specific data
6. Complementary or auxiliary data

In the case of relational tables, the structure is very similar:

1. Identifiers
2. Identification data

As mentioned above, the order of the identifiers is directly related to the name of the table.

The general order of fields in a normal table is as follows:

Identifier

Primary identifier of the record.

id

A 32-character string used as a unique identifier unique id and therefore the primary unique key. The use of autoincrement has been discarded, since it can cause problems in the case of concurrent use of data (lastinsert). This name is not interchangeable.

External identifiers

Corresponding to foreign keys.

foreign_id

A series of 32-character strings, identifiers, or foreign keys with respect to other tables. They are indicated by the name of the referenced table, followed by the postfix _id. Thus, a key indicated as documentcategory_id indicates that it corresponds to the id of the documentcategory table.

Authorship

This data is especially useful for establishing accountability in the event of an audit. A log record was not chosen since generally only the most recent modification is consulted.

created

Data about the record's creator. This follows the format datetime author:id, where datetime is in the form YYYY-MM-DD HH:MM:SS or the date the record was created, while author is a three-letter string indicating whether the data was created by the system during installation (sup), by the administrator (adm), or by the user (usr), followed by a colon and the corresponding ID. As you can see, in the case of data created by the system, there will only be one id, corresponding to the installation identifier.

modified

Similar to the above, this string indicates who last modified the data and when, and is null when the record is first created.

Internal Identification

This is the operational identification data of the record, no longer used by the system. It is generally only present in normal tables, but not in relational tables.

iid

Internal identifier. Optional. It should preferably be a unique identifier, but it is left to the programmer's discretion. It is not found with the rest of the identifiers, as it is set by the user.

active

Lower system logic, indicating whether the record is active. It allows data to be hidden, like a virtual trash can without having to delete it from the system, which is why it is important that all queries be verified against the active elements.

name

Name of the record.

Table data

The data that accompanies the record and defines its operation. The first is usually found in most cases.

description

Brief description of the record. (optional).

(various)

Table data used for the operation.

Auxiliary data

Optional; they do not affect the operation of the system.

note

Comments or notes about that particular record.

(miscellaneous)

Other auxiliary or complementary data that does not affect the operation.