Method 1: Using the Table Editor

Absolutely! Making tables in Supabase can be done through their web interface with a visual table editor, or by writing SQL commands directly. Here’s a step-by-step guide for both methods to get you started:

Method 1: Using the Table Editor

Step 1: Access Your Project

Step 2: Navigate to the Table Editor

On the left sidebar, click on Table Editor under the Database section.

Step 3: Create a New Table

Click on New Table.
Name Your Table: Enter a name for your table, e.g., users.
Add Columns: Specify the columns you want in your table. For each column, you need to provide:
- Name: E.g., id, username, email.
- Type: Choose the data type (e.g., integer, text, timestamp).
- Nullable: Check this if the column can be empty, or leave it unchecked for mandatory fields.
- Default Value: If applicable, you can set a default value for the column.
- Primary Key: Decide if the column will be a primary key. Typically, an id column is set as the primary key and often marked to auto-increment.

Step 4: Save the Table

After setting up your columns and other properties, click the Save button to create the table.

Method 2: Using SQL Commands

Step 1: Access Your Project

As before, log into Supabase and open your project.

Step 2: Navigate to the SQL Editor

Click on SQL Editor in the left sidebar.

Step 3: Write SQL to Create a Table

You’ll enter SQL commands to create a table. Here’s an example SQL statement to create a users table:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

Breakdown:
- SERIAL: Automatically increments—good for an id.
- VARCHAR(255): A text field with a maximum length of 255 characters.
- UNIQUE: Ensures all emails in the column are unique.
- DEFAULT CURRENT_TIMESTAMP: Sets the created_at column to the current date and time by default.

Step 4: Run the SQL Command

Click Run to execute your SQL statement. This will create your table in the database.

Additional Tips

Indexes: For better performance on large tables, consider adding indexes on columns that are frequently used in queries.
Foreign Keys: To maintain relational integrity, you can add foreign keys that link your tables together. For example, linking a posts table to a users table to show which user created each post.
Check Constraints: These are rules you place on a column to ensure only valid data is added. For example, ensuring a percentage column doesn’t exceed 100.

Both methods will effectively create tables in your Supabase project, and you can switch between using the GUI and SQL as per your comfort and requirement. Happy building with Supabase!

Bonus Cool Tables to Make

Here are three cool table designs you might consider implementing in Supabase, tailored for different types of applications. These examples will showcase the flexibility and power of using Supabase for your database needs.

1. E-Commerce: Products and Inventory Management

For an e-commerce application, you can create a robust table setup that handles products, categories, and inventory tracking efficiently.

Products Table

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price NUMERIC(10, 2) NOT NULL,
    category_id INTEGER REFERENCES categories (category_id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

Categories Table

CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,
    name VARCHAR(255) UNIQUE NOT NULL,
    description TEXT
);

Inventory Table

CREATE TABLE inventory (
    inventory_id SERIAL PRIMARY KEY,
    product_id INTEGER REFERENCES products (product_id),
    quantity INTEGER DEFAULT 0,
    warehouse_location VARCHAR(255)
);

What Makes It Cool?
This setup allows for detailed tracking of products across various categories and their inventory statuses, facilitating complex queries like joint product and inventory reports, categorized product listings, and real-time inventory updates.

This model is great for a social media platform where users can create posts and comment on posts.

Users Table

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(255) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    profile_pic_url TEXT,
    joined_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

Posts Table

CREATE TABLE posts (
    post_id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users (user_id),
    content TEXT NOT NULL,
    image_url TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

Comments Table

CREATE TABLE comments (
    comment_id SERIAL PRIMARY KEY,
    post_id INTEGER REFERENCES posts (post_id),
    user_id INTEGER REFERENCES users (user_id),
    content TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

What Makes It Cool?
This schema supports a basic social media structure with relational links between users, their posts, and comments on those posts. It enables real-time interaction, user engagement analytics, and can be expanded to support features like “likes” or “reposts.”

3. Event Management: Events, Attendees, and Sessions

Manage an event with detailed tables for events, sessions within those events, and attendee registrations.

Events Table

CREATE TABLE events (
    event_id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    location VARCHAR(255) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    organizer_info TEXT
);

Sessions Table

CREATE TABLE sessions (
    session_id SERIAL PRIMARY KEY,
    event_id INTEGER REFERENCES events (event_id),
    name VARCHAR(255) NOT NULL,
    speaker VARCHAR(255),
    start_time TIMESTAMP NOT NULL,
    end_time TIMESTAMP NOT NULL,
    room VARCHAR(255)
);

Registrations Table

CREATE TABLE registrations (
    registration_id SERIAL PRIMARY KEY,
    attendee_id INTEGER REFERENCES users (user_id),
    session_id INTEGER REFERENCES sessions (session_id),
    registered_on TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

What Makes It Cool? This setup facilitates complex event planning and management, allowing for multiple sessions per event and tracking attendee registrations. It can be used to generate schedules, manage room allocations, and provide valuable insights into attendance patterns.

Each of these table setups in Supabase can be tailored further based on specific application needs and can utilize features like real-time updates, row-level security, and automatic API generation to make your application robust and scalable.

Method 1: Using the Table Editor