# Quick Reference: Database Schema

## 🎯 The 5 Key Tables

### 1️⃣ `lr_source_document` (21,479 rows)
**What**: Individual document records  
**Key Fields**:
- `id` - Document ID (e.g., 10000000013787)
- `document_number` - Document Number (e.g., "PL11089") ⭐
- `document_type` - Type code (111, 103, 127)
- `page_count` - Expected pages
- `issued_by` - Who issued it

### 2️⃣ `lr_transaction` (16,766 rows)
**What**: Transaction/workflow groupings  
**Key Fields**:
- `id` - Transaction ID
- `transaction_number` - Transaction Number ⭐
- `transaction_type` - Type code (links to metadata)

### 3️⃣ `lr_transaction_document` (33,306 rows)
**What**: Links transactions ↔ documents  
**Key Fields**:
- `transaction_id` - FK to lr_transaction
- `document_id` - FK to lr_source_document
- `document_index` - Order within transaction

### 4️⃣ `lr_transaction_metadata` (42 rows)
**What**: Transaction type labels  
**Key Fields**:
- `Id` - Type ID
- `label` - "Indexing and Scanning", "Application of RofO" ⭐
- `category` - Must be "transaction_type"

### 5️⃣ `lr_dictionary` (2,643 rows)
**What**: Document type labels  
**Key Fields**:
- `Id` - Type ID
- `label` - "Property File", "History Card" ⭐
- `category` - Must be "document_type"

---

## 🔗 How They Connect

```
document_number "PL11089"
    ↓
lr_source_document (3 records)
    ↓ (via lr_transaction_document)
lr_transaction (2 transactions)
    ↓ (label from lr_transaction_metadata)
"Indexing and Scanning"
```

---

## ⚡ The One Query to Rule Them All

```sql
SELECT 
    sd.document_number,                          -- PL11089
    t.transaction_number,                        -- TX-2024-001
    tm.label AS transaction_type,                -- Indexing and Scanning
    sd.id AS document_id,                        -- 10000000013787
    d.label AS document_type,                    -- History Card
    sd.page_count                                -- 1
FROM lr_source_document sd
LEFT JOIN lr_transaction_document td ON td.document_id = sd.id
LEFT JOIN lr_transaction t ON t.id = td.transaction_id
LEFT JOIN lr_transaction_metadata tm ON tm.Id = t.transaction_type AND tm.category = 'transaction_type'
LEFT JOIN lr_dictionary d ON d.Id = sd.document_type AND d.category = 'document_type'
WHERE sd.document_number = 'PL11089'
ORDER BY t.id, td.document_index;
```

---

## 📊 Real Data for PL11089

### Documents (lr_source_document):
| ID | document_number | document_type | page_count |
|----|-----------------|---------------|------------|
| 10000000013787 | PL11089 | 111 | 1 |
| 10000000013791 | PL11089 | 103 | 46 |
| 10000000013800 | PL11089 | 127 | 2 |

### Document Types (lr_dictionary):
| ID | label | category |
|----|-------|----------|
| 111 | History Card | document_type |
| 103 | Property File | document_type |
| 127 | Land Form 7 (RofO Title) | document_type |

### Result:
```
PL11089
├─ Transaction #1
│  ├─ History Card (1 page)
│  └─ Property File (46 pages)
└─ Transaction #2
   └─ Land Form 7 (2 pages)
```

---

## 🎨 UI Flow

1. User enters: `PL11089`
2. Query `lr_source_document` → Find 3 documents
3. Query `lr_transaction_document` → Find which transactions
4. Query `lr_transaction` → Get transaction details
5. Query `lr_transaction_metadata` → Get "Indexing and Scanning" label
6. Query `lr_dictionary` → Get "Property File" label
7. Display as nested cards

---

## 💡 Pro Tips

### ✅ Always Use RTRIM/LTRIM
```sql
WHERE RTRIM(LTRIM(sd.document_number)) = 'PL11089'
```
Document numbers may have trailing spaces!

### ✅ Use LEFT JOIN for Transactions
Some documents don't belong to any transaction:
```sql
LEFT JOIN lr_transaction_document td ON td.document_id = sd.id
```

### ✅ Filter by Category
Both metadata tables use `category` to separate types:
```sql
-- Transaction types
WHERE tm.category = 'transaction_type'

-- Document types
WHERE d.category = 'document_type'
```

### ✅ Check for NULL Transactions
```sql
-- Group documents without transactions
CASE 
    WHEN t.id IS NULL THEN 'No Transaction'
    ELSE tm.label
END AS transaction_label
```

---

## 🚀 Quick Test Queries

### Test 1: Count Documents per Document Number
```sql
SELECT document_number, COUNT(*) as doc_count
FROM lr_source_document
WHERE document_number = 'PL11089'
GROUP BY document_number;
-- Expected: 3
```

### Test 2: Find Transactions
```sql
SELECT DISTINCT t.id, tm.label
FROM lr_source_document sd
JOIN lr_transaction_document td ON td.document_id = sd.id
JOIN lr_transaction t ON t.id = td.transaction_id
LEFT JOIN lr_transaction_metadata tm ON tm.Id = t.transaction_type
WHERE sd.document_number = 'PL11089';
```

### Test 3: Get Document Type Labels
```sql
SELECT sd.id, d.label
FROM lr_source_document sd
LEFT JOIN lr_dictionary d ON d.Id = sd.document_type
WHERE sd.document_number = 'PL11089';
-- Expected: History Card, Property File, Land Form 7
```

---

## 📋 Column Types Reference

| Type | Description | Example |
|------|-------------|---------|
| `numeric` | Large integers | 10000000013787 |
| `nvarchar(50)` | Variable text | "PL11089" |
| `nvarchar(500)` | Long text | "Ministry of Lands..." |
| `int` | Integer | 46 |
| `datetime` | Timestamp | 2015-03-09 10:14:41 |
| `tinyint` | Boolean | 0 or 1 |

---

## 🎯 Summary

**One document number** (PL11089)  
→ Multiple **documents** (3)  
→ Grouped by **transactions** (2)  
→ Labeled with **types** (from metadata tables)

**Total rows involved**: ~55,000+ across 5 tables

**Query time**: < 100ms (with proper indexes)

---

**For complete details, see `DATABASE_SCHEMA_GUIDE.md`** 📖