Learn how your database tables are organized, how to create or delete tables, how to change the structure of tables, and finally how to move data into and out of tables.
Database Table Organization
The database tables available for your use are organized into two schemas, or folders:
- A schema named 'x',
which is completely under your control. You can create/edit/delete as many custom tables and views as you like. Once added, these custom tables or views can be used as a data source for option filters, auto-complete fields, pricing rules, and custom queries written in Snap rules. These tables and views will show up automatically in theses sources after being added. - A second schema named 'dbo',
which is read-only. The tables found here can be used to learn more about the status of Quotes, the last login of Users, and other such administrative info. In most cases, you can find the same information more easily through our web service API.
Creating/Deleting Database Tables
In the Admin interface, navigate to Resources > SQL Database.
- To create a new database table, click the "+Add" button at the bottom of the window.
- To edit an existing database table, click the name of that table in the list.
- To delete or clone a database table, check the box next to the table/s you want to delete or clone, then click the appropriate button at the bottom of the window.
All these custom tables exist in the "x" schema. You cannot make changes to data in the "dbo" schema.
Editing the Structure of Database Tables
To add data to your local table, you must first create the columns, giving each a name and a type of data to store.
- To add a column, click the "Add Column" button and specify the name and type.
When naming your column, best practice is to use the same constraints for Local tables: try to use only letters and numbers and don't start with a number. We suggest using CamelCase naming, but snake_case is also acceptable for database tables.
- To edit an existing column in your table, right-click that column and choose "Edit Column" from the pop-up menu.
When changing the datatype of any column:
- Remember to change any code in safe functions or configurators which use the table. They will be expecting the old datatype of that column, and will usually report an error until fixed.
- Remember to refresh any other open windows/tabs to your admin interface (press SHIFT-F9, or close and re-open that browser tab). The admin interface caches table structures to make your coding easier, and after a schema change (like changing the datatype) it may be out-of-date and guide you incorrectly.
- Unlike local tables, you cannot easily reorder columns in database tables. Remember the order of columns has no bearing on Snap code or performance: it is purely for your ease of use as an Administrator.
If you must reorder database table columns, you can download the data from the table, delete the table, create a new table with the same name and columns in the correct order, and then upload your data into that new version of the table.
Advanced Mode
Table designs can be simple or complex. To make table creation easier, we first show basic table functionality when creating a table. For most purposes, this offers useful table tools without extra distracting or confusing features. However, advanced mode is available: turn on the "Advanced Mode" slider switch to reveal more column types, indexes, and other table features useful to more advanced administrators.
- Advanced Mode does not change the performance or structure of a table. It only shows or hides additional table design options.
- If a table uses features that are part of advanced mode, then the "Advanced mode" slider cannot be turned off.
Columns
You can add columns of the following types to database tables:
| Column Type | Description |
|---|---|
| Text | Any text value |
| Integer | Any whole numeric value, such as 3, 500, 0, or -24. Partial amounts are not allowed. |
| Boolean | True or False |
| Decimal | Any numeric value, such as 3, 500, 0, -24, or 3.14159. Partial amounts are allowed. |
| Other Types Available | If you select "advanced mode" at the top of a table definition, more column types become available. These include various options for expressing numbers and text more specifically. To learn more about these column types, and the benefits/drawbacks for using them instead of the basic types, visit this documentation. |
When defining a column, you can assign attributes to the column for better operation:
| Column Attribute | Description |
|---|---|
| Optional | Allows a row to be added or edited to the table with this column left blank (or empty, equal to NULL). |
| Unique | When any row is added or edited, the value in this column is compared to all the other values already stored in this column. If this value is equal to any other value already stored, then the row cannot be saved. This ensures the values in this column remain unique. |
| Index | If this column is used for looking up data in the table using SELECT statements, or for type-ahead in an auto-complete text field, then adding an index to the column will greatly increase the performance of lookups. |
Moving Data into and out of Tables
After you created a table and defined the columns of data it stores, you can then load data into it. While you can connect to your database in many ways, step-by-step instructions are available for two free tools:
- Connecting to your Epicor CPQ database via MSSMS
- Connecting to your Epicor CPQ database via Data Studio
You can also use Snap rules in your quote, configurator, or scene to read and write to your custom tables.
Table Relationships
After creating more than one table, you can begin defining relationships between them. These relationships help ensure referential integrity and performance when gathering data from multiple tables in a query.
Describing good database table design (concepts such as primary tables, secondary tables, bridge tables, denormalizing data, and the like) is outside the scope of this documentation: online learning resources or database professionals in your own organization can help you get started.
For each relationship, first define the tables. Then, when editing the primary table, open the "relationships" section and add a new relationship. Specify the source table and column and the reference table and column. If creating a many-to-many relationship, a bridge table will be created for you, to which you can subsequently add columns.
Localizing Database Tables
Like local tables, Database Tables can have multiple columns to represent multiple languages. Review the localization page to learn more about this feature.