For example, I have books that can have multiple authors (a simple 1-to-many relationship), and authors who may have written multiple books (another 1-to-many relationship). Now, I want to bring everything together as a many-to-many relationship (m:n).
Following the “standard” relational model, I should create a relation table with IDs from both the books and the authors. This works well in the table editor, but it doesn’t translate smoothly to the user interface (UI). There’s no simple way for a user to select an author from a dropdown in the book editing screen or select a book from the author editing screen since these records are in separate tables. It’s also not intuitive to visualize which books belong to which authors, and vice versa. You can better understand this issue by watching this YouTube video.
Method 2 involves using comma-separated values with a relation, then splitting the text column in the books table to assign multiple authors. In the authors’ table, I would then use a relation to assign multiple books. This method works well in the UI, but it has limitations. For instance, I can’t add a new book directly from the author editing screen (more details can be found here). The workaround would be to do the same for authors as I did for books, but this approach would lead to inconsistency in the data.
Why can’t I easily connect data between authors and books, like I can with Airtable? Am I missing something?
P.S. Before using Glide’s native features, I was using Airtable with Glide. However, the latency and recent updates that disrupted my setup pushed me to try doing everything natively, resulting in the issues mentioned above.