PCC Data visualisation

Pattern counts, examples, and recommendations are aligned with DATABASE-COLUMN-NAMING-PATTERNS.md in the PCC build tool folder (pcc build tool\DATABASE-COLUMN-NAMING-PATTERNS.md).

1. Scope of analysis

• 2,685 database columns
• 153 tables
• Across core PCC schema, migration tables, and view layers

2. Naming patterns identified

Four distinct naming conventions were identified across the database.

2.1 ALL_UPPERCASE (dominant)

Definition

All uppercase letters with no separators.

Prevalence

• 1,807 columns (67.3% of all fields)
• Present in 146 of 153 tables

Characteristics

• Words concatenated without separation
• Often used for identifiers, transactional data, and core attributes

Examples

ACCOUNTID, CUSTOMERID, CALLDATE, PHONENUMBER, TXNBALANCE, RESPONSECODE, VALUATIONDATE

Observations

• Low readability for multi-word fields; no clear word boundaries
• Heavily used in legacy and core tables

2.2 UPPER_SNAKE_CASE (secondary)

Definition

Uppercase letters with underscore separators between words.

Prevalence

• 762 columns (28.4%)
• Present in 69 tables

Characteristics

• More readable than ALL_UPPERCASE
• Common in financial attributes, structured data groups, and newer schema components

Examples

CURRENT_VALUATION_AMT, ACCOUNT_SCORE, SECURITY_POST_CODE, CUSTOMER_TYPE, PURSUABLE_BALANCE, APPLICATION_START_DATE

Observations

• Preferred format for multi-word clarity
• Often coexists with ALL_UPPERCASE in the same table

2.3 PascalCase (view layer)

Definition

Each word starts with uppercase, no separators.

Prevalence

• 115 columns (4.3%)
• Found in 16 tables, primarily views (VWT_*)

Characteristics

• Used almost exclusively in derived or view tables
• More aligned to application-layer readability

Examples

CustomerID, CustomerName, RouteName, SegmentName, SumOfArrears

Observations

• Clear separation between base tables (uppercase styles) and the view layer (PascalCase)
• Suggests different ownership or design standards

2.4 Invalid or mixed pattern (other in source doc)

Definition

Inconsistent or erroneous naming.

Prevalence

• 1 column (0.0%)

Example

APPlICATION_SCORE in PCC_ACCOUNTS (mixed casing typo; should be APPLICATION_SCORE)

Observations

• Indicates lack of enforced validation or QA controls on naming

3. Pattern distribution by table type

3.1 Core PCC tables (PCC_*)

Mix of ALL_UPPERCASE and UPPER_SNAKE_CASE; both patterns often exist within the same table.

Example: PCC_ACCOUNTS (154 columns): 119 UPPER_SNAKE_CASE, 34 ALL_UPPERCASE, 1 other.

3.2 Communications and transactional tables

Example: PCC_COMMUNICATIONS (195 columns): 100% ALL_UPPERCASE. Heavy bias toward legacy-style naming.

3.3 Migration tables (PCC_MIG_*)

Predominantly ALL_UPPERCASE, likely aligned to source system ingestion formats.

3.4 View tables (VWT_*)

Predominantly PascalCase. Clear distinction between storage layer and presentation or derived layer.

4. Key findings

1. No single standard exists. Multiple naming conventions coexist; no enforced schema-wide rule.
2. Mixed patterns within tables. Even core tables contain multiple naming styles, indicating incremental development and limited governance.
3. Strong legacy influence. ALL_UPPERCASE dominates (~67%), suggesting older design practices and backward compatibility constraints.
4. Layered naming behaviour. Base tables use uppercase conventions; view tables use PascalCase, implying separation between data storage and consumption layers.
5. Data quality and control gaps. Typos and inconsistent casing appear; no strict enforcement mechanism is evident from naming alone.

5. Suggested follow-up checks (duplicates and build hygiene)

These are candidates for automation or manual review against PCC-Table-Columns.json and your ERD or registry.

• Near-duplicate column names: After normalizing names (for example uppercase and strip non-alphanumerics), flag pairs that collapse to the same token string across different physical names. Maintain an allowlist for intentional pairs such as ACCOUNTS1 vs ACCOUNTID (legacy PK vs logical identifier), and similar id or FK naming pairs you accept by convention.
• Case-only or spelling variants: The pattern report calls out pairs like Routename vs RouteName in views; scan for Levenshtein distance 1 or case-insensitive equality within the same table.
• Typo sweep: Inner caps in otherwise ALL_UPPER patterns (for example APPlICATION_SCORE); optional dictionary pass on underscore-separated tokens.
• FK and registry drift: Compare loaded column JSON to diagram nodes and to PCC-Data-Registry.csv (tables present in one artefact but not another).
• Cardinality and keys: Tables with no column marked PK, or FK references to missing tables or columns in the JSON.
• Reserved words and length: Columns that match SQL reserved tokens or exceed your host or tool limits (if any).

Recommendations from the source doc include standardizing new base columns on UPPER_SNAKE_CASE, fixing APPlICATION_SCORE, keeping PascalCase for VWT_* views, and documenting the convention in project standards.