বাংলা সারসংক্ষেপ
এন্টিটি-রিলেশনশিপ ডায়াগ্রাম (Mermaid)। মূল সম্পর্ক: Booking ↔ Customer/Supplier, Booking → Tickets → Segments, Booking → JE → Lines → Accounts, Memo → Booking + Account, Payment → Invoice → Booking।
বিস্তারিত (English)
Chapter 10.2 — Entity Relationship Diagrams
1. Purpose
This chapter visualises the relationships between core entities. It is organised by module cluster: tenancy, booking, customer & supplier, ledger, invoicing & payments, integrations, audit. Each diagram is intentionally scoped — the full schema would be unreadable in one picture.
2. Conventions
||--o{— one-to-many.||--||— one-to-one (rare; typically denormalised).}o--o{— many-to-many (junction table shown explicitly).- Optional relationships indicated by
oon the optional side. - Diagrams show only owning relationships — every entity ultimately rolls up to
partnersviapartner_id; that edge is omitted for clarity except in the tenancy diagram.
3. Tenancy & user identity
erDiagram
PARTNERS ||--o{ PARTNER_USERS : memberships
PARTNERS ||--o{ PARTNER_CURRENCIES : enabled_currencies
PARTNERS ||--o{ GROUPS : "may belong to (consolidation)"
USERS ||--o{ PARTNER_USERS : memberships
USERS ||--o{ USER_MFA_METHODS : enrolled
USERS ||--o{ PERSONAL_ACCESS_TOKENS : issues
PARTNER_USERS ||--o{ PARTNER_USER_ROLES : assigned_roles
ROLES ||--o{ PARTNER_USER_ROLES : grants
ROLES ||--o{ ROLE_PERMISSIONS : has
PERMISSIONS ||--o{ ROLE_PERMISSIONS : granted_via
GROUPS ||--o{ GROUP_PARTNERS : members
PARTNERS ||--o{ GROUP_PARTNERS : member_of
4. Customer master
erDiagram
CUSTOMERS ||--o{ CUSTOMER_ADDRESSES : has
CUSTOMERS ||--o{ CUSTOMER_CONTACTS : has
CUSTOMERS ||--o{ CUSTOMER_CREDIT_HISTORY : audit
CUSTOMERS ||--o| CUSTOMER_BALANCES : materialised
CUSTOMERS ||--o{ CUSTOMER_KYC_DOCUMENTS : holds
CUSTOMERS ||--o{ CUSTOMER_SANCTIONS_SCREENINGS : screened_by
CUSTOMERS ||--o| CUSTOMER_PRICING_PROFILES : may_have
CUSTOMERS ||--o{ CUSTOMERS : "parent_customer_id (self-ref)"
CUSTOMERS ||--o| CUSTOMER_CREDIT_BALANCE : "wallet credit"
5. Supplier master
erDiagram
SUPPLIERS ||--o{ SUPPLIER_ADDRESSES : has
SUPPLIERS ||--o{ SUPPLIER_CONTACTS : has
SUPPLIERS ||--o{ SUPPLIER_BANK_DETAILS : payout_destinations
SUPPLIERS ||--o{ SUPPLIER_BANK_HISTORY : audit
SUPPLIERS ||--o{ SUPPLIER_COMMISSION_RULES : commission_schedules
SUPPLIERS ||--o| SUPPLIER_BALANCES : materialised
SUPPLIERS ||--o{ SUPPLIER_CREDENTIALS : api_credentials
SUPPLIERS ||--o{ SUPPLIER_SANCTIONS_SCREENINGS : screened_by
6. Booking cluster
erDiagram
BOOKINGS ||--o{ BOOKING_PASSENGERS : carries
BOOKINGS ||--o{ BOOKING_PNRS : supplier_record_locators
BOOKINGS ||--o{ BOOKING_TSTS : fare_snapshots
BOOKINGS ||--o{ BOOKING_SEGMENTS : "air segments"
BOOKINGS ||--o{ BOOKING_ROOMS : "hotel rooms"
BOOKINGS ||--o{ BOOKING_SERVICES : "transfers / insurance / tours"
BOOKINGS ||--o{ BOOKING_ANCILLARIES : "bag / seat / meal"
BOOKINGS ||--o{ BOOKING_TICKETS : issued_tickets
BOOKINGS ||--o{ BOOKING_EMDS : ancillary_docs
BOOKINGS ||--o{ BOOKING_TAX_BREAKDOWN : per_xt_lines
BOOKINGS ||--o{ BOOKING_FARE_RULES : captured_rules
BOOKINGS ||--o{ BOOKING_HISTORY : state_transitions
BOOKINGS ||--o{ BOOKING_DOCUMENTS : "S3 refs"
BOOKINGS ||--o{ BOOKING_REVENUE_RECOGNITION_LOG : recognition_audit
BOOKING_TICKETS ||--o{ COMMISSION_ACCRUALS : generates
BOOKING_TICKETS ||--o{ MEMOS : "may attract ADM/ACM"
BOOKING_TICKETS ||--o{ REFUNDS : may_be_refunded
CUSTOMERS ||--o{ BOOKINGS : buys
SUPPLIERS ||--o{ BOOKINGS : supplies
7. Ledger
erDiagram
CHART_OF_ACCOUNTS ||--o{ CHART_OF_ACCOUNTS_HISTORY : versioned
CHART_OF_ACCOUNTS ||--o{ JOURNAL_ENTRY_LINES : referenced_by
JOURNAL_ENTRIES ||--o{ JOURNAL_ENTRY_LINES : contains
JOURNAL_ENTRIES ||--o| JOURNAL_ENTRIES : "reversed_by_je_id"
BOOKINGS ||--o{ JOURNAL_ENTRIES : source
INVOICES ||--o{ JOURNAL_ENTRIES : source
PAYMENTS ||--o{ JOURNAL_ENTRIES : source
PAYOUTS ||--o{ JOURNAL_ENTRIES : source
REFUNDS ||--o{ JOURNAL_ENTRIES : source
MEMOS ||--o{ JOURNAL_ENTRIES : source
ACCOUNTING_PERIODS ||--o{ JOURNAL_ENTRIES : period_assignment
FX_RATES ||--o{ JOURNAL_ENTRY_LINES : conversion
8. Invoicing & payments
erDiagram
INVOICES ||--o{ INVOICE_LINES : contains
INVOICES ||--o{ INVOICE_TAX_LINES : tax_breakdown
INVOICES ||--o| CREDIT_NOTES : "may have credit notes"
CREDIT_NOTES ||--o{ CREDIT_NOTE_LINES : contains
INVOICES ||--o{ PAYMENT_INVOICE_APPLICATIONS : settled_by
PAYMENTS ||--o{ PAYMENT_INVOICE_APPLICATIONS : applies
PAYMENTS ||--o{ PAYMENTS : "reversed_by_payment_id"
PAYOUTS ||--o{ PAYOUT_SUPPLIER_INVOICE_APPLICATIONS : applies
BOOKINGS ||--o{ INVOICE_LINES : feeds
CUSTOMERS ||--o{ INVOICES : owes
SUPPLIERS ||--o{ SUPPLIER_INVOICES : "we owe"
SUPPLIER_INVOICES ||--o{ PAYOUT_SUPPLIER_INVOICE_APPLICATIONS : settled_by
BANK_ACCOUNTS ||--o{ PAYMENTS : receives_to
BANK_ACCOUNTS ||--o{ PAYOUTS : sent_from
CASH_DRAWERS ||--o{ PAYMENTS : "cash receipts"
CASH_DRAWERS ||--o{ CASH_DRAWER_SESSIONS : open_close_events
9. Refunds, memos, commissions
erDiagram
REFUNDS ||--o{ REFUND_QUOTES : history
REFUNDS ||--o{ JOURNAL_ENTRIES : posts
REFUNDS ||--o| BOOKINGS : "against booking"
REFUNDS ||--o| BOOKING_TICKETS : "against ticket"
MEMOS ||--o{ MEMO_DOCUMENTS : evidence
MEMOS ||--o{ MEMO_DISPUTES : disputes
MEMOS ||--o{ MEMO_RECOVERIES : "customer recovery"
MEMOS ||--o| BOOKING_TICKETS : "linked to ticket"
BSP_IMPORT_FILES ||--o{ MEMOS : imported_from
COMMISSION_ACCRUALS ||--o| BOOKING_TICKETS : "ticket"
COMMISSION_ACCRUALS ||--o| SUPPLIER_COMMISSION_RULES : "rule version"
COMMISSION_SETTLEMENTS ||--o{ COMMISSION_ACCRUALS : clears
COMMISSION_DISPUTES ||--o| COMMISSION_ACCRUALS : disputes
10. Reconciliation
erDiagram
BANK_STATEMENT_FILES ||--o{ BANK_STATEMENT_LINES : parsed
BANK_STATEMENT_LINES ||--o{ RECON_MATCHES : matched_to
PAYMENTS ||--o{ RECON_MATCHES : "match target"
PAYOUTS ||--o{ RECON_MATCHES : "match target"
RECON_RUNS ||--o{ RECON_MATCHES : produced
RECON_RUNS ||--o{ RECON_EXCEPTIONS : exceptions
RECON_RUNS ||--o{ RECON_ADJUSTMENTS : adjusting_jes
GATEWAY_SETTLEMENT_FILES ||--o{ GATEWAY_SETTLEMENT_LINES : parsed
BSP_STATEMENT_FILES ||--o{ BSP_STATEMENT_LINES : parsed
SUPPLIER_STATEMENT_FILES ||--o{ SUPPLIER_STATEMENT_LINES : parsed
11. Tax & period close
erDiagram
TAX_RULES ||--o{ TAX_RULE_HISTORY : versioned
TAX_RULES ||--o{ BOOKING_TAX_LINES : applies_to
TAX_RULES ||--o{ INVOICE_TAX_LINES : applies_to
TAX_RETURNS ||--o{ TAX_RETURN_PAYMENTS : settled_by
TAX_RETURNS ||--o| ACCOUNTING_PERIODS : "covers period"
ACCOUNTING_PERIODS ||--o{ PERIOD_CLOSE_RUNS : closed_by
ACCOUNTING_PERIODS ||--o{ PERIOD_PRE_CLOSE_CHECKS : gate_state
ACCOUNTING_PERIODS ||--o{ PERIOD_SNAPSHOTS : "TB / P&L / BS / CF"
ACCOUNTING_PERIODS ||--o{ PERIOD_REOPENS : reopens
ACCOUNTING_PERIODS ||--o{ JOURNAL_ENTRIES : assigns
12. Integrations
erDiagram
SUPPLIERS ||--o{ SUPPLIER_CREDENTIALS : "api creds (encrypted)"
SUPPLIER_CREDENTIALS ||--o{ SUPPLIER_REQUEST_LOG : "every API call"
WEBHOOK_ENDPOINTS ||--o{ WEBHOOK_DELIVERIES : sent_via
WEBHOOK_DELIVERIES ||--o{ WEBHOOK_DELIVERY_ATTEMPTS : retries
PARTNERS ||--o{ WEBHOOK_ENDPOINTS : configures
13. Audit & history
erDiagram
AUDIT_LOGS ||--o| USERS : actor
AUDIT_LOGS ||--o| PERSONAL_ACCESS_TOKENS : actor
AUDIT_LOGS ||--o| BOOKINGS : "target (when applicable)"
AUDIT_LOGS ||--o| INVOICES : target
AUDIT_LOGS ||--o| JOURNAL_ENTRIES : target
AUDIT_LOGS ||--o| CUSTOMERS : target
AUDIT_LOGS ||--o| SUPPLIERS : target
CUSTOMERS ||--o{ CUSTOMERS_HISTORY : versioned
SUPPLIERS ||--o{ SUPPLIERS_HISTORY : versioned
BOOKINGS ||--o{ BOOKINGS_HISTORY : versioned
CHART_OF_ACCOUNTS ||--o{ CHART_OF_ACCOUNTS_HISTORY : versioned
TAX_RULES ||--o{ TAX_RULE_HISTORY : versioned
14. Notes on diagrams
These diagrams show logical relationships, not physical FK constraints in every case. For example:
- audit_logs.target_entity_id is polymorphic (different target types live in the same column with target_entity_type discriminator) — no FK is enforced.
- journal_entry_lines is dimensioned by customer_id, supplier_id, booking_id, etc. — multiple optional FKs.
- Reference tables (airport_codes, currencies) are platform-shared; their relationships to per-partner tables are loose (no FK across partitions).
The physical FK enforcement matrix is documented in Chapter 10.3 — Table Reference.
15. Common pitfalls
- ⚠️ Treating polymorphic columns as FKs. The
target_entity_type + target_entity_idpattern in audit logs is not a single FK; queries must join via discriminator. - ⚠️ Cascading deletes through booking history. Booking history is the audit chain — never delete.
- ⚠️ Inferring relationships from naming. Always verify via Chapter 10.3.