Tables Schema and Relations

Oct 13, 2025 by wbrum.org 43 views

Introduction

As we embark on a new project, it's essential to replicate the existing tables schema from our old project to ensure a seamless transition. In this article, we will explore how to replicate the tables schema in Supabase, a powerful cloud-based database platform. We will cover the tables schema and their relations, and provide a PR with the schema file to generate all the tables with the correct relations.

Tables Schema and Relations

Before we dive into replicating the tables schema in Supabase, let's take a look at the existing tables schema and their relations.

Users Table

The users table refers to the patients. This table stores information about each patient, including their personal details and therapy progress.

Column Name	Data Type	Description
id	integer	Unique identifier for each patient
name	varchar	Patient's name
email	varchar	Patient's email address
phone	varchar	Patient's phone number
created_at	timestamp	Timestamp when the patient's account was created
updated_at	timestamp	Timestamp when the patient's account was last updated

Therapist Table

The therapist table refers to the therapists. This table stores information about each therapist, including their personal details and qualifications.

Column Name	Data Type	Description
id	integer	Unique identifier for each therapist
name	varchar	Therapist's name
email	varchar	Therapist's email address
phone	varchar	Therapist's phone number
qualifications	text	Therapist's qualifications and certifications
created_at	timestamp	Timestamp when the therapist's account was created
updated_at	timestamp	Timestamp when the therapist's account was last updated

Package Table

The package table refers to the therapy package taken by the patient. This table stores information about each package, including its details and pricing.

Column Name	Data Type	Description
id	integer	Unique identifier for each package
name	varchar	Package name
description	text	Package description
price	decimal	Package price
created_at	timestamp	Timestamp when the package was created
updated_at	timestamp	Timestamp when the package was last updated

Session Table

The session table refers to a therapy session. This table stores information about each session, including its details and progress.

Column Name	Data Type	Description
id	integer	Unique identifier for each session
patient_id	integer	Foreign key referencing the patients table
therapist_id	integer	Foreign key referencing the therapists table
package_id	integer	Foreign key referencing the packages table
session_date	date	Date of the therapy session
session_time	time	Time of the therapy session
created_at	timestamp	Timestamp when the session was created
updated_at	timestamp	Timestamp when the session was last updated

Therapy Goal Table

The therapy_goal table refers to the therapy goals section of the app. This table stores information about each goal, including its details and progress.

Column Name	Data Type	Description
id	integer	Unique identifier for each goal
session_id	integer	Foreign key referencing the sessions table
goal_name	varchar	Name of the therapy goal
goal_description	text	Description of the therapy goal
created_at	timestamp	Timestamp when the goal was created
updated_at	timestamp	Timestamp when the goal was last updated

Replicating Tables Schema in Supabase

To replicate the tables schema in Supabase, we need to create a schema file that defines the tables and their relations. Here is an example of a schema file that replicates the tables schema:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  phone VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE therapists (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  phone VARCHAR(255) NOT NULL,
  qualifications TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE packages (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description TEXT NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE sessions (
  id SERIAL PRIMARY KEY,
  patient_id INTEGER NOT NULL REFERENCES users(id),
  therapist_id INTEGER NOT NULL REFERENCES therapists(id),
  package_id INTEGER NOT NULL REFERENCES packages(id),
  session_date DATE NOT NULL,
  session_time TIME NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE therapy_goals (
  id SERIAL PRIMARY KEY,
  session_id INTEGER NOT NULL REFERENCES sessions(id),
  goal_name VARCHAR(255) NOT NULL,
  goal_description TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

This schema file defines the tables and their relations, including the foreign keys that reference the users, therapists, and packages tables.

Conclusion

In this article, we explored how to replicate the tables schema in Supabase. We covered the tables schema and their relations, and provided a PR with the schema file to generate all the tables with the correct relations. By replicating the tables schema in Supabase, we can ensure a seamless transition to the new project and maintain the existing data and relationships.

Additional Resources

For more information on Supabase and its features, please visit the official Supabase documentation:

Supabase Documentation

For more information on SQL and database design, please visit the following resources:

SQL Tutorial
Database Design Tutorial
Replicate Tables Schema in Supabase: Q&A =====================================

Introduction

In our previous article, we explored how to replicate the tables schema in Supabase. We covered the tables schema and their relations, and provided a PR with the schema file to generate all the tables with the correct relations. In this article, we will answer some frequently asked questions (FAQs) about replicating tables schema in Supabase.

Q: What is the purpose of replicating tables schema in Supabase?

A: The purpose of replicating tables schema in Supabase is to ensure a seamless transition to the new project and maintain the existing data and relationships. By replicating the tables schema, we can ensure that the new project has the same structure and relationships as the old project.

Q: How do I create a schema file in Supabase?

A: To create a schema file in Supabase, you can use the Supabase console or the Supabase CLI. The Supabase console allows you to create a new schema file by clicking on the "Schema" tab and then clicking on the "Create Schema" button. The Supabase CLI allows you to create a new schema file by running the supabase schema create command.

Q: What is the difference between a schema file and a database file?

A: A schema file is a file that defines the structure of a database, including the tables, columns, and relationships. A database file, on the other hand, is a file that contains the actual data in the database. In Supabase, the schema file is used to define the structure of the database, and the database file is used to store the actual data.

Q: How do I add a foreign key constraint to a table in Supabase?

A: To add a foreign key constraint to a table in Supabase, you can use the ALTER TABLE statement with the ADD CONSTRAINT clause. For example, to add a foreign key constraint to the sessions table that references the users table, you can use the following statement:

ALTER TABLE sessions
ADD CONSTRAINT fk_sessions_users
FOREIGN KEY (patient_id)
REFERENCES users (id);

Q: How do I drop a table in Supabase?

A: To drop a table in Supabase, you can use the DROP TABLE statement. For example, to drop the therapy_goals table, you can use the following statement:

DROP TABLE therapy_goals;

Q: How do I rename a table in Supabase?

A: To rename a table in Supabase, you can use the RENAME TABLE statement. For example, to rename the therapy_goals table to therapy_sessions, you can use the following statement:

RENAME TABLE therapy_goals TO therapy_sessions;

Q: How do I create a view in Supabase?

A: To create a view in Supabase, you can use the CREATE VIEW statement. For example, to create a view that combines the sessions and therapy_goals tables, you can use the following statement:

CREATE VIEW therapy_sessions AS
SELECT *
FROM sessions
JOIN therapy_goals ON sessions.id = therapy_goals.session_id;

Conclusion

In this article, we answered some frequently asked questions (FAQs) about replicating tables schema in Supabase. We covered topics such as creating a schema file, adding foreign key constraints, dropping tables, renaming tables, and creating views. By understanding these concepts, you can effectively replicate tables schema in Supabase and maintain the existing data and relationships.

Additional Resources

For more information on Supabase and its features, please visit the official Supabase documentation:

Supabase Documentation

For more information on SQL and database design, please visit the following resources: