| | 1 | = Relational Model = |
| | 2 | |
| | 3 | == 1. Tables == |
| | 4 | |
| | 5 | === Objects === |
| | 6 | * '''PK''': object_id |
| | 7 | * '''Attributes''': inventory_number (UNIQUE), title, creation_year, acquisition_date, description |
| | 8 | * '''FK''': site_id → Sites, current_status_id → Status_Types, found_by_user_id → Users |
| | 9 | |
| | 10 | === Sites === |
| | 11 | * '''PK''': site_id |
| | 12 | * '''Attributes''': site_name, latitude, longitude, altitude, discovery_year, is_active |
| | 13 | * '''FK''': site_type_id → Site_Types, region_id → Regions, protection_status_id → Protection_Status, municipality_id → Municipalities |
| | 14 | |
| | 15 | === Users === |
| | 16 | * '''PK''': user_id |
| | 17 | * '''Attributes''': username (UNIQUE), full_name, email (UNIQUE), password_hash |
| | 18 | * '''FK''': role_id → Roles |
| | 19 | |
| | 20 | === Institutions === |
| | 21 | * '''PK''': institution_id |
| | 22 | * '''Attributes''': name (UNIQUE), address, city |
| | 23 | |
| | 24 | === Publications === |
| | 25 | * '''PK''': publication_id |
| | 26 | * '''Attributes''': title, request_date, publisher_location |
| | 27 | * '''FK''': status_id → Status_Types, main_author_id → Authors |
| | 28 | |
| | 29 | === Treatments === |
| | 30 | * '''PK''': treatment_id |
| | 31 | * '''Attributes''': treatment_date, description |
| | 32 | * '''FK''': object_id → Objects |
| | 33 | |
| | 34 | === Fragments === |
| | 35 | * '''PK''': fragment_id |
| | 36 | * '''Attributes''': description, discovery_date |
| | 37 | * '''FK''': site_id → Sites, object_id → Objects, found_by_user_id → Users, status_id → Status_Types, parent_fragment_id → Fragments (self-reference) |
| | 38 | |
| | 39 | === Conservation_Projects === |
| | 40 | * '''PK''': project_id |
| | 41 | * '''Attributes''': project_name, start_date, end_date, budget |
| | 42 | * '''FK''': institution_id → Institutions, object_id → Objects |
| | 43 | |
| | 44 | === Exhibitions === |
| | 45 | * '''PK''': exhibition_id |
| | 46 | * '''Attributes''': name, start_date, end_date |
| | 47 | * '''FK''': location_institution_id → Institutions |
| | 48 | |
| | 49 | === Condition_Assessment === |
| | 50 | * '''PK''': assessment_id |
| | 51 | * '''Attributes''': assessment_date, notes |
| | 52 | * '''FK''': object_id → Objects, status_id → Status_Types |
| | 53 | |
| | 54 | == 2. Junction Tables (M:N Relationships) == |
| | 55 | |
| | 56 | * '''Object_Publication''' (object_id, publication_id) — Objects ↔ Publications |
| | 57 | * '''Object_Exhibition''' (object_id, exhibition_id) — Objects ↔ Exhibitions |
| | 58 | * '''Publication_Authors''' (publication_id, author_id) — Publications ↔ Authors |
| | 59 | * '''Materials_Objects''' (object_id, material_id) — Objects ↔ Materials |
| | 60 | * '''Treatment_Materials''' (treatment_id, material_id) — Treatments ↔ Materials |
| | 61 | |
| | 62 | == 3. Constraints == |
| | 63 | |
| | 64 | * UNIQUE: inventory_number, username, email, site_name+region_id |
| | 65 | * CHECK: координати во опсег за Македонија |
| | 66 | * CHECK: датуми (end_date >= start_date) |
| | 67 | * CHECK: creation_year не е во иднина |
| | 68 | * CHECK: fragment не може да си биде сам parent |
| | 69 | |
| | 70 | == 4. Design Decisions == |
| | 71 | |
| | 72 | * M:N релации се решени со помошни табели: Object_Publication, Object_Exhibition, Publication_Authors, Materials_Objects, Treatment_Materials |
| | 73 | * Self-reference: Fragments (parent_fragment_id → fragment_id) |
| | 74 | * Status_Types е генерализирана табела со поле "type" за различни видови статуси |
| | 75 | * Одделна табела User_Details за дополнителни информации за корисниците |
| | 76 | |
| | 77 | == 5. Diagram == |
| | 78 | |
| | 79 | [[Image(diagram.png)]] |
