Combining multiple tables in Supabase

In a previous blog, I created a Supabase project with 1 table that I read from. Let’s expand it with a second table. Each drink we have is in the table drinks now let’s give each drink a producer in the table producers. We are going to make a relationship between drinks and producers. With that, we are building a relational database.

This blog is part of caffeinecritics.com the code can be found on GitHub.

Logo of Supabase

Benefits of multiple tables

Splitting data into multiple tables in a relational database is a fundamental concept known as database normalization. The primary reasons for doing this are:

Reduced Redundancy:

• Organizing data into multiple tables minimizes redundancy, preventing duplication of information. This reduces storage space requirements and helps maintain data consistency.

Improved Data Integrity:

• Multiple tables with proper relationships enhance data integrity by reducing the risk of update anomalies. Referential integrity is maintained through foreign key relationships, ensuring consistency across related tables.

Enhanced Query Performance:

• Well-organized tables often result in improved query performance. Smaller, specialized tables with appropriate indexes make it easier for the database engine to retrieve and process data efficiently. Although we are not dealing with the query directly via Supabase this is still good to take in mind.

It’s important to note that while normalization has many advantages, there can be trade-offs, particularly in terms of query performance for certain types of queries. Therefore, the level of normalization should be chosen carefully based on the specific requirements of the application and the types of queries that are expected to be frequent.

Adding a table in Supabase

To connect 2 tables we use a foreign key. A foreign key is a column in a table that links to the primary key of another table, establishing a relationship. It ensures data integrity by enforcing referential consistency between tables, preventing orphaned rows. This relation is one-directional.

To get started we create the table producers and we give it the columns name and website, both are of the type text.

Next up we go to the table drinks and insert a new column name producer. We will hit add foreign key relation. Select producers as a table and use the ID for reference. An ID is always unique and indexed, this is very important for the relationship.

To the left, the tab “add foreign key relation” shows a relationship to producers and uses the ID as a reference.
To the right, the “add new column” window, shows a foreign key relation and a name producer.

Now we created a relationship from drinks to producers in Supabase, let’s use it next.

Fetch from multiple tables

We made a relationship on ID, but we do not want to show the ID of the producer to our users but the full name. In SQL you would do this with a join, but in Supabase it is even simpler. All we need to do is add the producer to our select statement and tell Supabase what we want from it.

    supabase
    .from('drinks')
    .select('id, name, image_url, description, producer(name)')

Here we use the Supabase object we made in a previous blog. We select from the table drinks and we select id, name, image_url, and description directly from the table. We also add the producer with the name between brackets. Supabase will now look for the producer that is linked to this drink and return the name to us.

And we did it, we fetched data from 2 different but related tables.

The resulting page shows the producer of “Aroma Rood” to be “Douwe Egberts”