Database Schema Documentation

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.