Multi Select Data Type

[sai-sy]

Airtable (commercial leader in the space) and NocoDB (competitive open source choice) both have multi-select options for columns. For maintaining data normalization this would be a good feature to add

[seancolsen]

Thanks for starting this discussion, @sai-sy. The maintainers team has been talking about this problem for a while now, and addressing it is currently a medium-high priority for us.

We're still not certain of exactly how we want to solve this problem, but I can say that the design we ultimately employ is very unlikely to follow the patterns established by Airtable (and the assortment of Airtable clones, including NocoDB). Mathesar's approach (and high-level goals) are a bit different from those products. While Mathesar shares Airtable's goal of providing an easy-to-use interface for managing data, Mathesar has an additional goal of accurately representing the behavior of relational databases (PostgreSQL specifically). In some cases these two goals can be in tension, requiring delicate balance and careful design on our part.

Let's use an example to get more specific... say we have a table of books and a table of authors, and we want each book to have one author and each author to have many books. By default, Airtable would represent this relationship by displaying a single-select "Author" column within the books table and displaying a multi-select "Books" column within the authors table. While that may be an intuitive interface for many users, the pattern is not in line with the behavior of relational databases. In Postgres, the books table would have an "Author" column, but the authors table would not contain any information about books. Mathesar currently mirrors that structure by providing only a single-select "Author" column within the books table. This approach satisfies our goal of "PostgreSQL behavior", but in many cases does not satisfy our other goal of being "easy-to-use". Further, if we might want to assign genres to our books, then we have a many-to-many relationship and the usability challenges only get worse within Mathesar! As you may know, relational databases like PostgreSQL model many-to-many relationships using an intermediate "mapping" table which contains rows that point to rows in two other tables. Mathesar's "record page" feature is currently the best way we have to work with one-to-many and many-to-many data, but it's a long way from being as easy to use as Airtable.

As a brief tangent, it's worth noting that many relational databases (including Postgres) have array types which people sometimes use to model relationships. With arrays, our books table could have a "Genres" column containing arrays of strings, e.g. {fiction,fantasy}. In some cases, this structure could be just fine. But there are a lot of reasons to avoid it: performance, storage space, data updating, data cleanliness, etc. Suffice to say that the idiomatic way to represent this data in PostgreSQL would be to use a books table, a genres table, and a "books_grenres" mapping table. I mention the array type for clarity though. Mathesar already has some support for array types. If the column is defined in Postgres (outside of Mathesar), then Mathesar can show array types (but editing is not yet supported). We would definitely like to improve our support for array types, but figuring out how to improve the user experience of related data across separate tables is a higher priority for us.

Currently, Mathesar's Data Explorer allows you to join related data from separate tables into a single result — but editing data from that joined result is not yet supported. We are in the early stages of discussing changes to the Data Explorer that would allow editing data and building more sophisticated workflows. Hypothetically, you could use the improved Data Explorer to build an interface that would function more like the Airtable version of our example "authors" table. To be clear: Mathesar would still display the authors table as it is stored in Postgres — without any references to books. But separate from the raw table you'd have a saved exploration with one row per author and then a special "books" column you would define to show a writable multi-select of books published by the author. This is the direction we're (tentatively) heading. We'd love to hear your thoughts and feedback about this!

We still have a lot of design work in front of us, and it would really help us to hear more about your use case. Would you be able to describe the structure of the data that you're working with that makes you want a "multi select data type"? Perhaps you do have a good use case for array types. But my hunch is that you most likely have a one-to-many relationship or a many-to-many relationship, in which case I would advocate for normalizing the data across multiple tables — and making use of something like the "editable explorations" feature that we're working towards.