Changes between Initial Version and Version 1 of ERModel

Timestamp:

01/19/26 16:41:22 (3 weeks ago)

Author:

221511

Comment:

--

ERModel

@@ +1 @@
+= Entity-Relationship Model v.01 =
+
+== Diagram ==
+
+[[Image(diagrams/ERModel_v01.png, width=100%)]]
+
+== Data Requirements ==
+
+=== Entities ===
+
+==== Users ====
+'''Description:''' Users represents all individuals who interact with the system. This includes teaching staff who reserve resources for lectures and research, students who view availability and access permitted resources, and faculty administrators who manage the system. The entity is central to tracking who makes reservations and who approves them.
+
+'''Candidate Keys:'''
+ * Primary key: user_id (chosen as a surrogate key for efficiency and to avoid issues with changing natural identifiers)
+ * Alternative candidates: email (unique per user, but may change)
+
+'''Attributes:'''
+||'''Attribute'''||'''Data Type'''||'''Constraints'''||'''Description'''||
+||user_id||INTEGER||PK, NOT NULL||Unique identifier for each user||
+||first_name||VARCHAR(128)||NOT NULL||User's first name||
+||last_name||VARCHAR(128)||NOT NULL||User's family name||
+||email||VARCHAR(128)||NOT NULL, UNIQUE||Institutional email address used for login||
+||password||VARCHAR(128)||NOT NULL||Hashed password for authentication||
+
+==== UserTypes ====
+'''Description:''' UserTypes categorizes users into different roles within the system. This entity enables role-based access control, distinguishing between teaching staff, students, and faculty administrators. Each role has different permissions for viewing, reserving, and managing resources.
+
+'''Candidate Keys:'''
+ * Primary key: type_id (surrogate key for referential integrity)
+ * Alternative candidates: type_name (unique, but surrogate preferred for foreign key references)
+
+'''Attributes:'''
+||'''Attribute'''||'''Data Type'''||'''Constraints'''||'''Description'''||
+||type_id||INTEGER||PK, NOT NULL||Unique identifier for each user type||
+||type_name||VARCHAR(128)||NOT NULL, UNIQUE||Name of the role (e.g., 'Student', 'Teaching Staff', 'Administrator')||
+||description||VARCHAR(128)||NOT NULL||Detailed description of the role's permissions and purpose||
+
+==== Resources ====
+'''Description:''' Resources represents all physical and digital items that can be reserved within the faculty. Physical resources include laboratory equipment, projectors, rooms, and specialized tools. Digital resources may include software licenses, virtual machines, or online services. This entity is central to the reservation system.
+
+'''Candidate Keys:'''
+ * Primary key: resource_id (surrogate key for efficiency)
+ * Alternative candidates: name (if unique within the system)
+
+'''Attributes:'''
+||'''Attribute'''||'''Data Type'''||'''Constraints'''||'''Description'''||
+||resource_id||INTEGER||PK, NOT NULL||Unique identifier for each resource||
+||name||VARCHAR(128)||NOT NULL||Descriptive name of the resource||
+||description||VARCHAR(128)||NOT NULL||Detailed description including specifications, capacity, or usage instructions||
+
+==== ResourceTypes ====
+'''Description:''' ResourceTypes categorizes resources into different types for easier navigation and filtering. Examples include 'Laboratory Equipment', 'Classroom', 'Software License', 'Projector', etc. The is_physical attribute distinguishes between physical resources (that have a location) and digital resources.
+
+'''Candidate Keys:'''
+ * Primary key: type_id (surrogate key for referential integrity)
+ * Alternative candidates: type_name (unique, but surrogate preferred)
+
+'''Attributes:'''
+||'''Attribute'''||'''Data Type'''||'''Constraints'''||'''Description'''||
+||type_id||INTEGER||PK, NOT NULL||Unique identifier for each resource type||
+||type_name||VARCHAR(128)||NOT NULL, UNIQUE||Name of the category (e.g., 'Projector', 'Laboratory', 'Software')||
+||is_physical||VARCHAR(128)||NOT NULL||Indicates if resources of this type are physical ('Y') or digital ('N')||
+
+==== Locations ====
+'''Description:''' Locations stores information about physical places where resources can be found. This enables users to know where to pick up or use a resource. Only physical resources are linked to locations; digital resources do not require this association.
+
+'''Candidate Keys:'''
+ * Primary key: location_id (surrogate key for efficiency)
+ * Alternative candidates: (building, room) composite key
+
+'''Attributes:'''
+||'''Attribute'''||'''Data Type'''||'''Constraints'''||'''Description'''||
+||location_id||INTEGER||PK, NOT NULL||Unique identifier for each location||
+||building||VARCHAR(128)||NOT NULL||Building name or code (e.g., 'TMF', 'FINKI-A')||
+||room||VARCHAR(128)||NOT NULL||Room number or name within the building||
+
+==== Reservations ====
+'''Description:''' Reservations is the core transactional entity that records each booking of a resource by a user. It tracks the time period, status (pending, approved, rejected, completed), and purpose of each reservation. This entity connects users with resources and enables conflict detection and usage analytics.
+
+'''Candidate Keys:'''
+ * Primary key: reservation_id (surrogate key for unique identification)
+ * Alternative candidates: None suitable (combination of user, resource, and time could work but is complex)
+
+'''Attributes:'''
+||'''Attribute'''||'''Data Type'''||'''Constraints'''||'''Description'''||
+||reservation_id||INTEGER||PK, NOT NULL||Unique identifier for each reservation||
+||start_time||TIMESTAMP||NOT NULL||When the reservation begins||
+||end_time||TIMESTAMP||NOT NULL||When the reservation ends (must be after start_time)||
+||status||VARCHAR(128)||NOT NULL||Current status: 'pending', 'approved', 'rejected', 'completed', 'cancelled'||
+||purpose||VARCHAR(128)||NOT NULL||Description of why the resource is being reserved||
+||created_at||TIMESTAMP||NOT NULL||When the reservation was created in the system||
+
+==== AvailabilitySlots ====
+'''Description:''' AvailabilitySlots defines recurring time windows when resources are available for reservation. Rather than storing availability for each specific date, this entity stores patterns (e.g., "Mondays 9:00-17:00") that apply weekly. This simplifies availability management and reduces data redundancy.
+
+'''Candidate Keys:'''
+ * Primary key: slot_id (surrogate key for efficiency)
+ * Alternative candidates: None suitable (same day/time combination may apply to different resources)
+
+'''Attributes:'''
+||'''Attribute'''||'''Data Type'''||'''Constraints'''||'''Description'''||
+||slot_id||INTEGER||PK, NOT NULL||Unique identifier for each availability slot||
+||day_of_week||VARCHAR(128)||NOT NULL||Day name (e.g., 'Monday', 'Tuesday') or number (1-7)||
+||start_time||TIME||NOT NULL||When the availability window starts on that day||
+||end_time||TIME||NOT NULL||When the availability window ends (must be after start_time)||
+
+==== RecurrencePatterns ====
+'''Description:''' RecurrencePatterns enables reservations to repeat automatically according to defined rules. This is useful for recurring events like weekly lectures or monthly equipment maintenance. Instead of creating multiple individual reservations, a single reservation can reference a pattern that defines how it repeats.
+
+'''Candidate Keys:'''
+ * Primary key: pattern_id (surrogate key for unique identification)
+ * Alternative candidates: None suitable
+
+'''Attributes:'''
+||'''Attribute'''||'''Data Type'''||'''Constraints'''||'''Description'''||
+||pattern_id||INTEGER||PK, NOT NULL||Unique identifier for each recurrence pattern||
+||pattern_type||VARCHAR(128)||NOT NULL||Type of recurrence: 'daily', 'weekly', 'biweekly', 'monthly'||
+||interval_days||INTEGER||NOT NULL||Number of days between occurrences||
+||end_date||DATE||NULL||When the recurrence ends (NULL means indefinite)||
+
+=== Relationships ===
+
+==== has_type (Users - UserTypes) ====
+'''Description:''' Each user is assigned exactly one user type that determines their role and permissions in the system. A user type can be assigned to many users. This is a mandatory relationship for users (total participation) since every user must have a defined role.
+
+'''Cardinality:''' N:1 (Many users to one user type)
+
+'''Participation:'''
+ * Users: Total (every user must have a type)
+ * UserTypes: Partial (a type may exist without assigned users)
+
+==== makes (Users - Reservations) ====
+'''Description:''' Users create reservations for resources. One user can make multiple reservations over time, but each reservation is made by exactly one user. This relationship tracks who requested each reservation.
+
+'''Cardinality:''' 1:N (One user makes many reservations)
+
+'''Participation:'''
+ * Users: Partial (not all users make reservations)
+ * Reservations: Total (every reservation has a creator)
+
+==== approves (Users - Reservations) ====
+'''Description:''' Faculty administrators approve or reject reservations. This relationship is separate from 'makes' to distinguish between the user who requested a reservation and the administrator who processed it. One administrator can approve many reservations.
+
+'''Cardinality:''' 1:N (One administrator approves many reservations)
+
+'''Participation:'''
+ * Users: Partial (only administrators approve)
+ * Reservations: Partial (pending reservations have no approver yet)
+
+==== reserved_in (Reservations - Resources) ====
+'''Description:''' Each reservation is for exactly one resource. A resource can have many reservations over time (though not overlapping for the same time period). This is a mandatory relationship for reservations since every booking must specify what is being reserved.
+
+'''Cardinality:''' N:1 (Many reservations to one resource)
+
+'''Participation:'''
+ * Reservations: Total (every reservation must be for a resource)
+ * Resources: Partial (a resource may have no reservations)
+
+==== categorized_as (Resources - ResourceTypes) ====
+'''Description:''' Each resource belongs to exactly one resource type for categorization purposes. This enables filtering and determines whether the resource is physical (requires location) or digital. The relationship is mandatory for resources.
+
+'''Cardinality:''' N:1 (Many resources to one resource type)
+
+'''Participation:'''
+ * Resources: Total (every resource must have a type)
+ * ResourceTypes: Partial (a type may exist without resources)
+
+==== located_at (Resources - Locations) ====
+'''Description:''' Physical resources are associated with a location where they can be found or used. Digital resources do not have locations. One location can house multiple resources (e.g., a lab room with multiple pieces of equipment).
+
+'''Cardinality:''' N:1 (Many resources to one location)
+
+'''Participation:'''
+ * Resources: Partial (only physical resources have locations)
+ * Locations: Partial (a location may have no resources assigned)
+
+==== available_during (Resources - AvailabilitySlots) ====
+'''Description:''' This many-to-many relationship defines when each resource is available for reservation. A resource can have multiple availability slots (e.g., available Monday-Friday 8:00-18:00), and the same slot pattern can apply to multiple resources.
+
+'''Cardinality:''' M:N (Many resources to many availability slots)
+
+'''Participation:'''
+ * Resources: Partial (some resources may be available anytime)
+ * AvailabilitySlots: Partial (slots are defined independently)
+
+==== follows (Reservations - RecurrencePatterns) ====
+'''Description:''' Reservations can optionally follow a recurrence pattern for repeating bookings. When a reservation references a pattern, the system generates or recognizes repeated occurrences according to the pattern rules. Most reservations are one-time and do not use this relationship.
+
+'''Cardinality:''' N:1 (Many reservations to one pattern)
+
+'''Participation:'''
+ * Reservations: Partial (only recurring reservations have patterns)
+ * RecurrencePatterns: Partial (patterns exist independently)
+
+== Entity-Relationship Model History ==
+
+||'''Version'''||'''Date'''||'''Changes'''||
+||v01||2025-01-19||Initial version with 8 entities and 8 relationships||
+
+== Attachments ==
+
+ * [[attachment:diagrams/ERModel_v01.xml|ERModel_v01.xml]] (TerraER source file)
+ * [[attachment:diagrams/ERModel_v01.png|ERModel_v01.png]] (Exported diagram image)