Data Model Architecture
The portal hierarchy consists of three tables in the portal schema that provide a consistent structure for managing customer accounts.
Schema Diagram
Table Definitions
Tier 1: Parent Accounts
Top-level entity representing the customer organization (e.g., "UPS").
| Column | Type | Purpose |
|---|---|---|
parent_account_idrequired | INT IDENTITY(1001,1) | Primary key |
parent_account_namerequired | VARCHAR(250) | Customer name (e.g., "UPS") |
parent_display_name | VARCHAR(250) | Display name (usually same as name) |
portal_load_phase | INT | Loading priority |
active_account | BIT | Active flag |
Tier 2: Accounts
Links to CieTrade counterparties via CPID.
| Column | Type | Purpose |
|---|---|---|
account_idrequired | INT IDENTITY(11,1) | Primary key |
parent_account_idrequired | INT | FK to wg_parent_accounts |
account_namerequired | VARCHAR(250) | Account/facility name |
cietrade_account_idrequired | INT | CPID - links to new_ct.accounts.account_id |
cietrade_account_id has a unique constraint preventing duplicate CieTrade links.
Tier 3: Account Locations
Individual service addresses linked to CieTrade addresses.
| Column | Type | Purpose |
|---|---|---|
location_idrequired | INT IDENTITY | Primary key |
account_idrequired | INT | FK to wg_accounts |
cietrade_address_id | INT | Links to new_ct.addresses.address_id |
location_namerequired | VARCHAR(250) | Location identifier (business key) |
address | VARCHAR | Street address |
city | VARCHAR | City |
state | VARCHAR | State |
zip | VARCHAR | ZIP code |
location_udf1 | VARCHAR | User-defined field 1 |
location_udf2 | VARCHAR | User-defined field 2 |
location_udf3 | VARCHAR | User-defined field 3 |
location_udf4 | VARCHAR | User-defined field 4 |
cietrade_active_status | VARCHAR | Active/Inactive flag |
Composite unique constraint on (location_name, account_id).
Relationship Cardinalities
The hierarchy supports two patterns:
| Pattern | Structure | When to Use |
|---|---|---|
| Standard (1:1:N) | 1 Parent → 1 Account → N Locations | Single CPID with multiple service addresses |
| Complex (1:N:N) | 1 Parent → N Accounts → N Locations | Multiple CPIDs requiring consolidated reporting (e.g., UPS, McKesson) |
Key Join Fields
| Portal Field | CieTrade Field | Notes |
|---|---|---|
cietrade_account_id | accounts.account_id | CPID - counterparty identifier |
cietrade_address_id | addresses.address_id | Location identifier |
location_name | addresses.location_name | Business key - must match exactly |
Always join on BOTH cietrade_account_id (CPID) AND location_name. Address IDs alone are insufficient because location_name is the business key.
Standard Join Pattern
-- Join CieTrade fact tables to portal hierarchy
FROM new_ct.billing_charges bc
JOIN portal.vw_wg_accounts v
ON v.cietrade_account_id = bc.account_id
AND v.location_name = bc.location_name
Primary Interface
portal.vw_wg_accounts is the primary integration point for all reporting. This view flattens the three-tier hierarchy into a single queryable interface.