Booking backend

[fatjonfreskina]

Certainly! Here's a more detailed breakdown of the database design for the football field reservation system, including table schemas and relationships.

1. Users Table

Purpose: Stores user information.

Fields:

• user_id (Primary Key): Unique identifier for each user.
• name: Full name of the user.
• email: Email address of the user.
• password: Hashed password for user authentication.
• role: Role of the user (e.g., admin, customer).
• phone_number: Contact number of the user.
• created_at: Timestamp when the user account was created.
• updated_at: Timestamp when the user account was last updated.

2. Fields Table

Purpose: Stores information about each football field.

Fields:

• field_id (Primary Key): Unique identifier for each field.
• name: Name of the field.
• location: Physical address of the field.
• description: Description of the field and its amenities.
• price_per_hour: Cost to book the field per hour.
• available_start_time: Daily start time when the field is available.
• available_end_time: Daily end time when the field is available.
• created_at: Timestamp when the field record was created.
• updated_at: Timestamp when the field record was last updated.

3. Bookings Table

Purpose: Stores information about each booking.

Fields:

• booking_id (Primary Key): Unique identifier for each booking.
• user_id (Foreign Key): Identifier for the user who made the booking.
• field_id (Foreign Key): Identifier for the booked field.
• booking_date: Date for which the field is booked.
• start_time: Start time of the booking.
• end_time: End time of the booking.
• status: Status of the booking (e.g., confirmed, canceled).
• payment_status: Payment status (e.g., paid, pending).
• created_at: Timestamp when the booking was made.
• updated_at: Timestamp when the booking was last updated.

4. Payments Table

Purpose: Stores information about payments made for bookings.

Fields:

• payment_id (Primary Key): Unique identifier for each payment.
• booking_id (Foreign Key): Identifier for the associated booking.
• user_id (Foreign Key): Identifier for the user who made the payment.
• amount: Payment amount.
• payment_date: Date and time when the payment was made.
• payment_method: Method of payment (e.g., credit card, PayPal).
• status: Status of the payment (e.g., completed, failed).

5. Reviews Table

Purpose: Stores reviews and ratings for fields.

Fields:

• review_id (Primary Key): Unique identifier for each review.
• user_id (Foreign Key): Identifier for the user who left the review.
• field_id (Foreign Key): Identifier for the reviewed field.
• rating: Rating given to the field (e.g., 1-5 stars).
• comment: Textual comment/review.
• created_at: Timestamp when the review was created.

Relationships

1. Users to Bookings: One-to-Many relationship. One user can make multiple bookings.
2. Fields to Bookings: One-to-Many relationship. One field can have multiple bookings.
3. Bookings to Payments: One-to-One relationship. One booking is associated with one payment.
4. Users to Reviews: One-to-Many relationship. One user can leave multiple reviews.
5. Fields to Reviews: One-to-Many relationship. One field can have multiple reviews.

This detailed database schema provides a solid foundation for the football field reservation system, ensuring that all necessary data is captured and relationships between entities are maintained properly.

[fatjonfreskina]

This is actually a side project which will be handled in another repository.