Retail POS data model
Normalized (3NF) OLTP model of a retail point-of-sale system modeled on NCR / Square / Oracle Xstore structures. Store, register, cashier, product, inventory, transaction, tender, return, and loyalty tables are each normalized with surrogate and natural keys and connected by child-to-parent foreign keys. Customer and cashier PII and card suffixes are classified and masked.
Operational store-level point-of-sale data: the store fleet, registers, and cashiers, a product master with per-store inventory, sales transactions and their line items, the tenders that settle them, returns and return lines against prior sales, and the loyalty customers and accounts that earn points. It is a normalized (3NF) relational model that mirrors the source system, suited to operational databases and to landing source data faithfully.
- 12
- tables
- 106
- columns
- 19
- relationships
- 8
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: PostgreSQL). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Transaction header / line / tender split
A POS basket is one header with many scanned lines settled by one or more tenders; modeling them as three tables matches the receipt structure and lets a sale be paid by split tender.
Returns reference the original sale
Return transactions and return lines softly reference the original sales transaction and line so refunds can be validated against the purchase and shrink/disposition tracked.
Card data minimization for PCI-DSS
Tenders store only a masked card_last4 and auth code, never the full PAN, keeping the POS store database out of PCI cardholder-data scope.
Normalized (3NF) for store-level OLTP
POS is high-volume write OLTP at each lane requiring referential integrity and minimal anomalies; a normalized relational model on Postgres is preferred over a sales star schema, which is built downstream.
Tables in the Retail POS model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Tables (12)
store
Grain: one row per retail store / location
Store master.
| Column | Type | Keys |
|---|---|---|
| store_key | bigint | PK |
| store_number | varchar | BK |
| store_name | varchar | |
| store_format | varchar | |
| address_line | varchar | |
| city | varchar | |
| region | varchar | |
| country | varchar | |
| timezone | varchar | |
| is_active | boolean |
register
Grain: one row per POS register / lane in a store
| Column | Type | Keys |
|---|---|---|
| register_key | bigint | PK |
| store_key | bigint | |
| register_number | varchar | BK |
| device_serial | varchar | |
| register_type | varchar | |
| is_active | boolean |
cashier
Grain: one row per cashier / store associate
| Column | Type | Keys |
|---|---|---|
| cashier_key | bigint | PK |
| store_key | bigint | |
| employee_id | varchar | BK |
| full_name | varchar | PII |
| varchar | PII | |
| role | varchar | |
| hire_date | date | |
| is_active | boolean |
product
Grain: one row per product / item master
| Column | Type | Keys |
|---|---|---|
| product_key | bigint | PK |
| sku | varchar | BK |
| upc | varchar | |
| product_name | varchar | |
| department | varchar | |
| category | varchar | |
| retail_price | decimal | |
| tax_category | varchar | |
| is_weighed | boolean | |
| is_active | boolean |
inventory
Grain: one row per product per store on-hand position
| Column | Type | Keys |
|---|---|---|
| inventory_key | bigint | PK |
| store_key | bigint | |
| product_key | bigint | |
| quantity_on_hand | decimal | |
| reorder_point | decimal | |
| safety_stock | decimal | |
| last_counted_at | timestamp |
customer
Grain: one row per identified retail customer
| Column | Type | Keys |
|---|---|---|
| customer_key | bigint | PK |
| customer_number | varchar | BK |
| first_name | varchar | PII |
| last_name | varchar | PII |
| varchar | PII | |
| phone | varchar | PII |
| postal_code | varchar | PII |
| marketing_opt_in | boolean |
loyalty_account
Grain: one row per loyalty membership for a customer
| Column | Type | Keys |
|---|---|---|
| loyalty_account_key | bigint | PK |
| customer_key | bigint | |
| loyalty_number | varchar | BK |
| tier | varchar | |
| points_balance | integer | |
| lifetime_points | integer | |
| enrolled_date | date | |
| loyalty_status | varchar |
sales_transaction
Grain: one row per POS sales transaction (basket)
Basket header.
| Column | Type | Keys |
|---|---|---|
| sales_transaction_key | bigint | PK |
| store_key | bigint | |
| register_key | bigint | |
| cashier_key | bigint | |
| customer_key | bigint | |
| transaction_number | varchar | BK |
| business_date | date | |
| transacted_at | timestamp | |
| transaction_type | varchar | |
| subtotal_amount | decimal | |
| discount_amount | decimal | |
| tax_amount | decimal | |
| total_amount | decimal | |
| item_count | integer |
transaction_line
Grain: one row per line item on a sales transaction
| Column | Type | Keys |
|---|---|---|
| transaction_line_key | bigint | PK |
| sales_transaction_key | bigint | |
| product_key | bigint | |
| line_number | integer | |
| quantity | decimal | |
| unit_price | decimal | |
| line_discount | decimal | |
| extended_amount | decimal | |
| tax_code | varchar |
tender
Grain: one row per tender / payment settling a transaction
| Column | Type | Keys |
|---|---|---|
| tender_key | bigint | PK |
| sales_transaction_key | bigint | |
| tender_type | varchar | |
| amount | decimal | |
| change_given | decimal | |
| card_last4 | varchar | PII |
| auth_code | varchar | |
| tender_status | varchar |
return_transaction
Grain: one row per return against a prior sale
| Column | Type | Keys |
|---|---|---|
| return_transaction_key | bigint | PK |
| store_key | bigint | |
| register_key | bigint | |
| cashier_key | bigint | |
| original_transaction_key | bigint | |
| return_number | varchar | BK |
| returned_at | timestamp | |
| return_reason | varchar | |
| refund_amount | decimal | |
| refund_method | varchar |
return_line
Grain: one row per line item on a return
| Column | Type | Keys |
|---|---|---|
| return_line_key | bigint | PK |
| return_transaction_key | bigint | |
| product_key | bigint | |
| original_line_key | bigint | |
| quantity | decimal | |
| unit_price | decimal | |
| refund_amount | decimal | |
| disposition | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- register.store_key → store (many-to-one)
- cashier.store_key → store (many-to-one)
- inventory.store_key → store (many-to-one)
- inventory.product_key → product (many-to-one)
- loyalty_account.customer_key → customer (many-to-one)
- sales_transaction.store_key → store (many-to-one)
- sales_transaction.register_key → register (many-to-one)
- sales_transaction.cashier_key → cashier (many-to-one)
- sales_transaction.customer_key → customer (many-to-one)
- transaction_line.sales_transaction_key → sales_transaction (many-to-one)
- transaction_line.product_key → product (many-to-one)
- tender.sales_transaction_key → sales_transaction (many-to-one)
- return_transaction.store_key → store (many-to-one)
- return_transaction.register_key → register (many-to-one)
- return_transaction.cashier_key → cashier (many-to-one)
- return_transaction.original_transaction_key → sales_transaction (many-to-one)
- return_line.return_transaction_key → return_transaction (many-to-one)
- return_line.product_key → product (many-to-one)
- return_line.original_line_key → transaction_line (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.