Design for List data type

[ghislaineguerin]

Problem

So far, we've been assuming that users will only store a single point of data in any given table "cell". However, PostgreSQL (the database we use) supports the ability to store a list instead of a single point of data.

Lists will be useful in both Tables and Views.

Lists in Tables

As an example, see this table that stores the mapping between movies and actors:

ID	Movie	Actor
1	The River Wild	Kevin Bacon
2	The River Wild	Meryl Streep
3	The River Wild	David Strathairn
4	Don't Look Up	Jennifer Lawrence
5	Don't Look Up	Meryl Streep
6	Don't Look Up	Leonardo DiCaprio

Here, Actor is a Text field. If we instead wanted to store a table like so:

ID	Movie	Actors
1	The River Wild	Kevin Bacon, Meryl Streep, David Strathairn
2	Don't Look Up	Jennifer Lawrence, Meryl Streep, Leonardo DiCaprio

We would need to set the data type of Actors to be a List of Text types instead.

This is not actually the ideal structure for this data (you can see that Meryl Streep is repeated in both records), for the ideal structure, see the tables listed in this wiki page and the Foreign Key constraints spec. However, we still want to support Lists since it's an underlying database feature and it might be easier to use for some use cases.

Lists in Views

As described in the Product Spec for Views, columns in Views will have the same data types as Tables. Data types in Views will represent the final data type.

Please see the example view on the Views "Concepts" page on the wiki. There, the Actors column, which is generated from summarizing individual actors, will have a list data type.

Proposed solution

Since Views are still being figured out, this issue will focus on representing Lists in tables. We will need designs for the following scenarios:

Viewing data

1. The user is able to read and identify List values in tables
2. The user can expand an List value that is too long for the cell and see the full contents

Setting data type

1. The user is able to change the data type of a column to a List and select the data type of what the items of the list should be. By default, the data type of list items will be "Text".
2. The user should be able to change the data type of List items for an existing column.
3. he user should be able to update the database and display options of List items for an existing column. The specific options available will depend on the data type, please see existing data type specs.
4. The user should be able to see errors if changing the data type, database options, or display options fails.

Existing data type specs (under "Additional Context") should be helpful here.

Editing List cells

1. The user can add an item to a cell containing a List
2. The user should be able to see errors if adding an item fails for any reason. If the reason is because they've entered invalid data, they should see why the data is invalid.
   3 The user can remove an item from a cell containing a List
3. The user should be able to see errors if removing an item fails for any reason.
4. The user can edit an individual List item
5. The user should be able to see errors if editing an item fails for any reason. If the reason is because they've entered invalid data, they should see why the data is invalid.

Filtering and Grouping

1. We should support the following filters for List cells:
   1. is empty
   2. is not empty
   3. contains <LIST ITEM DATA TYPE>
   4. number of items greater than <NUMBER>
   5. number of items greater than or equal to <NUMBER>
   6. number of items equal to <NUMBER>
   7. number of items lesser than <NUMBER>
   8. number of items lesser than or equal to <NUMBER>
2. We should support the following custom grouping types for List cells:
   1. Number of list items

Design Notes

• Lists should look a little different from other data types. Most data types involve just picking the data type and then setting Database and Display options. Lists add an additional step to the workflow, first you pick the List data type, then you pick the list item data type, and then you pick database and display options for the list item data type.
• The default value database option of columns with List data types should apply to the List, not List Item. This means we need to be really clear about which database option applies to the list as a whole and which applies to list items.

Additional context

• Design specs for Data Types
• Data Types "Concepts" page
• Mathesar Data Types engineering wiki page
• Product Spec for Views
• PostgreSQL documentation for Arrays

[kgodey]

Assigned to @ghislaineguerin to review. @ghislaineguerin when this looks good, please change label to status: ready and assign to @ppii775.

[ppii775]

@kgodey @ghislaineguerin

The user is able to change the data type of a column to a List and select the data type of what the items of the list should be. By default, the data type of list items will be "Text".
A few questions:

