Setting up the database schema for a Flask application

In this lesson, we're going to set up the database schema for the hotel management app. The schema consists of three tables: guest, booking, and room_type. We'll be using PlanetScale for our database, and the app will be built using Flask.

Our application's schema

The schema includes the following tables and columns:

1. guest
   • id
   • name
   • email
2. booking
   • id
   • check_in
   • check_out
   • guest_id (foreign key referencing the guests table)
   • room_type_id (foreign key referencing the room type table)
   • breakfast
   • airport_pickup_time
   • reference_number
3. room_type
   • id
   • description
   • code

You can find the schema definition in the schema.sql file within the hotel management app folder from the repo in the previous lesson.

You can use the following SQL code as an example of creating the tables:

CREATE TABLE guests (
  id INTEGER AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(50) NOT NULL UNIQUE,
  PRIMARY KEY (id)
);


CREATE TABLE booking (
  id INTEGER AUTO_INCREMENT,
  check_out DATE NOT NULL,
  check_in DATE NOT NULL,
  guest_id INTEGER,
  room_type_id INTEGER,
  breakfast BOOLEAN NOT NULL,
  airport_pickup_time TIME,
  reference_number CHAR(10) UNIQUE,
  PRIMARY KEY (id)
);


CREATE TABLE room_type (
  id INTEGER AUTO_INCREMENT,
  description VARCHAR(50) NOT NULL,
  code CHAR(10) NOT NULL UNIQUE,
  PRIMARY KEY (id)
);

Importing the schema into the database

To import the schema into your database, follow these steps:

1. Open a terminal or command prompt.
2. You'll be using the PlanetScale CLI, so make sure that's set up first.
3. Run pscale login and authenticate in the browser.
4. Connect to the shell for your database by running pscale shell <database_name>. If you followed the previous lessons, you may already have a hotel_management database set up. If so, you can connect to that. Otherwise, create a new database.
5. If your tables still exist in the previous database, you can delete them using DROP TABLE <table_name>;.
6. Copy the SQL code for creating the tables from the schema.sql file, and paste it into the shell.
7. Verify that the tables were created successfully by running SELECT * FROM <table)name>; for each table.

Updating the .env file

Next, update your .env file with the following information:

• DB_HOST: The database host provided by PlanetScale.
• DB_USERNAME: Your database username.
• DB_PASSWORD: Your database password.
• DB_NAME: The name of your database.

Make sure you've set these environment variables correctly, as your app will use them to connect to the database.

Overview of the app files

Inside the hotel management app folder, you'll find the following files:

• __init__.py: Sets up the Flask app.
• db.py: Contains the db() function that sets up the connection to the database, and the close_db() function that closes the connection. We'll work on this file in the next section.
• routes.py: Defines the different routes that our app will use. Currently, it only returns templates without any data.

The app also includes a couple of HTML templates (single.html and list.html) for displaying reservation information.

Next steps

In the next section, we'll work on creating the connection to the database using the db.py file. Once we have a connection, we can start implementing the app's functionality, such as creating new reservations, viewing all reservations, and updating or deleting existing reservations.