ERP 2.50:Developers Guide/Concepts/DB/Tables

ERP 2.50:Developers Guide

ERP 2.50:Developers Guide/Fundamentals and Concepts ERP 2.50:Developers Guide/Concepts/DB Fundamentals ERP 2.50:Developers Guide/Concepts/DB/Tables ERP 2.50:Developers Guide/Concepts/DB/Constraints and Triggers ERP 2.50:Developers Guide/Concepts/DB/PL-SQL code infrastructure ERP 2.50:Developers Guide/Concepts/DB/PL-SQL code rules to write Oracle and Postgresql code

Index

Contents 1 Introduction 2 Common Columns 2.1 Primary Key 2.2 Client/Organization 2.3 Audit Information 3 Naming conventions 3.1 Tables 3.2 Columns 3.2.1 Modularity 3.2.2 Primary Key Column 3.2.3 Foreign Key Columns 4 Supported Column Data types

Introduction

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

Common Columns

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

Primary Key

All tables in Openbravo ERP 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 HR_Salary table would be HR_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 Openbravo 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 should 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'.
• 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 Openbravo and shouldn't 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
CUS, PD, US, ZZ	personal developments

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 should not exceed the 30 characters long, that includes the "EM_" plus the DB Prefix of the module; for more information see issue 12779.

This restriction also applies for naming constraints, triggers and functions.

Primary Key Column

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. 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 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.

Supported Column Data types

DBSourceManager, the utility that Openbravo 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

---

ERP 2.50:Developers Guide/Concepts/DB Fundamentals | ERP 2.50:Developers Guide/Concepts/DB/Constraints and Triggers