The Compiere data model is consistently based on the principle of surrogate or anonymous key. As an example, the product table (called "M_Product") has the column "M_Product_ID" as the primary key. The primary key is a unique number generated based on a sequence table. For entities maintained by Compiere (e.g. dictionary), these IDs are below one million whereas the ID for normal entries are above one million.
The user key value for the product is maintained in the column "Value" (40 unicode characters) and the product table has other standard columns as "Name" (60 characters), "Description" (255) and "Help" (2000). You will find the columns Value (or DocumentNo), Name, Description and Help in most of the tables.
The standard columns are the primary key " _ID" (e.g. M_Table_ID), the tenant "AD_Client_ID" and organization "AD_Org_ID", the active flag "IsActive", the time, the record was created and last updated "Created", "Updated" and the user who created the record and updated it - "CreatedBy" and "UpdatedBy". The latter are foreign keys to the user table AD_User and it's primary key AD_User_ID. The user key "Value" is unique for a tenant (supported by a unique key) and could be used for the product number or a harmonized product name for easier search.
In addition to this, UPC (or EAN) and SKU can be used as alternative user keys. In this example, they are not unique and supported by an index. The user can easily make UPC and/or SKU unique by switching the flag on the index.
The IDs are not directly displayed. You find the record count in the lower right and corner of a window, e.g. "1/6" (first of the total of 6 records) or [1/3] (first of the 3 queried records). When you click on it, the Record Info is displayed - among the information who created and last updated the record and when - you find here the table name, the key column and its value, e.g. (M_Product - M_Product_ID=103).
The other information displayed in the Record Info are all the changes to the record, if the change log feature is enabled. In reports, just add the key column to the report. You can also search for a record with a specific ID. For that, click on the search and go to the "Advanced" tab. In the Column selection, you find the ID column identified, e.g. "Product (ID)".
One of the advantages in Compiere is that when writing reports, you do not have to de-reference the foreign keys. When you create a special order report, you create a view with the information required - for product just M_Product_ID.
What is then actually displayed are the "Identifier" columns - for product, it is the name, but you could change that to the user key (column Value) or even a combination of Name, SKU and Vendor. You select the columns to be displayed in the window "Table and Column" be selecting the identifier flag and selecting in what sequence the field should be displayed for that record.
During the version migration, Compiere ignores all IDs over one million (these are user defined) and compares the IDs of the Dictionary and Compiere entity type. This way, we can identify changes and update the target system to the new version of the dictionary.
It is a bit trickier for the component migration, as a Component or Application does not have a pre-defined ID value range. Here Compiere uses a "Table Unique ID" to identify the record. For columns, it is the ColumnName, which is supported by a unique constraint. Rather than comparing the numeric IDs, the Table UID is used for comparison and with that independent from the actual (local) IDs.
The data migration (under development) goes one step further. For the Product, the Table UID is the Tenant "AD_Client_ID" and the user key value "Value", which is enforced by a unique constraint. When exporting product information, the user needs to select from what Tenant to export the data and to what Tenant the data should be imported.
Note that the version migration and part of the data migration is available in version 3.0.3. The next release will include data migration. If you have questions, please contact me.
