# AI Database Crossreference Relations

## Overview
This document describes the relationships between cross-reference tables in the Viamed system. The cross-reference system manages OEM part number mappings to internal stock references, supporting product compatibility and interchangeability.

## Table Relationships

### Core Cross-Reference Tables

#### viamed_xref (Main Cross-Reference Table)
- **Primary Key**: `id` (auto_increment)
- **Relationships**:
  - Links OEM part numbers to internal stock references
  - Groups entries by `xrefcode` for categorization
  - References `hostcompanyid` for multi-tenant support
  - Contains `specsheet_id` linking to specification documents
  - Uses `primary_id` for hierarchical relationships

#### viamed_xref_cat (Cross-Reference Categories)
- **Primary Key**: `id` (auto_increment)
- **Relationships**:
  - Categorizes cross-reference entries by `xrefcode`
  - Controls web visibility with `showonweb` and `showfeaturesonweb` flags
  - Associates with `hostcompanyid` for company-specific categories
  - Referenced by `viamed_xref.xrefcode`

### Supporting Tables

#### viamed_xref_fields (Dynamic Fields)
- **Primary Key**: `id` (auto_increment)
- **Relationships**:
  - Defines custom fields for cross-reference categories
  - Linked to categories via `xrefcode`
  - Controls display settings with `showweb`, `showlocal`, `hiddenfield` flags
  - Ordered by `displayorder`

#### viamed_xref_fields_answers (Field Answers)
- **Primary Key**: `id` (auto_increment)
- **Relationships**:
  - Stores answers for dynamic fields
  - Links to `viamed_xref_fields` via `fieldid`
  - Associates with specific cross-references via `xrefcode` and `stock_ref`
  - References specification sheets via `specsheet_id`
  - Can reference predefined answers via `answerid`

#### viamed_xref_answer (Predefined Answers)
- **Primary Key**: `id` (auto_increment)
- **Relationships**:
  - Provides predefined answer options
  - Associated with categories via `xrefcode`
  - Referenced by `viamed_xref_fields_answers.answerid`

### Documentation and Specification Tables

#### viamed_xref_specsheet (Specification Sheets)
- **Primary Key**: `id` (auto_increment)
- **Relationships**:
  - Links specification sheets to cross-reference categories
  - Associates with specific stock references
  - Referenced by `viamed_xref.specsheet_id`

#### viamed_xref_specsheet_documents (Spec Sheet Documents)
- **Primary Key**: `id` (auto_increment)
- **Relationships**:
  - Links specification sheets to actual document records
  - References `viamed_xref_specsheet` via `specsheetid`
  - Points to document storage via `docid`

### Data Management Tables

#### viamed_xref_cleanlookup (Data Cleaning)
- **Primary Key**: `id` (auto_increment)
- **Relationships**:
  - Maps "dirty" data to "clean" standardized values
  - Associated with categories via `xrefcode`
  - Used for data normalization and search optimization

#### viamed_xref_memos (Notes and Comments)
- **Primary Key**: `id` (auto_increment)
- **Relationships**:
  - Stores additional notes for cross-reference records
  - Links to specific records via `rowid`
  - Tracks who added notes and when

#### viamed_xref_removed (Soft Delete Archive)
- **Primary Key**: `id` (auto_increment)
- **Relationships**:
  - Archives removed cross-reference entries
  - Maintains data integrity for historical reference
  - Mirrors structure of `viamed_xref` for compatibility

## Key Relationships Summary

### One-to-Many Relationships
- `viamed_xref_cat` → `viamed_xref` (one category, many entries)
- `viamed_xref` → `viamed_xref_fields_answers` (one xref entry, many field answers)
- `viamed_xref_fields` → `viamed_xref_fields_answers` (one field definition, many answers)
- `viamed_xref_specsheet` → `viamed_xref_specsheet_documents` (one spec sheet, many documents)

### Many-to-One Relationships
- `viamed_xref` → `viamed_xref_cat` (many entries belong to one category)
- `viamed_xref_fields_answers` → `viamed_xref_fields` (many answers belong to one field)
- `viamed_xref_fields_answers` → `viamed_xref_answer` (many answers reference predefined options)

### Self-Referential Relationships
- `viamed_xref.primary_id` → `viamed_xref.id` (hierarchical product relationships)

### External References
- All tables reference `hostcompanyid` for multi-tenant isolation
- Various tables reference user IDs (`updatedby`, `addedby`, `setby`)
- Document references via `docid`, `specsheet_id`
- Stock reference integration via `stock_ref`

## Data Flow and Dependencies

### Creation Flow
1. Categories are created in `viamed_xref_cat`
2. Custom fields are defined in `viamed_xref_fields` for each category
3. Predefined answers are set up in `viamed_xref_answer`
4. Cross-reference entries are added to `viamed_xref`
5. Field-specific answers are stored in `viamed_xref_fields_answers`
6. Specification sheets are linked via `viamed_xref_specsheet`
7. Documents are attached via `viamed_xref_specsheet_documents`

### Update Flow
- Changes to categories affect all related entries
- Field definition changes impact answer storage
- Data cleaning rules in `viamed_xref_cleanlookup` standardize entries
- Memos provide additional context without affecting core data

### Deletion Flow
- Soft deletes move records to `viamed_xref_removed`
- Maintains referential integrity for historical data
- Preserves audit trail and relationships

## Business Logic Constraints

### Category Management
- Categories control visibility (`showonweb`, `showfeaturesonweb`)
- Category codes (`xrefcode`) must be unique per company
- Categories can be enabled/disabled without data loss

### Field Management
- Fields are category-specific
- Display order controls UI presentation
- Visibility flags control where fields appear (web/local/hidden)

### Data Integrity
- Stock references must exist in main inventory
- OEM part numbers should be unique within categories
- Host company isolation prevents cross-company data leakage

### Performance Considerations
- `xrefcode` indexing critical for category-based queries
- `stock_ref` and `oem_pn` combinations should be indexed
- `hostcompanyid` partitioning recommended for large datasets