Primary & Foreign Keys

Hi Everyone,

Acumatica is a advanced ERP system. And when we speak about ERP it is not only about data input, it also about how to store, retrieve and analyze data we have.
Because of this we have very connected tables in Acumatica databas where almost every 3^rd-4^th column is a foreign key to other record. This helps a lot with data analyses.
But how to organize keys better for usage, faster for search and smaller for database storage?

Primary/Foreign Keys Definition

In general we have only 2 ways to define and organize keys: Natural Keys and Surrogate Keys. Each way has own benefits.

Natural keys (Code, CD) A natural key is formed of attributes that already exist in the real world.	Surrogate Keys (Identifier, ID) A surrogate key is a unique identifier for an entity.
Easy to read	Lightweight for storage
Consumes more storage space	Faster Search
More complicated to search	Complicated to remember and read
Reference hard to rename	Reference hard to rename

However in Acumatica we use a combined approach – for many tables we create together 2 keys (Surrogate and Natural, ID and CD). This approach give befits of Natural and Surrogate keys together.

• Faster to search by Surrogate Key
• Cheaper to store Surrogate Key
• Easy to read Natural Key
• Natural key can be renamed without affecting reference by Surrogate key.

Example:

• Inventory Item
  • InventoryID – Int32, Surrogate – Database key, for references
  • InventoryCD – String, Natural – Unique Readable key, for user
• SOLine
  • InventoryID – Int32, Surrogate – Reference to the record that contains natural key. Sub-Select or Join can be used to get Natural Key.

This approach is often used with PXSelectors where reference is done by Surrogate key but Natural key is provided as a reference using SubstituteKey property.

[PXSelector(
	typeof(Search<Product.productID>),
	SubstituteKey = typeof(Product.productCD))]
public virtual Int32? ProductID { get; set; }

As soon as SubstituteKey is specified, selector will do a dynamic conversion from Surrogate to Natural key using sub-selects or addition selects to the original database record. So in DB we always have a reference by surrogate key, but user will be happy to see friendly natural key.

Dynamic conversion back and forth from/to UI is done in the code using FieldSelecting (converting surrogate to natural) and FieldUpdating (converting natural to surrogate) events.

As a side benifit of this approach, we now can rename product and place new value in Product CD, however all past references will be preserved using surrogate key.

Primary/Foreign Keys API

There is a new way to work with keys in 2018R1 version of Acumatica. Primary and Foreign key API that simplifies records selection by defining a special class referenced to key fields.
This approach is not a replacement for IsKey property of DBField Attributes and can be optionally used where needed together with IsKey definition.

API is available after you declare a child class of PrimaryKeyOf or ForeignKeyOf classes. As soon as you define it, you can use their static methods to retrieve record based on values you have.
With this API you don’t need to declare BQL Select every time you need parent or child records. This saves development time and minimize number of potential mistakes.

Selecting Records using Primary Key API:

public partial class SOOrder : PX.Data.IBqlTable
{
    public class PK : PrimaryKeyOf<SOOrder>.By<orderType, orderNbr>
    {
        public static SOOrder Find(PXGraph graph, string orderType, 
            string orderNbr)
	    => FindBy(graph, orderType, orderNbr);
    }
    //...
}

SOOrder item = SOOrder.PK.Find(this, "SO", "SO000001");

Selecting Records using Foreign Key API:

public partial class SOLine : PX.Data.IBqlTable
{
    public class SOOrderFK 
        : SOOrder.PK.ForeignKeyOf<SOLine>.By<orderType, orderNbr>
    { }
    //...
}

//Select the parent record
SOOrder order = SOLine.SOOrderFK.FindParent(this, soLine);

//Select the child records
IEnumerable<SOLine> lines = SOLine.SOOrderFK.SelectChildren(this, soOrder);

You can find more information about Acumatica Primary/Foreign key APIs here.

Hope it helps and have a great development!