In this tutorial, you will learn about the different SQL database tables that make up an Episerver Commerce website and more importantly you will learn what each one does. When we work with Episerver and Episerver commerce, all the data we work with is stored within two back-end databases, one database for CMS data and one for commerce data. There are certain times when building a project where you encounter one of those annoying bugs that don't seem to make any sense. In a few situations after taking a peak in the database, I have managed to unblock myself. If you are trying to debug an Episerver Commerce related issue and you want to learn some tips and tricks to make it easier to figure out what's going on, read on 🔥🔥🔥
When we build projects with Episerver, you should never change the database scheme yourself, or, write custom code that talks to the database directly. Everything should always go via one of the Episerver APIs. Failing to stick to this rule will mean that when you upgrade your site it will likely break. Below I'm going to cover some of the most useful tables that have helped me to unblock myself over the years. The point of this article is to grow and expand over time, so if you want any extra knowledge about the Episerver database, please leave a comment or email me.
Episerver Commerce Database Tables Uncovered
Catalog: This is the table that stores your PIM data. All the data within your product catalogue can be found here. This table contains the top-level data for your product catalogue. It contains things like default currency and default language.
Cls_Contact: This table stores your Episerver commerce admin/editor user details. Look in here to check which accounts exists in the system, the email address specified, the user id. This can be handy when you have to implement your own custom membership provider.
LineItemEx: This is the table that's used to store any additional meta fields that you create on the line item class. Each custom meta field that you add will be displayed in an appropriate column.
OrderGroup: This is the table that's used to store the items specific to the order. It contains the data used with the IOrderGroup interface. In here you will find data like your carts subtotal, currency, market id and total.
OrderGroup_PurchaseOrder: This is where the purchase order information will be stored. If you add any custom fields to the Purchase order, these will also be created within here.
OrderGroup_ShoppingCart: This is the table that stores the data against the Episerver cart. Any custom fields you add to the cart will be displayed in a column in this table.
PaymentMethod: This table lists the available payment providers that are available within your project. The IsActive property is useful if you need to switch a payment gateway on or off.
PromotionInformation: This is the table that's used to store any promotions that are applicable to the cart. In your promotions processor, you return a PromotionsInformation object the data returned by that call is found in this table. Data that is stored in this table includes the promotions SavedAmount, the Description and the IsActive state.
These are not the only tables, however, when debugging an issue, these are the tables that will give you a good feel about what data is being added to the Episerver Commerce catalogue. Happy Coding 🤘
