Database Schema

This topic has information about data table relationships in Prism. 

Note: Unique identifiers for controller, subsidiary, and tenant are referenced by virtually every record in the database. Unique identifiers for store are referenced by a large fraction of those records. For readability, these tables were omitted from diagrams.

Adjustments
Adjustment memos are transactional documents that record changes to inventory quantity, price, and cost that are not recorded on receipts, vouchers, or slips. Prism stores general information about each adjustment in the ADJUSTMENT table, information about each item being adjusted in the ADJ_ITEM table, and specific information related to the quantity, price, or cost being adjusted in descendants of the ADJ_ITEM table.

Cash Drawers
Main Table: DRAWER
The DRAWER table stores general cash drawer info such as name, number and reference to workstation (see Common for WORKSTATION). A till is the tray (containing cash) that a cashier or manager puts in the cash drawer. The TILL table contains basic information about the tills like associated drawer, till name, max and min cash amount, etc. The DRAWER_EVENT and DRAWER_EVENT_CURRENCY tables store info about drawer events, typically cash transactions, such as date/time, amount, etc.

Centrals
Prism's Centrals feature enables retailers to designate a single server in the subsidiary as the validation point for any or all of the following POS tasks: Customer lookup, Returns, Gift Cards and Store Credit. In addition, the Customer Loyalty feature uses the Centrals server for accessing/updating customer loyalty information.  Here are the centrals-related tables: LTY_CUST_CENTRAL, CENTRAL_CREDIT, CENTRAL_CREDIT_HOLDTXN, CENTRAL_GIFT_CARD, CENTRAL_GIFT_CARD_RB, CENTRAL_GIFT_CARD_TXN_RB, CUST_CENTRAL_CREDIT_BAL_TMP

Contact Types
Main Tables: ADDRESS_TYPE, PHONE_TYPE & EMAIL_TYPE
Address, Phone and email types (i.e. "Home" and "Work") can be added in Prism and associated with customer and vendor records. The types are stored in the ADDRESS_TYPE, PHONE_TYPE and EMAIL_TYPE tables and are referenced by records in the CUSTOMER_ADDRESS, CUSTOMER_PHONE, CUSTOMER_EMAIL, and VENDOR_CONTACT tables.

Currency
Main Table: CURRENCY
Prism allows users to define currencies and exchange rates/options. The main CURRENCY table stores information about currencies (name of currency, currency code, etc.) and has 2 dependents: CURRENCY_DENOMINATION stores information about individual denominations for currencies and EXCHANGE_RATE stores actual exchange rate information. Note that each currency can have one or more exchange rates defined.

Customers
Main Table: CUSTOMER
The CUSTOMER table stores general information about each customer and its child tables store information regarding their home and email addresses, phone numbers, credit cards, document and contact histories, and more.

Document
The DOCUMENT schema stores transaction information, including Bill To/Ship To Customer, Taxes, Discounts, Coupons, POS Flags and several other descendant tables.

Employees
Main Tables: EMPLOYEE & USER_GROUP
Each employee is recorded in the EMPLOYEE table and specific information such as their assigned stores, subsidiaries, etc. are distributed among the descendants of the EMPLOYEE table. Furthermore, employees can be added to groups and each group can have its own set of permissions. The USER_GROUP table stores permissions for each group and the USER_GROUP_USER table tracks the group(s) that an employee is assigned to.

Fees
Main Tables: PURCH_FEE_TYPE, DOCUMENT_FEE_TYPE, TRAN_FEE_TYPE
Fees can be applied to purchase orders (POs), vouchers, POS transactions, and transfer slips. Information related to fees on purchase orders (including fee amount) is stored in the PO_FEE table and each record references a fee type located in the PURCH_FEE_TYPE table. Fees on vouchers are stored in the VOU_FEE table and each record references a fee type located in the same PURCH_FEE_TYPE table. Fees on POS transactions are stored in the DOCUMENT (see Transactions for DOCUMENT) table and each record references a fee type located in the DOCUMENT_FEE_TYPE table. Finally, fees on transfer slips are stored in the SLIP_FEE table and each record references a fee type located in the TRAN_FEE_TYPE table.

Inventory
Main Table: INVN_SBS_ITEM
Each inventory item is stored in the INVN_SBS_ITEM table and its details are distributed among descendants of that table.

Loyalty
Main Table: LTY_LEVEL
The Customer Loyalty module tracks loyalty points and programs. Each customer can have a loyalty level, and the name and active status of each of those levels is defined in the LTY_LEVEL table. Each Customer Loyalty program is defined in the LTY_LEVEL_PROGRAM table and details such as start and end date as well as a reference to a loyalty level are stored there. Each customer enrolled in a loyalty program is recorded in the LTY_CUST_CENTRAL along with their loyalty level, points balance, and more.

Markdowns
Main Table: MARKDOWN
Markdowns in Prism are essentially managed by the MARKDOWN table and its 2 descendent tables. The parent table holds general information such as the name, date, and active status while the child tables (MARKDOWN_ADJ, MARKDOWN_DATA, MARKDOWN_ITEM) contain specifics such as markdown/original values and references to the ITEM and ADJUSTMENT tables.

