I’m attempting to use baserow as a database backend for a simple application.
Suppose I have two tables with a one-to-many relationship. In a normalized SQL database, I’d create each table with a primary key and other fields and have a third table that contained only the primary keys of the related records from each of the two other tables. Then, if I wanted to query for an item in one table with specific record(s) in the other table, I’d perform a couple of SQL joins and filter on any of the joined fields.
I get the impression that Baserow doesn’t really work this way.
Can you tell me how to think about this problem correctly in Baserow? It seems like I’d want to use a link_to_table field in one of the tables to point to the other one.
Baserow seems to create the third table I described above automatically. However, I need to be able to filter based on fields (other than the id) in either of the tables. Is this even possible?
Hi,
Welcome to the community.
I get the impression that Baserow doesn’t really work this way.
Correct, Baserow - and other no-code databases - use a slightly different approach than a typical relationial database.
LINKING TABLES
Assume that I have a database for project tracking where each project has multiple tasks, but a task always belongs to exactly one project. This is implemented in the following template: Project Tracker // Baserow
As you can see, the Projects table has a linked field to Tasks table (the field is hidden in the template). This field is of the type Link to table and contains multiple tasks. The tasks table also has a Link to table field containing exactly one project for each task. So, because a Link to table field can hold multiple values, there is no need for a third table.
APPLY FILTERS
Assume that I want to filter the tasks for a certain project. I can add a filter to the Tasks table and select the link_row_has filter to select the project. The result of the query is tasks that are only assigned to this specific project.
1 Like
How are many-to-many relationships to be implemented? In particular, how do I construct a filter that is analogous to joining on tables A and B with some conditions?
It seems like I can only filter on table A and for each of those records, get all the associated records from table B. Is there a way to filter records in both tables?
I quickly created a database with 2 tables: Actors and Movies. A movie has multiple actors and an actor plays in multiple movies.
So, if I want all movies where Actor 1 plays a role, I will filter the link to table field Actors in the Movies table
If I want to filter all movies that have actors where the age is higher than 60, I need to add a rollup field to my Movies table. I add this rollup field to my filter
It might not be the most common example, but my point is that you need a formula or rollup field to create a query based on conditions from multiple tables.
PS: the formula field has a filter() function where you can filter the linked records based on certain conditions.
1 Like
