Index
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:
- Identifier
- External identifiers
- Authorship information
- Identification data
- Table-specific data
- Complementary or auxiliary data
In the case of relational tables, the structure is very similar:
- Identifiers
- Identification data
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.