CCAM Database Structure - Complete Documentation¶

This document describes the complete structure of the CCAM (Classification Commune des Actes Médicaux) database exported as DBF files.

Overview¶

The CCAM is a comprehensive medical coding and billing system for French healthcare, containing: - 83,025 medical procedures - 1.3M business rule relationships - 788K pricing records - 72 interconnected tables

1. CORE HIERARCHY (3 Levels)¶

Level 1: ACTE (Medical Procedures)¶

File: R_ACTE.dbf (83,025 records)

The root entity representing medical procedures.

Primary Key: COD_ACTE (13 chars) + DT_MODIF (date)

Fields: - Identity: - COD_ACTE: Procedure code (13 chars) - COD_STRUCT: Structured hierarchical code - Names: - NOM_COURT: Short name (70 chars) - NOM_LONG + NOM_LONG0 through NOM_LONGE: Long description (~3,800 chars total, split due to DBF 254-char field limit) - Classification: - MENU_COD → R_MENU: Hierarchical classification tree - TYPE_COD → R_TYPE: Procedure type (3 types) - Financial: - FRAIDP_COD → R_FRAIS_DEP: Travel expenses (4 types) - REMBOU_COD → R_REMBOURSEMENT: Reimbursement mode (3 types) - Versioning: - PRECEDENT: Previous version of procedure code - SUIVANT: Next version of procedure code - DT_CREATIO: Creation date - DT_FIN: End date (if obsolete) - DT_EFFET: Effective date - DT_ARRETE: Decree date - DT_JO: Official journal publication date - Constraints: - SEXE: Gender restriction (0=both, 1=male, 2=female) - ENTENTE: Prior agreement required (Y/N) - PMSI: - MFIC_PLACE: Position in medical information file

Example:

COD_ACTE: GDFE005
NOM_COURT: résec/section des plis aryépiglottiques laryngo sans laser
NOM_LONG: Résection ou section des plis aryépiglottiques, par laryngoscopie directe sans laser

Level 2: ACTIVITE (Activity Context)¶

File: R_ACTE_IVITE.dbf (137,623 records)

Activities represent specific contexts for performing procedures (e.g., surgical vs. anesthesia).

Code: AA_CODE (14 chars) = ACTE_COD + ACTIV_COD

Fields: - COD_AA: Activity code (14 chars) - ACTE_COD + ACDT_MODIF: Link to ACTE - ACTIV_COD → R_ACTIVITE: Activity type - '1' = "1° activité chir/med" (primary surgical/medical) - '4' = "anesthésie" (anesthesia) - (5 types total) - REGROU_COD → R_REGROUPEMENT: Grouping code (44 groups like 'ADC', 'ADA') - CATMED_COD → R_CATEGORIE_MEDIC: Medical category (8 categories)

Example:

COD_AA: DGPA0161
ACTE_COD: DGPA016
ACTIV_COD: 1
REGROU_COD: ADC

Level 3: ACTIVITE_PHASE (Pricing/Execution Unit)¶

File: R_ACTE_IVITE_PHASE.dbf (137,884 records)

The detailed pricing and execution level.

Code: AAP_COD (16 chars) = AA_CODE + PHASE_COD

Fields: - COD_AAP: Activity-phase code (16 chars) - AA_COD + AADT_MODIF: Link to ACTIVITE - PHASE_COD → R_PHASE: Phase type (4 phases) - Work Unit: - UOEUVR_COD + UOEDT_EFFE → R_UNITE_OEUVRE: Work unit definition and value - Payment: - PAIEM_COD → R_PAIEMENT: Payment mode (6 modes) - Pricing Components: - COEFFICIEN: Multiplier coefficient - PU_BASE: Base unit price - SUPPLEMENT: Additional charge - Constraints: - AGE_MIN, AGE_MAX: Age restrictions - NB_SEANCES: Number of sessions - NB_DENTS: Number of teeth (dental procedures) - Scoring: - SCORE: Procedure score - PRATIQUE: Practice score - ICR: Resource consumption index

2. PRICING SYSTEM¶

R_PU_BASE (774,338 records)¶

Multi-tariff grid pricing system.

Fields: - AAP_COD: Activity-phase code - GRILLE_COD: Pricing grid identifier (allows multiple pricing schemes) - PU_BASE: Base unit price - APDT_MODIF: Modification date

