Data Model Fundamentals

Overview

• What you’ll learn: The core database tables, naming conventions, standard column patterns, key entities, and the client-organization data isolation model that form the foundation of every iDempiere system.
• Prerequisites: Lesson 6 – Working with Windows and Tabs
• Estimated reading time: 22 minutes

Introduction

Every enterprise resource planning system is, at its core, a structured collection of database tables and the relationships between them. iDempiere’s data model has been refined over two decades, evolving from Compiere through ADempiere to its current form. Understanding this data model is essential for anyone who wants to go beyond basic data entry and truly comprehend how the system works.

In this lesson, you will learn the naming conventions that make iDempiere’s hundreds of tables immediately recognizable, the standard columns that appear on every table, the key entities that represent fundamental business concepts, and the data isolation model that enables multi-tenant operation. This knowledge forms the foundation for everything that follows, from business partner management to financial posting to custom development.

Table Naming Conventions

iDempiere organizes its tables into logical groups using prefix conventions. The prefix of a table name immediately tells you which functional area it belongs to. Knowing these prefixes allows you to locate tables quickly and understand their purpose at a glance.

Core Table Prefixes

When you encounter an unfamiliar table, the prefix immediately narrows down its functional area. A table starting with M_ relates to material management, one starting with C_ relates to core business processes, and so on.

Line Tables

As discussed in the previous lesson, many header tables have corresponding line tables. The naming convention is straightforward: append Line to the header table name. C_Order has C_OrderLine, C_Invoice has C_InvoiceLine, and GL_Journal has GL_JournalLine.

Custom Table Prefixes

When creating custom tables, you should use your own prefix to avoid conflicts with core tables and with other customizations. Common approaches include using a two-letter company abbreviation (e.g., XX_) or a project-specific prefix. This prefix should align with your custom entity type registered in the Application Dictionary.

Standard Column Patterns

One of the most consistent aspects of iDempiere’s data model is the set of standard columns that appear on virtually every table. These columns enable core system functionality like multi-tenancy, auditing, and soft deletion.

Mandatory Standard Columns

These seven columns are present on every regular data table in iDempiere. The system’s persistence layer (the PO — Persistent Object — framework) automatically manages Created, CreatedBy, Updated, and UpdatedBy, so developers and users never need to populate them manually.

Primary Key Convention

Every table’s primary key column follows a strict naming convention: TableName_ID. The table C_Order has the primary key C_Order_ID. The table M_Product has M_Product_ID. The table AD_User has AD_User_ID. This convention is not merely a suggestion; the Application Dictionary and the Java model layer depend on it for automatic foreign key resolution, link column detection, and reference lookups.

Primary key values are generated from database sequences. Each table has its own sequence (e.g., C_Order_Seq), and the system retrieves the next value when creating a new record.

UUID Columns

In addition to the numeric primary key, iDempiere tables include a UUID (Universally Unique Identifier) column following the pattern TableName_UU. For example, C_Order_UU stores a UUID like a1b2c3d4-e5f6-7890-abcd-ef1234567890. UUIDs serve several important purposes:

• Cross-system identification: Unlike numeric IDs, which are sequence-dependent and can differ between systems, UUIDs are globally unique. This makes them essential for data migration, synchronization, and 2Pack packaging.
• REST API references: When exposing data through web services, UUIDs provide stable, system-independent identifiers.
• Replication: Multi-site environments use UUIDs to match records across servers.

Foreign Key Conventions

Foreign keys in iDempiere follow the same naming pattern as primary keys. If a table has a column named C_BPartner_ID, it is a foreign key reference to the C_BPartner table. This consistency means you can determine the referenced table from any foreign key column name by removing the _ID suffix. The Application Dictionary’s Table Direct reference type exploits this convention to automatically resolve lookups without additional configuration.

Key Entities

iDempiere’s data model includes hundreds of tables, but a core set of entities forms the backbone of the entire system. Understanding these key entities is essential for comprehending how business data flows through iDempiere.

AD_Client: The Tenant

At the highest level, AD_Client represents a tenant in iDempiere’s multi-tenant architecture. Each client is a completely isolated business entity with its own chart of accounts, business partners, products, and transactions. A single iDempiere installation can host multiple clients, each unaware of the others’ data.

