Database Schema Documentation - DeepBlockDeepak/RoadTripPlanner GitHub Wiki

This documentation provides an overview of the database schema used in the Flask application, focusing on the models, their relationships, and the design rationale behind them.

Entity-Relationship Diagram

Models Overview

User

  • Description: Represents a user of the application.
  • Attributes:
    • id: Primary key.
    • username: Unique username.
    • email: Unique email address.
    • password_hash: Hashed password for security.
    • budget: Stores the user's budget.
    • joined_at: Timestamp of account creation.
  • Relationships:
    • Linked to Favoritelist, Searchlist, and Travellist via foreign keys.
    • One-to-many relationship with Blurb.

Place

  • Description: Represents a city or location.
  • Attributes:
    • id: Primary key.
    • city: City name.
    • state: State name.
    • population: Population of the city.
    • activities: Activities available in the city.
    • wiki: Wikipedia content related to the city.
    • times_favorited: Count of how many times favorited.
    • times_searched: Count of how many times searched.
  • Relationships:
    • Many-to-many relationship with User through Favoriteitem and Searchitem.

Blurb

  • Description: Allows users to write short messages or "tweets."
  • Attributes:
    • id: Primary key.
    • content: Content of the blurb.
    • author_id: Foreign key to User.

Favoriteitem and Searchitem

  • Description: Junction tables for the many-to-many relationships between User and Place.
  • Attributes:
    • id: Primary key.
    • place_id: Foreign key to Place.
    • favoritelist_id/searchlist_id: Foreign key to Favoritelist/Searchlist.

Travel

  • Description: Represents a trip between two locations.
  • Attributes:
    • id: Primary key.
    • origin_place_id: Foreign key to Place (origin).
    • destination_place_id: Foreign key to Place (destination).
    • price: Cost of the trip.
  • Relationships:
    • Many-to-many relationship with Place through Travelplaceitem.

List Models (Favoritelist, Searchlist, Travellist)

  • Description: Represents lists associated with a user.
  • Attributes:
    • id: Primary key.
  • Relationships:
    • One-to-many relationships with their respective item models.

Design Rationale

The Junction Tables Favoriteitem, Searchitem, and Travelplaceitem

  • Abstraction of Relationships: Each *item class, such as Travelplaceitem, serves as a junction table in the database. It abstracts the relationship between a Place and a list (e.g., a list of travel destinations).

    • By using these abstract classes, you gain the flexibility to add or remove places from various lists without affecting the Place entities themselves. For instance, if a User decides to remove a Place from their travel itinerary, only the corresponding Travelplaceitem entity is removed. The Place entity remains intact in the database, potentially still linked to other lists or users.