Tables
Overview
Physical database tables are the basis Application Dictionary Data Model is built on. This document discusses the particularities all tables in Etendo ERP must have.
Common Columns
All tables in Etendo must have some common columns. All these columns must be defined as not nullable.
Primary Key
All tables in Etendo have a single column primary key. This column will be automatically populated with a generated UUID therefore the type for this column must be VARCHAR2(32) .
Primary Key column must be named like its table with an ID suffix. Thus, the primary key column for HT Salary table would be HT Salary ID.
This column must be also set as primary key in database, it is not enough with defining it as ID in Application Dictionary.
Client/Organization
As Etendo ERP is a multi client and multi organization application, all data belongs to a client and an organization, so all tables must have these two columns:
- AD_Client_ID
- AD_Org_ID
These columns are a foreign key to AD Client and AD Org tables. So, their types must also be VARCHAR2(32) , 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 CHAR(1) 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 DATE .
- CreatedBy : Indicates the user that created the record. It is a foreign key to AD User so its type is VARCHAR2(32) .
- Upated : It contains the last date and time when the record was modified (or created if no modification was performed later). Its type is DATE .
- UpdatedBy : Indicates the last user that updated the record. It is a foreign key to AD User so its type is VARCHAR2(32) .
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 underscore character (_).
The following table prefixes are used by Etendo 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 |
| 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 Mngmt |
| 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 than 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 EM
plus the DB Prefix of the module the column belongs to. For instance,
EM_MYMODULEDBPREFIX_COLUMNNAME.
Note
The column name must not exceed the 30 characters long, 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
Info
Naming for primary key column is explained in 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 than 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 contraints) 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 C_BPartner_ID because it is a foreign key to C_BPartner.C_BPartner_ID column. This is not possible when there is in the same table more than one column linking to the same table or when adding columns in a different module than the table's one.
Following this naming rule allows to define standard references as TableDir when the column is defined in Application Dictionary.
Naming of Columns and the Data Access Layer
In Etendo, Java classes are generated from the tables definition. A DAL entity is generated from every table defined in the Application Dictionary.
Info
For more information, visit Data Access Layer.
It is important you 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 class , if , int , ...
Info
Here you can find a list of the Java keywords.
Supported Column Data types
DBSourceManager, the utility that Etendo uses to manage database related
operations, supports a subset of the datatypes that Oracle and PostgreSQL
databases support. Below we include the currently supported data types:
| Oracle | PostgreSQL |
|---|---|
| (n)char | char |
| (n)varchar(2) | varchar |
| blob | bytea |
| date | timestamp |
| number | numeric |
| clob | text |
This work is a derivative of Tables by Openbravo Wiki, used under CC BY-SA 2.5 ES. This work is licensed under CC BY-SA 2.5 by Etendo.