Every iDempiere installation has at least two clients:

• System (AD_Client_ID = 0): Contains system-level configuration, dictionary definitions, and shared reference data. Regular users never work directly in the System client.
• Your company client (e.g., AD_Client_ID = 11): Contains your actual business data. This is where users log in and perform daily operations.

AD_Org: The Organization

Within a client, AD_Org represents an organizational unit. Organizations can model divisions, departments, branches, legal entities, or any other structural unit your business requires. The organization hierarchy enables:

• Data segregation: Records can be assigned to specific organizations, restricting visibility.
• Role-based access: Users can be granted access to specific organizations, ensuring they only see relevant data.
• Financial reporting: Organizations can have their own accounting schemas for consolidated or segmented reporting.

Every client has a special organization with AD_Org_ID = 0 (often labeled with an asterisk *). Records assigned to org 0 are considered shared across all organizations within the client. Reference data like payment terms, currencies, and tax rates are typically assigned to org 0.

C_BPartner: Business Partner

The C_BPartner table is one of the most central entities in iDempiere. It represents any external party your organization does business with: customers, vendors, employees, or any combination thereof. A single business partner record can simultaneously be a customer, a vendor, and an employee. Business partners will be covered in depth in the next lesson.

M_Product: Product

M_Product represents items, services, resources, or expenses that can be bought, sold, manufactured, or stocked. Products are categorized by product type (Item, Service, Resource, Expense), and items can be further classified by product category for reporting and accounting. Key related tables include:

• M_Product_Category: Grouping for accounting defaults and reporting.
• M_Product_PO: Purchasing information (vendor, vendor product number, list price).
• M_ProductPrice: Prices on specific price lists.
• M_Storage / M_StorageOnHand: Inventory quantities by warehouse locator.

C_Order: Sales and Purchase Orders

C_Order is the universal order table used for both sales orders and purchase orders. The IsSOTrx flag distinguishes between the two: Y for sales, N for purchasing. This unified design means the same infrastructure handles both sides of the transaction, with the Application Dictionary’s display logic and window configuration adapting the user interface accordingly.

C_Invoice: Invoices

C_Invoice manages both customer invoices (Accounts Receivable) and vendor invoices (Accounts Payable), again using IsSOTrx to distinguish direction. Invoices link to orders, shipments, and payments, forming the complete document chain.

M_InOut: Shipments and Material Receipts

M_InOut handles physical movement of goods, covering both outbound shipments to customers and inbound receipts from vendors. The IsSOTrx flag indicates direction. Each M_InOutLine references the product, quantity, and warehouse locator.

C_Payment: Payments

C_Payment records incoming payments from customers and outgoing payments to vendors. Payments link to invoices through the allocation mechanism (C_AllocationHdr / C_AllocationLine), which supports partial payments, overpayments, and cross-invoice allocation.

The Data Isolation Model

iDempiere’s multi-tenant data isolation is one of its most important architectural features. Understanding this model is crucial for administrators and developers alike.

Three Levels of Data Scope

Data in iDempiere exists at three levels of visibility:

How Data Isolation Works

When a user logs in, they select a client, a role, and an organization. Every database query the system executes automatically includes a filter on AD_Client_ID, and most queries also filter on AD_Org_ID based on the user’s role permissions. This filtering happens at the framework level, deep within the persistence and query infrastructure, so it is transparent to the application code above it.

The practical effect is that:

• A user logged into Client A can never see data belonging to Client B, regardless of SQL queries or API calls.
• A user restricted to Organization “East Division” cannot see records belonging to “West Division.”
• System-level records (Client 0, Org 0) are visible to all clients as read-only reference data.
• Client-wide records (Org 0 within a client) are visible to all organizations within that client.

Shared Data from the System Client

The System client (AD_Client_ID = 0) holds data that is common across all tenants. This includes:

• Application Dictionary definitions (AD_Table, AD_Column, AD_Window, etc.)
• System-level reference data (countries, currencies, languages)
• Default chart of accounts templates

When a new client is created using the Initial Client Setup process, it copies relevant reference data from the System client and creates client-specific versions where needed.

