Creación de tablas y columnas

Welcome to creating your first database! Just like organizing information in a spreadsheet, databases use tables to hold related data.

Think of a table as a single sheet in your spreadsheet. Each table has columns, which are like the headings, defining what kind of information each entry will hold.

Supabase provides a powerful web interface called the Supabase Studio (or Dashboard) to manage your project. This is where we'll create our tables.

• First, log in to your Supabase project.
• On the left sidebar, click the Database icon (it looks like a cylinder).
• Then, select Table Editor from the submenu.

Once in the Table Editor, you'll see an option to create a new table. Let's make one for 'products'.

• Click the '+ New Table' button.
• Give your table a descriptive name, like products.
• Keep the schema as public for now. This is the default container for your tables.
• Click 'Save' to create the empty table.

An empty table isn't very useful! Now, let's add some columns to our products table. Columns define the specific pieces of information each product will have.

• In the table editor, find your new products table.
• Click '+ Add column'.
• You'll specify the column's name, data type, and other properties.

Each column needs a data type, which tells the database what kind of data it can store (text, numbers, dates, etc.).

• text: For names, descriptions, or any string of characters.
• integer: For whole numbers (e.g., quantity).
• numeric or decimal: For numbers with decimal points (e.g., price).
• boolean: For true/false values (e.g., is_available).
• uuid: A universally unique identifier, great for primary keys.

Every table needs a Primary Key (PK). This is a special column (or set of columns) that uniquely identifies each row in the table. No two rows can have the same primary key value.

A common practice is to use a uuid (Universally Unique Identifier) as the primary key. Supabase can automatically generate these for you.

• When adding an id column, set its type to uuid.
• Check the 'Is Primary Key' box.
• Set the 'Default Value' to gen_random_uuid(). This will auto-generate a unique ID for every new row.

Databases are powerful because they can link related information across different tables. This is done using a Foreign Key (FK).

A foreign key in one table points to the primary key of another table. It establishes a relationship, ensuring data consistency. For example, a posts table might have a user_id foreign key linking to the id in the users table.

Let's create two tables, users and posts, and link them using a foreign key. Run this SQL directly in the Supabase SQL Editor (accessible from the left sidebar).

After running the SQL or creating tables via the UI, you can always review your database structure in the Supabase Studio.

• Go back to the Table Editor.
• You'll see your new tables listed.
• Click on a table name to see its columns, data types, and any primary or foreign key constraints.
• The 'Relationships' tab can also show how your tables are connected.

Great job! In this lesson, you've learned the fundamental concepts of database structure:

• Tables are containers for related data.
• Columns define the specific attributes within a table.
• Data types specify the kind of data a column can hold.
• Primary Keys uniquely identify each row.
• Foreign Keys link tables together, defining relationships.

You now have the skills to start structuring your Supabase database effectively. Next, we'll learn how to put data into these tables!