Columns
| Column | Type | Size | Nulls | Auto | Default | Children | Parents | Comments | |||
|---|---|---|---|---|---|---|---|---|---|---|---|
| id | uuid | 2147483647 | null |
|
|
||||||
| prison_code | varchar | 6 | null |
|
|
||||||
| staff_id | int8 | 19 | null |
|
|
||||||
| person_identifier | varchar | 10 | null |
|
|
||||||
| allocated_at | timestamp | 29,6 | null |
|
|
||||||
| allocated_by | varchar | 64 | null |
|
|
||||||
| allocation_reason_id | int8 | 19 | null |
|
|
||||||
| deallocated_at | timestamp | 29,6 | √ | null |
|
|
|||||
| deallocation_reason_id | int8 | 19 | √ | null |
|
|
|||||
| deallocated_by | varchar | 64 | √ | null |
|
|
|||||
| is_active | bool | 1 | null |
|
|
||||||
| policy_code | varchar | 16 | null |
|
|
||||||
| version | int4 | 10 | 0 |
|
|
Indexes
| Constraint Name | Type | Sort | Column(s) |
|---|---|---|---|
| pk_allocation | Primary key | Asc | id |
| idx_active_staff_allocations | Performance | Asc/Asc/Asc | staff_id + prison_code + policy_code |
| idx_allocation_person_identifier | Performance | Asc/Asc | person_identifier + policy_code |
| idx_allocation_person_identifier_prison_code_policy | Performance | Asc/Asc/Asc | person_identifier + prison_code + policy_code |
| idx_allocation_prison_code | Performance | Asc/Asc | prison_code + policy_code |
| idx_allocation_staff_id_allocated_at_deallocated_at | Performance | Asc/Asc/Asc/Asc | staff_id + allocated_at + deallocated_at + policy_code |
| idx_allocation_with_and_at | Performance | Asc/Asc/Asc/Asc/Asc/Asc | staff_id + person_identifier + allocated_at + policy_code + prison_code + deallocated_at |
| uq_allocation | Must be unique | Asc/Asc | person_identifier + policy_code |
Check Constraints
| Constraint Name | Constraint |
|---|---|
| ch_allocation_is_not_active | ((NOT ((is_active = false) AND ((deallocated_at IS NULL) OR (deallocated_by IS NULL) OR (deallocation_reason_id IS NULL))))) |
| ch_allocation_is_active | ((NOT ((is_active = true) AND ((deallocated_at IS NOT NULL) OR (deallocated_by IS NOT NULL) OR (deallocation_reason_id IS NOT NULL))))) |