The Organization Hierarchy

Organizations in iDempiere can be structured in a hierarchy using the AD_Tree mechanism. A typical multi-branch company might have:

Company (Client)
├── Headquarters (Org)
│   ├── Finance Department (Org)
│   └── HR Department (Org)
├── East Region (Org)
│   ├── New York Office (Org)
│   └── Boston Office (Org)
└── West Region (Org)
    ├── Los Angeles Office (Org)
    └── San Francisco Office (Org)

Users with access to a parent organization can typically see data from all child organizations, depending on the role’s IsAccessAllOrgs setting and the org access configuration. This hierarchy enables both granular data isolation and consolidated reporting.

Understanding Record IDs

Every record in iDempiere is identified by its numeric ID (the TableName_ID column). These IDs are critical for understanding data relationships. When you see C_BPartner_ID = 119 in an order record, you know it references business partner record 119 in the C_BPartner table.

Important characteristics of record IDs:

• Sequence-generated: IDs are assigned automatically from database sequences. They are not user-configurable.
• System-specific: The same business partner might have C_BPartner_ID = 119 on one server and C_BPartner_ID = 5023 on another. Never hard-code IDs in customizations.
• Reserved ranges: IDs below a certain threshold (typically 1,000,000) are reserved for standard seed data. Custom records use IDs above this threshold.
• Zero means not set: An ID value of 0 typically means “not specified” and may have special behavior (e.g., AD_Org_ID = 0 means shared).

Best practice: When writing SQL queries or customizations, always reference records by their UUID or by a unique key field (like Value) rather than by numeric ID. This ensures your code works correctly across different environments.

Putting It All Together: The Document Flow

To see how these entities work together, consider a simple sales cycle:

1. A Business Partner (C_BPartner) is created as a customer.
2. A Sales Order (C_Order, IsSOTrx='Y') is created for that customer, with line items referencing Products (M_Product).
3. The order is completed, generating a Shipment (M_InOut) that reduces inventory.
4. An Invoice (C_Invoice) is generated from the order or shipment.
5. A Payment (C_Payment) is received and allocated against the invoice.

Each of these documents carries AD_Client_ID and AD_Org_ID, linking them to their tenant and organizational context. The foreign keys (like C_BPartner_ID on C_Order, C_Order_ID on C_OrderLine) weave these entities into a traceable chain that supports reporting, auditing, and business intelligence.

Key Takeaways

• Table prefixes (AD_, C_, M_, GL_, PP_, I_, T_) immediately identify the functional module a table belongs to.
• Seven mandatory standard columns (AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy) appear on every table and enable multi-tenancy, soft deletion, and audit tracking.
• Primary keys follow the TableName_ID convention, and foreign keys use the same name as the referenced table’s primary key.
• UUID columns (TableName_UU) provide system-independent record identification for migration, replication, and web services.
• AD_Client provides tenant isolation; AD_Org provides organizational data segregation within a tenant.
• Data exists at three scopes: System (shared globally), Client-wide (shared within a tenant), and Organization-specific (restricted to an org).
• Key entities (C_BPartner, M_Product, C_Order, C_Invoice, M_InOut, C_Payment) form the document chain that drives all business processes.

What’s Next

In the next lesson, Business Partner Management, you will take a deep dive into the C_BPartner entity, exploring how customers, vendors, and employees are configured, how addresses and contacts are managed, and how business partners connect to the transactional document chain.

公司（用戶端）
├── 總部（組織）
│   ├── 財務部門（組織）
│   └── 人力資源部門（組織）
├── 東區（組織）
│   ├── 紐約辦公室（組織）
│   └── 波士頓辦公室（組織）
└── 西區（組織）
    ├── 洛杉磯辦公室（組織）
    └── 舊金山辦公室（組織）

会社（クライアント）
├── 本社（組織）
│   ├── 財務部門（組織）
│   └── 人事部門（組織）
├── 東部地域（組織）
│   ├── ニューヨークオフィス（組織）
│   └── ボストンオフィス（組織）
└── 西部地域（組織）
    ├── ロサンゼルスオフィス（組織）
    └── サンフランシスコオフィス（組織）