Skip to main content

Optimizely

Implementation of Custom Tables in Optimizely Configured Commerce

Istock 917895628

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.
    Db Structure
  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.
    Script Property

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:
Script

Table Structure

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 Extensions 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

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Ankit Chitnavis

Ankit Chitnavis is a senior technical consultant at Perficient with hands-on experience across B2B and B2C implementations. He helps the brand deliver a high-performing, customer-centric digital experience using the full capabilities of Optimizely Commerce. His core skills include .NET (C#), MVC, and REST APIs.

More from this Author

Categories
Follow Us