Business object instances are typically java object representations of rows of a database table.
The addition of following columns to each database table is strongly suggested:
The Object ID is used as a globally unique identifier of each row across all database tables (see caveat below). That is, every row in every table should have a different object value. It is typically defined as a VARCHAR field of 36 characters, and should be named "OBJ_ID". A unique constraint should be applied to the object ID column, but must NOT be part of the primary key.
The object ID value is automatically stored by the framework and/or the database layer.
The value of the object ID is a Globally Unique Identifier (GUID). It is extremely likely that each row will have a unique value, and for practical purposes, we assume that each row will have a unique object ID.
KFS/Rice uses optimistic locking to provide concurrency control. Optimistic locking requires the use of a version number field, named "VER_NBR". On Oracle, the field is defined as a
NUMBER(8,0). On MySQL, the field is defined as a
DECIMAL(8). This column should NOT be part of the primary key.
Optimistic locking helps to prevent updates to stale data and consists of two steps:
The following series of steps demonstrates how optimistic locking works:
The following series of steps demonstrates how optimistic locking prevents concurrency problems.
Below is the definition of the simplified account table.
CREATE TABLE CA_ACCOUNT_T ( FIN_COA_CD VARCHAR(2), ACCOUNT_NBR VARCHAR(7), OBJ_ID VARCHAR(36) NOT NULL, VER_NBR DECIMAL(8) default 1 NOT NULL, ACCOUNT_NM VARCHAR(40), ACCT_FSC_OFC_UID VARCHAR(10), CONSTRAINT CA_ACCOUNT_TP1 PRIMARY KEY(FIN_COA_CD,ACCOUNT_NBR), CONSTRAINT CA_ACCOUNT_TC0 UNIQUE (OBJ_ID) ) -- the chart code in the account table comes from the chart table -- each account row refers to exactly one row in the chart table ALTER TABLE CA_ACCOUNT_T ADD CONSTRAINT CA_ACCOUNT_TR1 FOREIGN KEY (FIN_COA_CD) REFERENCES CA_CHART_T (FIN_COA_CD) -- this is one of the foreign keys for the sub account table. -- Each account may have 0 to many sub-accounts ALTER TABLE CA_SUB_ACCT_T ADD CONSTRAINT CA_SUB_ACCT_TR1 FOREIGN KEY (FIN_COA_CD, ACCOUNT_NBR) REFERENCES CA_ACCOUNT_T (FIN_COA_CD, ACCOUNT_NBR) -- note that even though there is a column for a universal user ID field in the account table (i.e. ACCT_FSC_OFC_UID), -- there is no foreign key to FP_UNIVERSAL_USR_T. This relationship will be mapped in a data dictionary based relationship.