Purpose: Same activity-phase can have different prices depending on the pricing grid (region, insurance type, etc.)

R_AAP_PMSI (13,749 records)¶

PMSI (Programme de Médicalisation des Systèmes d'Information) integration.

Fields: - AAP_COD: Activity-phase code - ICR: Resource consumption index - CLASSANT: Classification flag

Purpose: Integration with hospital information systems for DRG-like classification.

3. BUSINESS RULES¶

R_ASSOCIATIONS (1,205,159 records)¶

Defines which activity combinations are ALLOWED and how they affect pricing.

Fields: - AA_CODE1 + DT_MODIF1: First activity - AA_CODE2 + DT_MODIF2: Second activity - REGLE_COD → R_TB03: Association rule (20 rule types)

Purpose: When two procedures are performed together, this defines if it's allowed and what coefficient to apply.

R_INCOMPATIBILITES (107,437 records)¶

Defines which procedure combinations are FORBIDDEN.

Fields: - ACTE_COD1 + DT_MODIF1: First procedure - ACTE_COD2 + DT_MODIF2: Second procedure

Purpose: Mutual exclusions (procedures that cannot be billed together).

R_PROCEDURE (11,539 records)¶

Related procedure relationships.

Fields: - ACTE_COD + ACDT_MODIF: Source procedure - PROC_COD + PRDT_MODIF: Related procedure

Purpose: Links procedures that are related (e.g., follow-up procedures, alternative approaches).

4. MODIFIERS & EXTENSIONS¶

R_ACTIVITE_MODIFICATEUR (471,273 records)¶

Pricing modifiers that can be applied to activities.

Fields: - AA_CODE + AADT_MODIF: Activity code - MODIFI_COD → R_TB11: Modifier code

R_TB11 (554 records)¶

Modifier definitions with pricing impacts.

Fields: - COD_MODIFI: Modifier code (1 char) - LIBELLE: Description - COEF: Coefficient multiplier - FORFAIT: Flat fee amount - GRILLE_COD: Pricing grid - DT_DEBUT, DT_FIN: Temporal validity

Purpose: Defines situation-specific pricing adjustments (emergency, night shift, etc.).

R_ACTIVITE_EXTENSION (5 records)¶

Activity extensions.

Fields: - AA_COD: Activity code - EXTENS_COD → R_EXTENSION: Extension type (3 types)

5. CONTEXT & ELIGIBILITY¶

R_ACTIVITE_EXECUTANT (144,275 records)¶

Defines WHO can perform each activity.

Fields: - AA_COD + AADT_MODIF: Activity code - CATSPE_COD → R_CATE_SPEC: Specialty category (5 categories)

Purpose: Restricts activities to qualified healthcare professionals.

R_ACTE_PRESCRIPTEUR (91,347 records)¶

Defines WHO can prescribe each procedure.

Fields: - ACTE_COD + ACDT_MODIF: Procedure code - CATSPE_COD → R_CATE_SPEC: Specialty category

Purpose: Restricts prescribing authority.

R_ACTE_NAT_ASS (33,915 records)¶

Insurance nature restrictions.

Fields: - ACTE_COD: Procedure code - NATASS_COD → R_NAT_ASS: Insurance nature (8 types)

Purpose: Defines which insurance types cover which procedures.

R_ACTE_EXO_TM (84,975 records)¶

Tax exemption rules.

Fields: - ACTE_COD + ACDT_MODIF: Procedure code - EXOTM_COD → R_EXO_TM: Exemption type (6 types)

Purpose: Defines tax exemptions for certain medical procedures.

R_COMPAT_EXO_TM (5 records)¶

Compatibility rules between exemption types.

Fields: - EXOTM_COD1: First exemption - EXOTM_COD2: Second exemption

Purpose: Defines which exemptions can be combined.

R_ACTE_FORFAIT (1,438 records)¶

Package/bundle associations.

Fields: - ACTE_COD + ACDT_MODIF: Procedure code - FORFAI_COD → R_FORFAIT: Package type (5 types)

Purpose: Procedures that are part of bundled payment packages.

R_ACTE_CMUC (910 records)¶

Universal Health Coverage (Couverture Maladie Universelle Complémentaire) special pricing.

Fields: - CLE_FORFAI: Package key - ACTE_COD + MODIF_DT: Procedure code

Purpose: Special pricing rules for universal health coverage beneficiaries.

R_ACTE_COND_GEN (8,533 records)¶

General conditions for procedure execution.

Fields: - ACTE_COD: Procedure code - CONDGE_COD → R_COND_GEN: Condition code (4 conditions)

R_COND_GEN (4 records)¶

General condition definitions.

Fields: - COD_CONDGE: Condition code - LIBELLE + LIBELLE0 through LIBELLEE: Long text description (~4,000 chars)

Purpose: Detailed textual conditions for procedure execution (medical protocols, safety requirements).

6. DOMAIN-SPECIFIC EXTENSIONS¶

R_ACTIVITE_PHASE_DENT (16,322 records)¶

Dental-specific: tooth position restrictions.

Fields: - AAP_COD: Activity-phase code - DEN_NUMERO → R_DENT: Tooth position (64 positions in dental notation)

Purpose: Specifies which teeth a dental procedure applies to.

R_DENT (64 records)¶

Tooth position reference table.

Fields: - NUMERO_DEN: Tooth number - LIBELLE: Tooth description

R_ACTIVITE_PHASE_DOM (551,536 records)¶

Medical domain majorations (pricing adjustments).

Fields: - AAP_COD + APDT_MODIF: Activity-phase code - DOM_COD → R_DOM: Domain code (4 domains) - MAJORATION: Price increase factor

Purpose: Domain-specific pricing adjustments (e.g., pediatrics, geriatrics).

R_DOM (4 records)¶

Medical domain reference table.

Fields: - COD_DOM: Domain code - LIBELLE: Domain description

7. DOCUMENTATION SYSTEM¶

R_NOTE_ACTE (40,930 records)¶

Rich text annotations for procedures.

Fields: - ACTE_COD + ACDT_MODIF: Procedure code - ORDRE_NOTE: Note sequence number - TYPNOT_COD → R_TYPE_NOTE: Note type (17 types) - TEXTE_NOTE + TEXTE_NOT0 through TEXTE_NOTF: Note text (~4,000 chars) - RENVOI_COD: Cross-reference to another procedure

Purpose: Clinical notes, billing instructions, contraindications, etc.

R_NOTE_MENU (995 records)¶

Notes attached to menu classification nodes.

Fields: Same structure as R_NOTE_ACTE - MENU_COD: Menu node code

R_TYPE_NOTE (17 records)¶

Note type definitions.

Fields: - COD_TYPNOT: Note type code - LIBELLE: Description

Examples: Clinical indications, contraindications, billing notes, technical requirements.

R_GLOSSAIRE (9,709 records)¶

Medical terminology dictionary.

Fields: - LIBELLE: Term (50 chars) - DEFINITION: Definition (254 chars)

Purpose: Definitions of medical terms used throughout the database.

8. CLASSIFICATION TREE¶

R_MENU (1,725 records)¶

Hierarchical classification tree for organizing procedures.

Fields: - COD_MENU: Menu node code - COD_PERE: Parent node code (for tree structure) - RANG: Position/rank within siblings - LIBELLE: Node label

Purpose: Navigable tree structure for browsing procedures by medical specialty/body system.

Example Structure:

Root
├─ Cardiovascular Surgery
│  ├─ Coronary Procedures
│  │  ├─ CABG
│  │  └─ Angioplasty
│  └─ Valve Surgery
└─ Neurosurgery
   └─ ...

9. RMO & QUALITY RULES¶

R_RMO (1 record)¶

Références Médicales Opposables (Medical Practice Guidelines).

Fields: - COD_INTERN: Internal code - REF_RMO: RMO reference - TITRE: Title - TEXTE + TEXTE0 through TEXTEE: Guideline text (~4,000 chars)

Purpose: Evidence-based medical practice guidelines.

R_ACTIVITE_RMO (0 records - currently unused)¶

Links activities to RMO guidelines.

Fields: - AA_COD + AADT_MODIF: Activity code - INTERN_COD → R_RMO: RMO reference

R_AGRE_RADIO (0 records - currently unused)¶

Radiology accreditation types.

R_ACTIVITE_AGRE_RADIO (0 records - currently unused)¶

Links activities to radiology accreditation requirements.

R_CLASSE_DMT (1 record)¶

DMT (Dispositifs Médicaux et Technologies) classification.

R_ACTE_CLASSE_DMT (0 records - currently unused)¶

Links procedures to DMT classes.

10. TECHNICAL TABLES (TB01-TB23)¶

These define coefficient grids, context rules, and association rules with temporal validity.

Common Pattern¶

All TB tables typically include: - DT_DEBUT, DT_FIN: Temporal validity period - GRILLE_COD: Pricing grid identifier - COEF or FORFAIT: Pricing impact

R_TB02 (100 records)¶

Association pricing coefficients.

Fields: - COD_ASSONP: Association code (2 chars) - LIBELLE: Description - COEF: Coefficient multiplier - GRILLE_COD: Pricing grid - DT_DEBUT, DT_FIN: Validity period

R_TB03 (20 records)¶

Association rule definitions (used by R_ASSOCIATIONS).

Fields: - COD_REGLE: Rule code (1 char) - LIBELLE + LIBELLE0: Rule description (~500 chars) - COEF: Coefficient to apply - GRILLE_COD: Pricing grid - DT_DEBUT, DT_FIN: Validity period

Purpose: Defines how to calculate pricing when procedures are associated (e.g., "second procedure at 50%").

R_TB11 (554 records)¶

Modifier codes with pricing impacts (documented above in section 4).

R_TB22 (390 records)¶

Context combination rules.

Fields: - COD_CONTXT: First context code - COD_CONTX0: Second context code - GRILLE_COD: Pricing grid - DT_DEBUT, DT_FIN: Validity period

Purpose: Defines valid combinations of beneficiary and provider contexts.

R_CONTEXT_BN (4 records)¶

Beneficiary context definitions.

Fields: - COD_CONTXT: Context code - LIBELLE: Description

R_CONTEXT_PS (54 records)¶

Healthcare provider context definitions.

Fields: - COD_CONTXT: Context code - LIBELLE: Description

Other TB Tables¶

R_TB01 (1 record): Purpose unclear
R_TB04 through R_TB23: Various specialized technical tables with similar structure

11. REFERENCE/LOOKUP TABLES¶

R_ACTIVITE (5 records)¶

Activity type definitions.

Values: - '1' = "1° activité chir/med" (primary surgical/medical activity) - '4' = "anesthésie" (anesthesia) - (5 types total)

R_PHASE (4 records)¶

Phase type definitions.

R_FORFAIT (5 records)¶

Package type definitions.

R_EXO_TM (6 records)¶

Tax exemption type definitions.

R_NAT_ASS (8 records)¶

Insurance nature type definitions.

R_EXTENSION (3 records)¶

Extension type definitions.

R_REGROUPEMENT (44 records)¶

Grouping definitions (e.g., 'ADC', 'ADA').

R_CATE_SPEC (5 records)¶

Specialty category definitions.

R_CATEGORIE_MEDIC (8 records)¶

Medical category definitions.

R_FRAIS_DEP (4 records)¶

Travel expense type definitions.

R_PAIEMENT (6 records)¶

Payment mode definitions.

R_REMBOURSEMENT (3 records)¶

Reimbursement mode definitions.

R_TYPE (3 records)¶

Procedure type definitions.

R_UNITE_OEUVRE (1 record)¶

Work unit definition with value.

Fields: - COD_UOEUVR: Work unit code - DT_EFFET: Effective date - VALEUR: Monetary value

COMPLETE DATA MODEL DIAGRAM¶

┌─────────────────────────────────────────────────────────┐
│                      R_MENU (tree)                      │
│                  (Classification Hierarchy)              │
│                     1,725 nodes                         │
└────────────────────────┬────────────────────────────────┘
                         │
                         ▼
┌─────────────────────────────────────────────────────────┐
│                   ACTE (Procedure)                      │
│                    83,025 records                       │
│  • Identity & Names                                     │
│  • Type, Reimbursement, Travel                         │
│  • Versioning (Precedent/Suivant)                      │
│  • Constraints (Sex, Prior Agreement)                  │
├─────────────────────────────────────────────────────────┤
│  LINKED TO:                                             │
│  • Notes (R_NOTE_ACTE) - 40,930 records               │
│  • Prescribers (R_ACTE_PRESCRIPTEUR) - 91,347 rec     │
│  • Insurance (R_ACTE_NAT_ASS) - 33,915 rec            │
│  • Exemptions (R_ACTE_EXO_TM) - 84,975 rec            │
│  • Packages (R_ACTE_FORFAIT) - 1,438 rec              │
│  • Conditions (R_ACTE_COND_GEN) - 8,533 rec           │
│  • CMUC (R_ACTE_CMUC) - 910 rec                       │
│  • Procedures (R_PROCEDURE) - 11,539 rec              │
│  • Incompatibilities (R_INCOMPATIBILITES) - 107,437    │
└────────────────────────┬────────────────────────────────┘
                         │
                         ▼
┌─────────────────────────────────────────────────────────┐
│            ACTIVITE (Activity Context)                  │
│              From: R_ACTE_IVITE                         │
│                 137,623 records                         │
│  • Activity Type (5 types)                             │
│  • Regroupement (44 groups)                            │
│  • Medical Category (8 categories)                     │
├─────────────────────────────────────────────────────────┤
│  LINKED TO:                                             │
│  • Executors (R_ACTIVITE_EXECUTANT) - 144,275 rec     │
│  • Modifiers (R_ACTIVITE_MODIFICATEUR) - 471,273 rec  │
│    └─> R_TB11 (554 modifier definitions)              │
│  • Extensions (R_ACTIVITE_EXTENSION) - 5 rec          │
│  • RMO Rules (R_ACTIVITE_RMO) - 0 rec (unused)        │
│  • Associations (R_ASSOCIATIONS) - 1,205,159 rec      │
│    └─> R_TB03 (20 association rules)                  │
└────────────────────────┬────────────────────────────────┘
                         │
                         ▼
┌─────────────────────────────────────────────────────────┐
│         ACTIVITE_PHASE (Pricing Unit)                   │
│         From: R_ACTE_IVITE_PHASE                        │
│                137,884 records                          │
│  • Phase (4 phases)                                     │
│  • Payment Mode (6 modes)                               │
│  • Work Unit                                            │
│  • Pricing: Coefficient, Base Price, Supplement       │
│  • Constraints: Age, Sessions, Teeth                   │
│  • Scoring: SCORE, PRATIQUE, ICR                       │
├─────────────────────────────────────────────────────────┤
│  LINKED TO:                                             │
│  • Pricing Grids (R_PU_BASE) - 774,338 rec            │
│  • PMSI Integration (R_AAP_PMSI) - 13,749 rec         │
│  • Dental Positions (R_ACTIVITE_PHASE_DENT) - 16,322  │
│  • Medical Domains (R_ACTIVITE_PHASE_DOM) - 551,536   │
└─────────────────────────────────────────────────────────┘

RELATIONSHIP SUMMARY¶

Core Entities: 3 Levels¶

ACTE (83,025) → 2. ACTIVITE (137,623) → 3. ACTIVITE_PHASE (137,884)

Business Rules: 1,324,135 relationships¶

Associations: 1,205,159
Incompatibilities: 107,437
Procedures: 11,539

Pricing: 788,087 records¶

PU_BASE: 774,338
AAP_PMSI: 13,749

Modifiers & Context: ~1,850,000 records¶

Modifiers: 471,273
Phase-Domain: 551,536
Executors: 144,275
Prescribers: 91,347
Exemptions: 84,975
Insurance: 33,915
Phase-Dental: 16,322
Conditions: 8,533
Forfaits: 1,438
CMUC: 910
Extensions: 5

Documentation: 51,634 records¶

Notes on Acts: 40,930
Glossary: 9,709
Notes on Menu: 995

Reference Tables: 23 small lookup tables¶

Types, Phases, Domains, Payment modes, Specialties, etc.

Technical Grids: 23 TB tables¶

Coefficient grids, context rules, temporal validity

KEY DESIGN PATTERNS¶

1. Temporal Versioning¶

All core tables include DT_MODIF (modification date) as part of the key, allowing tracking of changes over time without losing historical data.

2. Text Field Splitting¶

Long text fields are split across multiple 254-character fields (e.g., NOM_LONG, NOM_LONG0, NOM_LONG1, ...) due to DBF format limitations. Applications must concatenate these fields.

3. Composite Hierarchical Keys¶

Codes are built by concatenation: - AA_CODE (14 chars) = ACTE_COD (13) + ACTIV_COD (1) - AAP_COD (16 chars) = AA_CODE (14) + PHASE_COD (2)

4. Multi-Grid Pricing¶

The same activity-phase can have different prices depending on GRILLE_COD, allowing regional variations, different insurance schemes, etc.

5. Extensive Business Rules¶

The database includes massive association (1.2M) and incompatibility (107K) matrices that define: - Which procedures can be billed together - What pricing adjustments apply when combined - Which procedures are mutually exclusive

6. Empty/Unused Tables¶

Some tables are defined but currently empty: - R_ACTIVITE_RMO (0 records) - R_AGRE_RADIO (0 records) - R_ACTIVITE_AGRE_RADIO (0 records) - R_ACTE_CLASSE_DMT (0 records)

This suggests either future functionality or deprecated features.

7. PMSI Integration¶

Hospital coding system integration via R_AAP_PMSI provides resource consumption indices (ICR) for DRG-like hospital payment systems.

SUGGESTED IMPLEMENTATION¶

For Relational Database (SQL)¶

Schema: 1. Normalize around ACTE → ACTIVITE → ACTIVITE_PHASE hierarchy 2. Keep all reference tables as lookup enums 3. Create indexes on: - All code fields + modification dates - Foreign key relationships - Text search fields (NOM_COURT, NOM_LONG) 4. Create materialized views for: - Pricing calculations (joining PU_BASE, coefficients, majorations) - Full procedure descriptions (concatenating split text fields) - Current versions (filtering by DT_FIN IS NULL)

Challenges: - Text field concatenation in queries - Temporal queries (finding version valid at specific date) - Complex pricing calculations involving multiple tables

For Document/NoSQL Database¶

Structure: 1. ACTE documents containing: - Full text fields (concatenated) - Embedded ACTIVITE array - Each ACTIVITE contains embedded ACTIVITE_PHASE array - Embedded notes array - Embedded eligibility rules (prescribers, executors) 2. Separate collections for: - Associations (too large to embed) - Incompatibilities (too large to embed) - Pricing grids (can be joined at query time) 3. Denormalize frequently accessed lookups (activity types, phases, etc.)

Advantages: - Single document read for full procedure details - No text concatenation needed - Natural hierarchical structure

Challenges: - Large document size - Complex association/incompatibility queries - Update propagation for denormalized data

For Application Cache/Search Index¶

Elasticsearch/Solr approach: 1. Index ACTE documents with full-text search on: - Concatenated names - Notes - Glossary terms 2. Facets/filters on: - Menu hierarchy - Activity types - Specialty restrictions - Date ranges (current vs. historical) 3. Store pricing and business rules in relational DB 4. Hybrid architecture: search in index, transactional operations in SQL

DATA QUALITY NOTES¶

Encoding: Files use cp850
Record Counts: Some junction tables are very large (associations: 1.2M records)
Hierarchical Integrity: Menu tree structure requires validation
Temporal Consistency: Multiple date fields must be logically consistent
Text Truncation: Long descriptions may exceed combined field limits
Empty Tables: Several tables defined but unused (may indicate incomplete export)

BUSINESS LOGIC SUMMARY¶

This database represents a complete medical billing and coding system that enforces:

Medical Rules: Which procedures can be performed by which specialists
Financial Rules: Complex pricing with coefficients, majorations, and grid-based variations
Insurance Rules: Coverage, exemptions, and reimbursement modes
Combination Rules: Which procedures can/cannot be billed together and how pricing is adjusted
Temporal Rules: Historical versioning of all procedures and pricing
Documentation: Rich clinical and billing annotations
Hospital Integration: PMSI resource indices for DRG-based systems

The system is production-grade, handling the complete medical coding needs of the French healthcare system.

VERSION INFORMATION¶

Source: CCAM database export as DBF files Location: source_dbf_data/ directory Format: dBASE III/IV format (.dbf) Total Files: 72 tables Total Records: ~4.8 million records across all tables Last Analysis: 2025-01-19