Physical Inventory
Main Table: PI_SHEET
A physical inventory is an actual count of the merchandise that is in stock. The retailer uses this count to determine exactly how much inventory they have and what loss has occurred. The main table in this diagram is PI_SHEET since a new PI "sheet" is created for each PI performed (before items are scanned). The PI_START table stores item quantities according to the system while PI_ZONE_QTY stores item quantities according to the scanning process. These are the quantities that are typically compared after the PI is complete. Information about bad scans with failure codes is stored in PI_SCAN_BAD table. If using zone-based PIs, the PI_ZONE and PI_ZONE_QTY tables store zone counts.

POS Transactions
Documents
Main Table: DOCUMENT
The DOCUMENT table stores records about all types of transactions such as sales, returns, exchanges, shipments, etc. Specific details related to each type of transaction are distributed among 8 children of the DOCUMENT table.
Each item in a document/transaction is stored in the DOCUMENT_ITEM table and each discount applied to each individual item is stored in the DOCUMENT_ITEM_DISC table. On the other hand, discounts applied to all items in a transaction (a.k.a. transaction-level discounts) are stored in the DOCUMENT_DISC table. Coupons are handled similarly and details for each coupon are stored in the DOCUMENT_COUPON table.
Tendering 
Main Table: TENDER
Tender-related tables store information about payments and payment options available to users at the POS. The TENDER table stores basic information about payments such as amount taken and type of currency, and each record in this table possesses a foreign key reference to a record in DOCUMENT. TENDER has several descendant tables that hold specific information associated with each payment type (e.g., credit card, debit card, gift card, travelers check, etc.).
POS Flags
Main Table: DOC_POS_FLAG
POS flags are available in Prism to record additional information with each sale related to marketing, promotions, customer demographics, weather information, etc. Each flag label is stored in the DOC_POS_FLAG table and each flag option is stored in the DOC_POS_FLAG_OPTION table. The flag options chosen at transaction time are stored with the other transaction-related information in the DOCUMENT table.

Printers and Tags
Main Table: PRINTER
Printer information like name, type of interface, and control code is stored in the PRINTER table. Settings/preferences related to printers are distributed among the many relatives (not necessarily descendants) of the PRINTER table like PRINTER_TYPE, PRINTER_TYPE_ASSIGNMENT, PRINTER_TYPE_SETTING, PRINT_AREA and PRINT_AREA_VALUE. Furthermore, tag-related tables and their relationships are shown in the same diagram, partially because each TAG_CODE references a PRINTER.

Promotions
Main Table: PCP_PROMOTION
The PCP_PROMOTION table and its descendants store promotion definitions. Basic information like description, start/end time, and validation information is stored in the PCP_PROMOTION table while more specific information is distributed among descendants. For example, groups affected by a promotion are stored in the PCP_PROMOTION_GROUPS table and coupons associated with a promotion are stored in the PCP_VALIDATION_COUPON table. When promotions are actually used in a transaction, that information is stored in the DOCUMENT table and its descendants.

Purchase Orders
Main Table: PO
Purchase orders (POs) are sent to vendors to restock or add new merchandise. A PO typically includes information about the vendor, relevant dates, shipping instructions, items being ordered, and order cost. Each PO is stored in the PO table and details for each PO are distributed among descendants of the PO table.
Allocation Patterns
Main Table: ALLOCATION_PATTERN
Allocation patterns specify the relative quantities of merchandise to be ordered for each store. The name and ID of each pattern is stored in the ALLOCATION_PATTERN table and store quantities are stored in the ALLOCATION_PATTERN_QTY table.

Reasons
Reasons can be associated with various actions in Prism such as applying a discount or checking in to work. Reason options are defined in the Admin Console and are stored in the PREF_REASON table. When a reason is selected while performing a task, that reason is stored in the respective table (i.e. ADJUSTMENT, DRAWER_EVENT, etc.).

Scales
Main Table: SCALE
Scales are applied to inventory styles and are defined by various sizes and attributes. Each scale is recorded in the SCALE table, and a scale's sizes, attributes and quantities are stored in the SCALE_SIZE, SCALE_ATTRIBUTE and SCALE_PATTERN_QTY tables, respectively.

Taxes
Main Table: TAX
A tax code is assigned to every item in inventory and stored in the TAX_CODE table. Tax areas are assigned to tax jurisdictions where sales are made, and each tax area is stored in the TAX_AREA table. Actual tax rate percentages are stored in the TAX_CODE_RULE table and categorized by a tax level.

Transfers
Transfer Orders
Main Table: TORD
Transfer orders (TORDs) are planning documents that do not affect inventory. They can be modified as needed until the time of transfer, at which point a transfer slip is generated. Transfer orders are stored in the TORD table, items within an order are stored in the TORD_ITEM table, and the quantity of each item being transferred is stored in the TORD_QTY table.
Transfer Slips
Main Table: SLIP
Transfer slips record the movement of merchandise out of the source location. Each transfer slip is recorded in the SLIP table, item details for each transfer slip are stored in the SLIP_ITEM table, comments associated with each transfer slip are stored in the SLIP_COMMENT table, and fees associated with each transfer slip are stored in the SLIP_FEE table.

Vendors
General information about each vendor like name and account number is stored in the VENDOR table and specific information such as contact info, terms, and invoices are distributed among various descendant tables.

Vouchers
Vouchers record the items and quantities received from or returned to venders. Each Voucher is stored in the VOUCHER table and each item on a Voucher is stored in the VOU_ITEM table. Other details such as comments, approvals, and fees are distributed among other descendants of the VOUCHER table.