Relational Model

Relational diagram

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.