# Complete Database Schema Guide ## 📊 Overview Based on `database_schema.txt` analysis for the PLAGIS Aumentum system. **Database**: LRS43 (SQL Server) **Total Tables**: 169 tables (168 in LRSAdmin schema) **Key Tables for Document Hierarchy**: 5 tables --- ## 🎯 Three-Level Hierarchy Tables ### Level 1: Document Number **Table**: `lr_source_document` ### Level 2: Transaction Grouping **Tables**: `lr_transaction`, `lr_transaction_document` (junction) ### Level 3: Document Types **Metadata Tables**: `lr_dictionary`, `lr_transaction_metadata` --- ## 📋 Table Details ### 1. `LRSAdmin.lr_source_document` (21,479 rows) **Purpose**: Stores individual documents/files **Key Columns**: ```sql id numeric NOT NULL -- Primary Key (e.g., 10000000013787) Hjtype nvarchar(255) NOT NULL -- Java class type document_number nvarchar(50) NULL -- ⭐ Document Number (e.g., "PL11089") ref_number nvarchar(50) NULL -- Reference number minute_number nvarchar(50) NULL -- Minute number document_type numeric NOT NULL -- ⭐ Links to lr_dictionary (e.g., 111, 103, 127) duplicate tinyint NULL -- Is duplicate? access_type numeric NOT NULL -- Access control acceptance datetime NULL -- Acceptance date recordation datetime NULL -- Recordation date submission datetime NULL -- Submission date issued_by nvarchar(500) NULL -- Issuing authority page_count int NULL -- ⭐ Expected page count comments nvarchar(800) NULL -- Comments create_date datetime NULL -- Created timestamp modified_at datetime NULL -- Last modified timestamp modified_by nvarchar(255) NULL -- Modified by user property_file_page nvarchar(50) NULL -- Property file page reference sdoc1 nvarchar(50) NULL -- Custom field 1 sdoc2 nvarchar(50) NULL -- Custom field 2 sdoc_dict1 numeric NOT NULL -- Dictionary reference 1 sdoc_dict2 numeric NOT NULL -- Dictionary reference 2 sdoc_count1 int NULL -- Count field 1 sdoc_count2 int NULL -- Count field 2 version numeric NULL -- Version number begin_lifespan_version datetime NULL -- Lifecycle start end_lifespan_version datetime NULL -- Lifecycle end ``` **Example Data for PL11089**: | id | document_number | document_type | page_count | issued_by | submission | |----|-----------------|---------------|------------|-----------|------------| | 10000000013787 | PL11089 | 111 | 1 | Ministry of Lands (MLSTP) | 1989-02-08 | | 10000000013791 | PL11089 | 103 | 46 | Ministry of Lands (MLSTP) | 1988-08-01 | | 10000000013800 | PL11089 | 127 | 2 | Director General | 1989-02-15 | --- ### 2. `LRSAdmin.lr_transaction` (16,766 rows) **Purpose**: Groups documents into transactions (workflows/processes) **Key Columns**: ```sql id numeric NOT NULL -- Primary Key Hjtype nvarchar(255) NOT NULL -- Java class type transaction_number nvarchar(50) NULL -- ⭐ Transaction Number (e.g., "TX-2024-001") transaction_type numeric NULL -- ⭐ Links to lr_transaction_metadata transaction_status numeric NOT NULL -- Status code transaction_source numeric NOT NULL -- Source code source_property_ids nvarchar(500) NULL -- Source property IDs target_property_ids nvarchar(500) NULL -- Target property IDs old_property_id nvarchar(50) NULL -- Old property reference parent_property_ids nvarchar(500) NULL -- Parent properties ... and 88 more columns ``` **Sample Queries**: ```sql -- Get all transactions SELECT id, transaction_number, transaction_type FROM LRSAdmin.lr_transaction WHERE id = 123456; -- Get transaction with type label SELECT t.id, t.transaction_number, t.transaction_type, tm.label AS transaction_type_label FROM LRSAdmin.lr_transaction t LEFT JOIN LRSAdmin.lr_transaction_metadata tm ON tm.Id = t.transaction_type AND tm.category = 'transaction_type' WHERE t.id = 123456; ``` --- ### 3. `LRSAdmin.lr_transaction_document` (33,306 rows) **Purpose**: Junction table linking transactions to documents (many-to-many) **Columns**: ```sql transaction_id numeric NOT NULL -- ⭐ FK to lr_transaction.id document_id numeric NOT NULL -- ⭐ FK to lr_source_document.id document_index int NOT NULL -- Order/sequence number ``` **Primary Key**: Composite (transaction_id, document_id, document_index) **Example**: | transaction_id | document_id | document_index | |----------------|-------------|----------------| | 123456 | 10000000013787 | 1 | | 123456 | 10000000013791 | 2 | | 123457 | 10000000013800 | 1 | **Purpose**: - One transaction can have multiple documents - One document can belong to multiple transactions - `document_index` maintains order within transaction --- ### 4. `LRSAdmin.lr_transaction_metadata` (42 rows) **Purpose**: Defines transaction types (like "Indexing and Scanning", "Application of RofO") **Key Columns**: ```sql Id numeric NOT NULL -- Primary Key Hjtype nvarchar(255) NOT NULL -- Java class type code nvarchar(64) NOT NULL -- Type code label nvarchar(128) NOT NULL -- ⭐ Display label (e.g., "Indexing and Scanning") descr nvarchar(1000) NULL -- Description category nvarchar(64) NOT NULL -- ⭐ Category (e.g., "transaction_type") general_term tinyint NULL -- Is general term? sort_order int NULL -- Display order hidden tinyint NULL -- Is hidden? group_name nvarchar(255) NULL -- Group name ... and 32 more columns ``` **Common Transaction Types**: ```sql -- Query to see all transaction types SELECT Id, code, label, descr FROM LRSAdmin.lr_transaction_metadata WHERE category = 'transaction_type' ORDER BY sort_order; ``` **Example Values** (hypothetical based on typical LRS usage): | Id | code | label | category | |----|------|-------|----------| | 5 | IDX_SCAN | Indexing and Scanning | transaction_type | | 10 | APP_RFO | Application of Right of Occupancy | transaction_type | | 15 | TITLE_REG | Title Registration | transaction_type | | 20 | SURVEY | Survey and Mapping | transaction_type | --- ### 5. `LRSAdmin.lr_dictionary` (2,643 rows) **Purpose**: Defines document types and other lookup values **Key Columns**: ```sql Id numeric NOT NULL -- Primary Key Hjtype nvarchar(255) NOT NULL -- Java class type code nvarchar(64) NOT NULL -- Type code label nvarchar(254) NOT NULL -- ⭐ Display label descr nvarchar(1000) NULL -- Description category nvarchar(64) NOT NULL -- ⭐ Category (e.g., "document_type") general_term tinyint NULL -- Is general term? sort_order int NULL -- Display order hidden tinyint NULL -- Is hidden? version numeric NULL -- Version ... and 5 more columns ``` **Document Types for PL11089**: ```sql SELECT Id, code, label, descr FROM LRSAdmin.lr_dictionary WHERE category = 'document_type' AND Id IN (111, 103, 127); ``` **Known Document Types**: | Id | code | label | category | |----|------|-------|----------| | 103 | PROP_FILE | Property File | document_type | | 111 | HIST_CARD | History Card | document_type | | 127 | LAND_FORM_7 | Land Form 7 (Right of Occupancy Title) | document_type | --- ## 🔗 Relationships Diagram ``` ┌─────────────────────────────────────┐ │ lr_source_document │ │ (Individual Documents) │ │ │ │ • id (PK) │ │ • document_number: "PL11089" │ ← Level 1 │ • document_type: 111 │ ──┐ │ • page_count: 1 │ │ │ • issued_by: "..." │ │ └──────────────┬──────────────────────┘ │ │ │ │ M:M relationship │ │ │ ↓ │ ┌─────────────────────────────────────┐ │ │ lr_transaction_document │ │ │ (Junction Table) │ │ │ │ │ │ • transaction_id (FK) │ │ │ • document_id (FK) │ │ │ • document_index │ │ └──────────────┬──────────────────────┘ │ │ │ │ M:1 relationship │ │ │ ↓ │ ┌─────────────────────────────────────┐ │ │ lr_transaction │ │ │ (Transaction Groupings) │ │ │ │ │ │ • id (PK) │ │ │ • transaction_number: "TX-..." │ ← Level 2 │ • transaction_type: 5 │ ──┐ └──────────────┬──────────────────────┘ │ │ │ │ │ │ M:1 (type lookup) │ │ │ │ │ ↓ │ │ ┌─────────────────────────────────────┐ │ │ │ lr_transaction_metadata │ │ │ │ (Transaction Type Labels) │ │ │ │ │ │ │ │ • Id (PK) │ │ │ │ • label: "Indexing and Scanning" │ ──┘ │ │ • category: "transaction_type" │ │ └─────────────────────────────────────┘ │ │ M:1 (type lookup) │ │ ┌─────────────────────────────────────┐ │ │ lr_dictionary │ │ │ (Document Type Labels) │ │ │ │ │ │ • Id (PK) │ │ │ • label: "Property File" │ ←───┘ │ • category: "document_type" │ └─────────────────────────────────────┘ ``` --- ## 🔍 Complete Query for Three-Level Hierarchy ```sql -- Get complete hierarchy for a document number SELECT -- Level 1: Document Number sd.document_number, -- Level 2: Transaction Info t.id AS transaction_id, t.transaction_number, t.transaction_type, tm.label AS transaction_type_label, tm.descr AS transaction_type_description, -- Level 3: Document Info sd.id AS document_id, sd.document_type, d.label AS document_type_label, d.descr AS document_type_description, sd.page_count, sd.issued_by, sd.submission, sd.acceptance, sd.recordation, sd.comments, -- Junction Table Info td.document_index AS order_in_transaction FROM LRSAdmin.lr_source_document sd -- Join to transaction-document junction LEFT JOIN LRSAdmin.lr_transaction_document td ON td.document_id = sd.id -- Join to transaction LEFT JOIN LRSAdmin.lr_transaction t ON t.id = td.transaction_id -- Join to get transaction type label LEFT JOIN LRSAdmin.lr_transaction_metadata tm ON tm.Id = t.transaction_type AND tm.category = 'transaction_type' -- Join to get document type label LEFT JOIN LRSAdmin.lr_dictionary d ON d.Id = sd.document_type AND d.category = 'document_type' WHERE RTRIM(LTRIM(sd.document_number)) = 'PL11089' ORDER BY t.id, -- Group by transaction td.document_index, -- Then by order within transaction sd.id; -- Then by document ID ``` --- ## 📊 Example Output for PL11089 ### Query Result: | document_number | transaction_id | transaction_number | transaction_type_label | document_id | document_type_label | page_count | |-----------------|----------------|-------------------|----------------------|-------------|---------------------|------------| | PL11089 | 123456 | TX-2024-001 | Indexing and Scanning | 10000000013787 | History Card | 1 | | PL11089 | 123456 | TX-2024-001 | Indexing and Scanning | 10000000013791 | Property File | 46 | | PL11089 | 123457 | TX-2024-002 | Application of RofO | 10000000013800 | Land Form 7 | 2 | ### Grouped View: ``` PL11089 ├─ Transaction: TX-2024-001 (Indexing and Scanning) │ ├─ History Card (ID: 10000000013787) - 1 page │ └─ Property File (ID: 10000000013791) - 46 pages │ └─ Transaction: TX-2024-002 (Application of RofO) └─ Land Form 7 (ID: 10000000013800) - 2 pages ``` --- ## 🎯 Important Notes ### 1. **Document Numbers Can Have Duplicates** ```sql -- Check for documents with same document_number SELECT document_number, COUNT(*) as count FROM LRSAdmin.lr_source_document WHERE document_number = 'PL11089' GROUP BY document_number; -- Result: 3 rows for PL11089 ``` ### 2. **Documents May Have No Transaction** Some documents exist without being part of any transaction. Handle with LEFT JOIN. ```sql -- Documents without transactions will have NULL transaction_id SELECT sd.id, sd.document_number, td.transaction_id FROM LRSAdmin.lr_source_document sd LEFT JOIN LRSAdmin.lr_transaction_document td ON td.document_id = sd.id WHERE sd.document_number = 'PL11089'; ``` ### 3. **Transaction Types Are Extensible** The `lr_transaction_metadata` table can have custom transaction types defined by the organization. ```sql -- Get all available transaction types SELECT Id, label, descr FROM LRSAdmin.lr_transaction_metadata WHERE category = 'transaction_type' AND hidden = 0 ORDER BY sort_order; ``` ### 4. **Document Types Are from lr_dictionary** The `document_type` field in `lr_source_document` is a foreign key to `lr_dictionary.Id` where `category = 'document_type'`. ```sql -- Get all document types SELECT Id, code, label, descr FROM LRSAdmin.lr_dictionary WHERE category = 'document_type' AND hidden = 0 ORDER BY sort_order; ``` --- ## 🔧 Optimized Queries ### Get All Transactions for a Document Number (Fast) ```sql SELECT DISTINCT t.id AS transaction_id, t.transaction_number, tm.label AS transaction_type_label, COUNT(td.document_id) AS document_count FROM LRSAdmin.lr_source_document sd JOIN LRSAdmin.lr_transaction_document td ON td.document_id = sd.id JOIN LRSAdmin.lr_transaction t ON t.id = td.transaction_id LEFT JOIN LRSAdmin.lr_transaction_metadata tm ON tm.Id = t.transaction_type WHERE RTRIM(LTRIM(sd.document_number)) = 'PL11089' GROUP BY t.id, t.transaction_number, tm.label ORDER BY t.id; ``` ### Get Documents for a Specific Transaction ```sql SELECT sd.id AS document_id, d.label AS document_type_label, sd.page_count, sd.issued_by, td.document_index FROM LRSAdmin.lr_transaction_document td JOIN LRSAdmin.lr_source_document sd ON sd.id = td.document_id LEFT JOIN LRSAdmin.lr_dictionary d ON d.Id = sd.document_type WHERE td.transaction_id = 123456 ORDER BY td.document_index; ``` --- ## 📝 Summary ### Tables Count by Row Count: - `lr_transaction_document`: **33,306 rows** (most used - junction table) - `lr_source_document`: **21,479 rows** (all documents) - `lr_transaction`: **16,766 rows** (all transactions) - `lr_dictionary`: **2,643 rows** (all lookup values) - `lr_transaction_metadata`: **42 rows** (transaction type definitions) ### Data Flow: 1. **User searches**: Document Number (PL11089) 2. **System finds**: 3 documents in `lr_source_document` 3. **System looks up**: Transactions in `lr_transaction_document` 4. **System groups**: By transaction from `lr_transaction` 5. **System labels**: Using `lr_transaction_metadata` and `lr_dictionary` 6. **UI displays**: Transaction cards with nested document lists --- **This schema guide is based on actual database structure from `database_schema.txt`** ✅