• Are users generating lists from other cells? Or we are just displaying lists for now?
• For a single column, do we allow users to have list data type with different item data types?
• Can one list have items in different data types?
• If these are answered on a PostgreSQL page I can just read that (a link would be appreciated). In general, is Mathesar trying to support all PostgreSQL functions but offering more?

[kgodey]

Are users generating lists from other cells? Or we are just displaying lists for now?

In Tables, users will be entering list data in individual cells manually, they will not be generating lists from other cells.

When we use lists in Views, users will be generating data from other cells but that is out of scope here and will be handled in a separate design issue.

For a single column, do we allow users to have list data type with different item data types?

No, all list items for a single column will be the same data type.

Can one list have items in different data types?

No, all list items in a single list will have the same data type.

If these are answered on a PostgreSQL page I can just read that (a link would be appreciated). In general, is Mathesar trying to support all PostgreSQL functions but offering more?

Lists in Mathesar are called Arrays in PostgreSQL and you can read about them here: https://www.postgresql.org/docs/13/arrays.html

Mathesar won't be supporting all functionality in PostgreSQL, just a subset. But everything in Mathesar is built on top of existing PostgreSQL feature.

[ppii775]

@kgodey Thanks for your reply!

all list items for a single column will be the same data type.

In other words, users only need to configure the entire column data type at once?

[kgodey]

In other words, users only need to configure the entire column data type at once?

Users will need to pick the List data type first and then configure the data type of list items once they have picked that. But all the configuration should take place at once, yes.

[kgodey]

@ghislaineguerin mentioned that additional examples of where users might use list data types could be useful. Here are some:

1. Using a List column to keep track of movie watches (list item type is Date), e.g.

Movie Title	Watched On
Batman Begins	2015-01-12, 2022-02-01
The Dark Knight	2022-02-08
The Dark Knight Rises	NULL
Spider-Man	2015-06-07, 2017-05-20, 2021-04-15

2. Using a List column to keep track of dice roll results for a player in a game (list item type is Number), e.g.

Player	Results
Alice	1, 6
Bob	2, 3
Carol	5, 3
Dan	3, 4

3. Using a List column to keep track of keywords or tags related to a product (list item type is Text), e.g.

Product Description	Keywords
Hoodie	white, grey, cotton
T-Shirt	white, plain
Sweater	wool, cashmere, angora, blue, navy
Pants	formal, black

4. Using a List column to keep track of emails associated with a given user (list item type is Email), e.g.

User	Emails
Kriti	[email protected], [email protected]
Han	[email protected]
Ghislaine	[email protected], [email protected]

I hope this is helpful @ppii775

[kgodey]

@ppii775 Also, please note that this issue does not deal with nested lists (Lists of Lists, or Lists of Lists of Lists, or so on). Although PostgreSQL does support column types that nest lists, creating a UI that works well for nested lists is not within scope of our initial release. You can ignore that use case for your work on this issue.

[ppii775]

Thanks @kgodey
I assume by "no nested lists" you meant all Lists are one-dimensional (a,b,c...)?
What are the List creation scenarios that I need to explore for this release? Are we assuming users already have a delimiter information format in a cell or do they have to aggregate data? If they need to aggregate data could you point me to the current design?

[kgodey]

Thanks @kgodey I assume by "no nested lists" you meant all Lists are one-dimensional (a,b,c...)?

Yes, that's accurate.

What are the List creation scenarios that I need to explore for this release? Are we assuming users already have a delimiter information format in a cell or do they have to aggregate data? If they need to aggregate data could you point me to the current design?

The creation scenarios are:

• The user creates a new column and configures it as a List. In this case, the column data will be blank and users will start entering new list items from scratch.
• The user transforms an existing column with a text data type into a List. In this case, we would parse existing text and turn it into a List. We can assume that the delimiter is a comma by default and ask the user what delimiter they would like to use. e.g. a text value of red, green, blue would turn into a List of red, green, blue.

We don't need to do anything related to aggregating other data for this issue.

Does that answer your questions?

[kgodey]

This is out of date, I'm going to close it. We'll create a new issue once we have better requirements.