Implementation of Custom Tables in Optimizely Configured Commerce

In many B2B commerce implementations, the default features provided by Optimizely Configured Commerce may fall short, particularly when it comes to managing or storing custom data. This blog will outline how to create and work with custom database tables within the platform, ensuring flexibility without altering core system components.

What Are Custom Tables?

Custom tables are user-defined database tables used to hold additional information about products, customers, orders, or other business-specific data. They allow developers to expand the platform’s capabilities without interfering with the existing database schema, which supports better maintainability and easier upgrades.

Why Create Custom Tables?

Here are the key reasons to implement custom tables in Optimizely Configured Commerce:

• Storing metadata or attributes that don’t naturally fit into existing system tables
• Supporting business processes that require unique data structures
• Keeping the core schema that clean while enabling platform customization

Steps to Implement a Custom Table

Creating a custom table involves adding a new table via a SQL script and configuring it to run automatically using the Optimizely bootstrapper.

1. Set Up the Project Structure:
   Begin by creating a folder named DatabaseScript at the root of your Extension project.
   
2. Follow Naming Conventions:
   Use the following format for naming your SQL script files: YYYY.MM.DD.SS.DescriptiveName.sql
   • YYYY = year
   • MM = month
   • DD = day
   • SS = sequence number for that day
     This convention ensures scripts run in the correct order and version control is maintained.
3. Mark Script as Embedded Resource:
   To ensure the bootstrapper can detect and execute your script, set its Build Action to Embedded Resource in the properties panel.
   

Example: Creating a Custom Table for Product Data

Let’s say you want to store extra product details. You could create a table called ProductExtensions using a script named 2025.06.03.01.Table_ProductExtension.sql. To proceed, this file should be added to the DatabaseScript folder and marked as an embedded resource with the following content:

Once you execute the script, the system creates a custom table named Extension.ProductExtension in the database, as shown below:

Guidelines for Custom Table Design

• All custom tables, views, and stored procedures must exist under the Extension schema.
• You can modify and load data into other schemas, but DDL operations are restricted to the Extension schema.
• Each custom table must include a primary key field named Id of type uniqueidentifier with a default of newsequentialid().
• Include audit fields like CreatedOn, CreatedBy, ModifiedOn, and ModifiedBy, all non-nullable and set with proper defaults.
• Add indexes on commonly queried fields like ErpNumber and Id.
• You can define foreign key relationships to dbo schema tables, since the Extensions user has permission to reference them.

Script Execution and Bootstrapper Behaviour

When the application starts, the bootstrapper checks for new database scripts and runs them. It also logs each executed script in the DatabaseScript table. To re-execute a script, however, you must delete both the custom table and its entry in the DatabaseScript table. On the next application start, the system will execute the script again and relog it.

Conclusion

Custom tables in Optimizely Configured Commerce offer a powerful way to tailor the platform to your organization’s unique data and functionality needs, all without compromising the integrity of the core system

Reference: Create custom tables with an entity and WebApi

Source: Read More