Wikiadmin: Halaman baru: Tables dari Etendo Documentation, rebrand Etendo→InfiniteERP

2026-09-20T17:32:53Z

Halaman baru: Tables dari Etendo Documentation, rebrand Etendo→InfiniteERP

New page

{{Languages}}
{{BackTo|Main Page}}

= Tables =

Physical database tables are the basis the Application Dictionary Data Model is built on. This document discusses the particularities all tables in InfiniteERP must have.

== Overview ==

All tables in InfiniteERP must have some common columns. All these columns must be defined as not nullable.

== Common Columns ==

=== Primary Key ===

All tables in InfiniteERP have a single column primary key. This column will be automatically populated with a generated UUID; therefore the type for this column must be <code>VARCHAR2(32)</code>.

The primary key column must be named like its table with an ID suffix. Thus, the primary key column for the <code>HT_Salary</code> table would be <code>HT_Salary_ID</code>.

This column must also be set as primary key in the database; it is not enough to define it as ID in the Application Dictionary.

=== Client/Organization ===

As InfiniteERP is a [[Multi Client and Multi Org|multi-client and multi-organization]] application, all data belongs to a client and an organization, so all tables must have these two columns:

* <code>AD_Client_ID</code>
* <code>AD_Org_ID</code>

These columns are a foreign key to <code>AD_Client</code> and <code>AD_Org</code> tables. So, their types must also be <code>VARCHAR2(32)</code>, and there must be a foreign key to these tables.

=== Audit Information ===

Finally, there are some columns that store information about whether a record is active and when and who created and last modified it. This information is maintained in the following columns:

* '''IsActive''': It is a boolean value (Y/N) indicating whether the record is active or not. Its type must be <code>CHAR(1)</code> and generally, its default value is 'Y'. It is also good practice to create a check constraint forcing its value to be either 'Y' or 'N'.
* '''Created''': It contains the date and time when the record was created. Its type is <code>DATE</code>.
* '''CreatedBy''': Indicates the user that created the record. It is a foreign key to <code>AD_User</code> so its type is <code>VARCHAR2(32)</code>.
* '''Updated''': It contains the last date and time when the record was modified (or created if no modification was performed later). Its type is <code>DATE</code>.
* '''UpdatedBy''': Indicates the last user that updated the record. It is a foreign key to <code>AD_User</code> so its type is <code>VARCHAR2(32)</code>.

== Naming Conventions ==

When creating new tables, it is necessary to pay special attention to the names given to tables and columns, particularly regarding modularity.

=== Tables ===

The only element to take into consideration is the module's '''DB Prefix'''. The table's name must start with this DB prefix followed by an underscore character (<code>_</code>).

The following table prefixes are used by InfiniteERP core and are '''not allowed''' to be used by any modules:

| Table prefix | Description |
| --- | --- |
| A | Asset management |
| AD | Application dictionary |
| C | Core functionality |
| I | Import temporary tables and processes |
| M | Material management |
| FACT | Accounting |
| GL | General ledger |
| MA | Manufacturing |
| MRP | Material resource planning |
| S | Service management |
| AT, AU, EM, FIN, I, MA, R, RV, T | Other Core prefixes |
| CUS, PD, US, ZZ | Personal developments |
| APRM | Advanced Payables and Receivables Management |
| OBUIAPP, NAVBA | User Interface Application |
| OBCHW | HTML Widget |
| OBCLFRE, OBCLKER | User Interface Client Kernel |
| OBKMO | Workspace & Widgets |
| OBCQL | Query/List Widget |
| OBSERDS | JSON Datasource |
| OBJSON | JSON REST Webservice |
| OBUISEL | User Interface Selector |
| OBUISC | Smartclient |
| FINPR | Orders Awaiting Delivery |

=== Columns ===

==== Modularity ====

In case the column belongs to the same module as its table, no special rule must be followed for its name. But if the column is going to be added to a table belonging to a different module, the column name must start with <code>EM_</code> + the DB Prefix of the module the column belongs to. For instance: <code>EM_MYMODULEDBPREFIX_COLUMNNAME</code>.

'''Note''': The column name must not exceed 30 characters, that includes the EM plus the DB Prefix of the module.

'''Info''': In PostgreSQL, all column names must be defined in lower case. This restriction also applies for naming constraints, triggers and functions.

==== Primary Key Column ====

Naming for primary key column is explained in the [[#Primary_Key|Primary Key]] section of this document.

==== Foreign Key Columns ====

It is a best practice to name, if possible, foreign key columns in the same manner as the primary key column of the table they link to. The reason for this is that in Oracle, foreign key (and the rest of the DB constraints) names must be unique at a database-level. So, for example, if we have in our table a column that contains a business partner, it should be named <code>C_BPartner_ID</code> because it is a foreign key to <code>C_BPartner</code>.

Following this naming rule allows to define standard references as <code>TableDir</code> when the column is defined in the Application Dictionary.

==== Naming of Columns and the Data Access Layer ====

In InfiniteERP, Java classes are generated from the tables definition. A DAL entity is generated from every table defined in the Application Dictionary. See [[Data Access Layer]] for more information.

It is important to take this into account when thinking about the names for your columns. The columns you define in a table will correspond to Java properties in a generated Java class. Therefore, '''you must not choose names which collide with Java keywords''', such as <code>class</code>, <code>if</code>, <code>int</code>, etc.

Here you can find a list of the [https://docs.oracle.com/javase/tutorial/java/nutsandbolts/_keywords.html Java keywords].

== Supported Column Data Types ==

'''DBSourceManager''', the utility that InfiniteERP uses to manage database related operations, supports a subset of the datatypes that Oracle and PostgreSQL databases support. Below is the currently supported data types:

| Oracle | PostgreSQL |
| --- | --- |
| (n)char | char |
| (n)varchar(2) | varchar |
| blob | bytea |
| date | timestamp |
| number | numeric |
| clob | text |

== See Also ==

* [[Data Access Layer]]
* [[Application Dictionary]]
* [[Multi Client and Multi Org]]
* [[DBSourceManager]]

== References ==

* [https://docs.etendo.software/developer-guide/etendo-classic/concepts/tables/ Tables — Etendo Documentation]

----

''This page is a derivative of [http://wiki.openbravo.com/wiki/Tables Tables] by [http://wiki.openbravo.com/wiki/Welcome_to_Openbravo Openbravo Wiki], used under [https://creativecommons.org/licenses/by-sa/2.5/es/ CC BY-SA 2.5 ES]. This work is licensed under [https://creativecommons.org/licenses/by-sa/2.5/ CC BY-SA 2.5].''

[[Category:Developers Guide]]
[[Category:Documentation ERP]]

Tables - Revision history

Wikiadmin: Halaman baru: Tables dari Etendo Documentation, rebrand Etendo→InfiniteERP