Database Properties
Database Type: PostgreSQL - 17.4 (Debian 17.4-1.pgdg120+2)
Schema public
standard public schema
Tables
| Table / View | Children | Parents | Columns | Rows | Type | Comments |
|---|---|---|---|---|---|---|
| audit | 0 | 1 | 3 | 114 | Table | System table for PBJ platform. Table records the last updated timestamp for various tables on corresponding nodes which can be used to look up during ETL |
| customer_communication_preference | 0 | 2 | 5 | -1 | Table | Table represents customers communication preferences |
| syndicate_dividend | 0 | 7 | 9 | -1 | Table | For commercial lotteries. Table represents syndicate dividends |
| update_history | 0 | 0 | 5 | 160 | Table | System table for PBJ platform. Table represents the update history for schema files |
| account_transaction_status_history | 0 | 2 | 5 | -1 | Table | Table represents the status history of each account transaction. There may be several transaction statuses or states for each transaction over time |
| deposit | 1 | 6 | 18 | -1 | Table | Table represents deposit specific data related to transactions |
| dim_draw | 6 | 1 | 9 | -1 | Table | A dimension table to normalise draws for the lotteries |
| syndicate_sales | 3 | 2 | 10 | -1 | Table | For commercial lotteries. Table represents customer syndicate share purchases |
| withdraw | 1 | 2 | 3 | -1 | Table | Table represents the withdrawals of customers |
| dim_browser | 0 | 0 | 9 | 5 | Table | Dimension table for list of all available browsers (primarily used in customer_action) |
| dim_date | 10 | 0 | 44 | 73413 | Table | Table represents a dimension of a day. This is not locked to a timezone and is updated daily |
| dim_pricing | 5 | 0 | 5 | -1 | Table | A dimension table to normalise pricing types |
| product | 0 | 0 | 12 | -1 | Table | Deprecated |
| dim_lottery | 11 | 0 | 7 | -1 | Table | A dimension table to normalise lotteries |
| customer | 16 | 4 | 43 | -1 | Table | Table represents the customers from the node databases |
| syndicate_session_sales_history | 0 | 2 | 7 | -1 | Table | Track changing attribute of syndicate session sales. |
| dim_recurring_purchase_type | 3 | 0 | 5 | -1 | Table | A dimension table to normalise recurring purchase types |
| dim_channel | 6 | 0 | 5 | -1 | Table | A dimension table to normalise PBJ sales and supplier channels |
| metrics_data_adjustments | 0 | 0 | 6 | -1 | Table | System table which identifies consistency and possible missing data across the metrics database transactional tables |
| customer_account_transaction_summary | 0 | 2 | 6 | 1 | Table | Table represents daily summaries of account_transaction for each customer according to QLD timezone. Aggregated for performance reasons |
| customer_event | 0 | 3 | 7 | -1 | Table | Table represents customers events |
| dim_syndicate | 1 | 0 | 5 | -1 | Table | A dimension table to normalise commercial lottery syndicates |
| account_transaction_reversed | 0 | 1 | 4 | -1 | Table | Table represents the account transactions that were reversed |
| deposit_status_history | 0 | 1 | 4 | -1 | Table | Table represents the status history of each deposit |
| dim_entry_offer | 2 | 0 | 5 | -1 | Table | A dimension table to normalise commerical lottery product offers |
| dim_campaign | 0 | 0 | 9 | -1 | Table | Deprecated |
| ticket_sales | 6 | 6 | 20 | -1 | Table | Table represents ticket sales of customers |
| dim_customer_event_type | 1 | 0 | 5 | -1 | Table | A dimension table to normalise customer event types |
| provider | 3 | 0 | 19 | 35 | Table | Deprecated: use dim_provider instead |
| syndicate_wager | 0 | 2 | 8 | -1 | Table | Deprecated: use wager instead |
| account_transaction | 6 | 3 | 7 | -1 | Table | Table represents the transactions of customers including purchases,deposits,withdrawals and reversals. More specific data is available for deposits, purchases (ticket_sales) and withdrawals under the relevant table |
| dim_cause | 1 | 0 | 5 | -1 | Table | |
| wager_cancelled | 0 | 8 | 17 | -1 | Table | Table represent wager cancellations |
| migrated_ticket_sales | 0 | 2 | 16 | -1 | Table | Table that represents migrated ticket sales. |
| wager | 0 | 13 | 25 | -1 | Table | Table represent the wager numbers and wager/purchase information of each ticket |
| syndicate_session_sales | 3 | 2 | 3 | -1 | Table | For commercial lotteries. Table represents syndicate sessions that shares belong to |
| dim_scheduled_purchase_event_type | 1 | 0 | 5 | -1 | Table | |
| dim_provider | 4 | 0 | 5 | -1 | Table | A dimension table to normalise commercial lottery providers |
| dim_organisation | 2 | 0 | 5 | -1 | Table | A dimension table to normalise PBJ sales and supplier organisations |
| dim_transfer_method | 4 | 0 | 5 | -1 | Table | A dimension table to normalise deposit/withdraw transfer methods |
| ticket_status_history | 0 | 2 | 5 | -1 | Table | Table represents the status history of each ticket sale |
| scheduled_purchase_event | 0 | 2 | 5 | -1 | Table | |
| ticket_dividend | 0 | 7 | 11 | -1 | Table | For commercial lotteries. Table represents ticket dividends |
| dim_mobile | 0 | 0 | 6 | -1 | Table | Deprecated |
| recurring_purchase_status_history | 0 | 2 | 8 | -1 | Table | Table represents the status history of each recurring purchase |
| dim_device_platform | 1 | 0 | 4 | 3 | Table | Table represents a dimension of the possible devices customer can use when accessing the site |
| dim_scheduled_purchase_type | 1 | 0 | 5 | -1 | Table | |
| dim_affiliate | 3 | 0 | 6 | -1 | Table | A dimension table to normalise affiliate data |
| sales | 0 | 3 | 14 | -1 | Table | Deprecated: use ticket_sales instead |
| metrics_health | 0 | 0 | 5 | -1 | Table | System table which identifies consistency and possible missing data across the metrics database transactional tables |
| dim_game | 0 | 0 | 5 | -1 | Table | Deprecated |
| scheduled_purchase | 2 | 5 | 12 | -1 | Table | |
| dim_division | 1 | 0 | 6 | -1 | Table | For commercial lotteries. A dimension table to normalise prize divisions |
| last_checkout_update | 1 | 0 | 4 | 6 | Table | Deprecated |
| dim_recurring_purchase_event_type | 1 | 0 | 5 | -1 | Table | A dimension table to normalise Autoplay/Subscription event types |
| dim_account_transaction_status | 1 | 0 | 5 | -1 | Table | A dimension table to normalise account transaction status data |
| syndicate_share_status_history | 0 | 1 | 3 | -1 | Table | For commercial lotteries. Table represents the status history of each syndicate share purchase |
| administrator | 6 | 0 | 2 | -1 | Table | Table represents list of PBJ Administrators |
| customer_action | 0 | 4 | 13 | -1 | Table | Table represents the actions that a customer has taken on the website |
| dim_location | 2 | 0 | 4 | -1 | Table | Deprecated |
| withdraw_status_history | 0 | 1 | 3 | -1 | Table | Table represents the status history of each withdrawal |
| recurring_purchase | 3 | 3 | 6 | -1 | Table | Table represents list of Autoplays/Subscriptions of customers |
| account_transaction_summary | 0 | 1 | 5 | 1 | Table | Table represents daily summaries of account_transaction according to QLD timezone. Aggregated for performance reasons |
| update_revision | 0 | 0 | 2 | 10 | Table | System table for PBJ platform. Table represents the release revision identifier |
| wager_purchases | 0 | 1 | 6 | -1 | Table | Deprecated: use wager instead |
| recurring_purchase_event | 0 | 2 | 5 | -1 | Table | Table represents the recurring purchases events. Includes next anniversary dates and success/failures for every purchase attempt |
| admin_transfer | 0 | 4 | 9 | -1 | Table | Table represents transfers made by PBJ Administrators |
| prize | 0 | 3 | 16 | -1 | Table | For commercial lotteries. Table represents ticket prizes |
| migrated_and_current_ticket_sales | 0 | 2 | 8 | 0 | View | Combines ticket sales data from migrated_ticket_sales (imported external tickets), and ticket_sales (current PBJ tickets) |
| customer_phone_event | 0 | 2 | 4 | 0 | View | View represents customers phone events |
| account_balances | 0 | 1 | 3 | 0 | View |