Changes between Initial Version and Version 1 of RelationalModel

Timestamp:

04/20/26 08:48:47 (12 days ago)

Author:

231504

Comment:

--

RelationalModel

@@ +1 @@
+= Relational Model =
+
+== Relational diagram ==
+
+[[Image(geoturism1.7.jpeg)]]
+
+== Description ==
+
+The relational model represents the structured implementation of the geospatial tourism system described in the project. The model is designed to support efficient storage, querying, and management of tourism-related data, with a particular focus on geospatial relationships and travel planning.
+
+The schema includes several core components:
+
+* User management (users, roles, permissions)
+* Trip organization and execution (trips, trip_executions, trip_participants)
+* Geographical hierarchy (countries, regions, cities, locations, coordinates)
+* Tourism services (hotels, rooms, attractions, facilities)
+* Transportation systems (flight routes, transport routes, stations, bookings)
+* User-generated content (reviews, photos)
+
+Relationships between entities are implemented using foreign keys, ensuring referential integrity. Many-to-many relationships are resolved using associative tables such as user_role_map, attraction_type_map, and trip_locations.
+
+== Modeling decisions and explanation ==
+
+'''Geographical hierarchy'''
+
+The geographical data is modeled using multiple normalized tables (countries, regions, cities, locations, coordinates). This allows flexible querying and avoids redundancy. Coordinates are separated into their own table to support potential spatial extensions and indexing.
+
+'''Trip structure'''
+
+Trips are separated into trips, trip_executions, and trip_participants. This design allows a single trip definition to have multiple executions over time, with different participants for each execution.
+
+'''Bookings and transportation'''
+
+Transportation is modeled using separate entities for flight routes, transport routes, and bookings (flight_bookings, bus_bookings). This separation allows flexibility in handling different transport types and supports realistic travel scenarios.
+
+'''Accommodation system'''
+
+Hotels and rooms are modeled separately, with an additional linking table (trip_participants_hotel_rooms) to associate participants with specific accommodations during trips.
+
+'''User roles and permissions'''
+
+User roles are implemented using a separate roles table and a mapping table (user_role_map). This allows flexible assignment of multiple roles to users and supports role-based access control.
+
+== Notes ==
+
+The model is normalized to reduce redundancy and ensure consistency. It is designed to support efficient querying, especially for complex operations such as route planning and location-based searches.
+
+Future improvements may include additional indexing strategies and further optimization of spatial queries using PostGIS.
+
+== Approval and development process ==
+
+The model was developed iteratively during laboratory sessions and refined through consultations with teaching assistants and professors. Feedback was incorporated into multiple revisions of the model, leading to the final version presented here.