Data Model

bc_dev_tools normalizes imported data into two parallel star schemas -- one for FAQ issue types and one for BC fault codes. A match bridge connects them.

FAQ Star Schema

The flat FAQ API response (faq_api_issue_types) is decomposed into four dimension tables and a normalized fact table:

Table	Source Column	Description
faq_main_categories	csMainCategoryId / csMainCategoryName	Top-level fault categories
faq_sub_categories	csSubCategoryId / csSubCategoryName	Second-level categories (linked to main)
faq_issue_types	issueTypeId / issueTypeName	Specific issue types
faq_item_parts	itemPartId / itemPartName	Item/part identifiers
faq_issue_types_normalized	Foreign keys to all four dimensions	Fact table

SQL views are created for convenience querying, joining the fact table back to its dimensions.

BC Star Schema

The flat BC fault code relationship import (bcFaultCodeRelationships) follows the same pattern:

Table	Source Columns	Description
bc_fault_areas	Fault Area Code / Fault Area Description	Top-level fault areas
bc_symptom_codes	Symptom Code / Symptom Code Description	Symptom classifications
bc_fault_codes_dim	Fault Code / Fault Code Description	Specific fault codes
bc_descriptions	Description	Free-text descriptions
bc_issue_types_normalized	Foreign keys to all four dimensions	Fact table

Match Bridge

The fuzzy matching process writes results back to the original BC source table (bcFaultCodeRelationships) as four additional columns:

Column	Type	Description
faq_match_found	boolean	Whether a match above the similarity threshold was found
faq_matched_id	integer	Foreign key to faq_issue_types_normalized.id
faq_match_details	string	Per-column similarity breakdown
match_score	float	Overall match confidence (0.0--1.0)

The column mapping used for matching:

BC Column	FAQ Column
Fault Area Description	csMainCategoryName
Symptom Code Description	csSubCategoryName
Fault Code Description	itemPartName
Description	issueTypeName