Customized database table requirements

If we customize the database schema by creating new tables, we must meet the several requirements to use the staging environment.

• Define a primary key or a unique index.

  The staging environment functions that are based on the key. To avoid logging excessive data in the STAGLOG table, log only the key (primary key or unique index). The stage utilities use the key for compression and to find the data to be propagated. If there is no key, the stage utilities cannot work.

  Rows in staging-enabled tables must be uniquely identifiable by at most five columns: Two columns that contain strings (maximum length: 254 characters) and three columns that contain numbers (maximum length: BIGINT). If our custom table does not have uniquely identifiable rows within these restrictions, modify our custom database table to meet these criteria

• A referential integrity (RI) constraint cycle cannot exist among the tables.

  The staging environment always propagates the parent table before the child table. If there is an RI constraint cycle, the staging environment cannot distinguish between parent and child tables.

• The names of our customized database tables must be lowercase within the STAGLOG database table.

  If the names of our customized database tables include uppercase letters in the STAGLOG table, the staging process can fail to propagate data into our customized tables.

• The database tables contain only configuration data.

  In a business-to-consumer scenario, configuration data is under Site Administrator control, such as catalogs and catalog entries. If a table contains operational data, a customer can change the same table in a production database after a Site Administrator copies the table to the production-ready data. This copying can cause a potential key conflict or an RI constraint violation. The database tables cannot contain any references to operation tables.

  The tables to be propagated should not contain any foreign key references to the primary keys of operation tables. If there is such a reference, the data cannot be restored to the product database if a customer deletes the primary key after the stagingcopy. An insert trigger cannot exist when two tables are being inserted into the production database.

  For any two tables that are covered by the staging environment (for example, R1 and R2), a trigger to insert rows into R1 or R2 cannot exist when we are inserting data into R2 and R1 in the production database. The insert trigger creates the update in both databases and generates key problems.

• The MEMBER table cannot have a unique index.

• Delete restrict on the customized database tables must be used with caution.

  Delete restrict inhibits the database cleanup utility's performance. We can also experience difficulties when we are cleaning the production-ready data. Before we can clean the production-ready data, you have to manually use the database cleanup utility with the force option to clean the tables. Otherwise, cleaning the production-ready data fails.

Note: All foreign key relationships in the custom tables should specify ON DELETE CASCADE

To prepare the staging environment for customized tables, refer to configuring the staging environment for customized tables.

Related tasks
Create triggers for custom tables