Mathesar Data Types definition

[kgodey]

There's been some confusion around the concept of the "Mathesar Data Type" abstraction that we've come up with, so I wrote up a couple of wiki pages to help clarify.

• The Data Types "Concepts" page on the wiki. This is aimed at a general audience.
• The Mathesar Types architecture page on the wiki. This is aimed at engineers working on Mathesar.

I know we've been talking about data types quite a bit, but please try to read the pages with as much of a fresh eye as possible (imagine the concept is new to you).

Some questions:

• Do the goals of the Mathesar type system make sense?
• Given the goals of the Mathesar type system, does the criteria to define Mathesar types make sense?
  • This is the section called "Defining Mathesar Types" in the Mathesar Types architecture page
• Does the current mapping of Mathesar types to PostgreSQL types make sense?

Any other feedback on the pages are welcome as well.

Previous discussions and context

• Discussion on Matrix
• Create Concepts page for data types mathesar-wiki#14
• Making the frontend type-agnostic #849
• Should filtering options be defined per Postgres or Mathesar type? #843
• Column types for Views #838

[silentninja]

Based on our discussion on matrix, I have listed some of the useful points that came up

I tried explaining data types with 2 others who were familiar with spreadsheets but don't have experience with database. Mathesar types were quite native to them, the problem started when I showed them db types. They thought it was a configuration for the number type.

@kgodey commented that this is correct and falls in line with our assumption.

Both the Mathesar type and the DB type are the actual data type

I think this might confuse the user. If this happens to be the case, they could be expecting filter options to be based on mathesar types

[kgodey]

@silentninja I don't think whether filter options are associated with Mathesar or DB types are going to be the concern of an end user., it's an architectural concern.

Per our filtering UI, the user selects the column and then sees what filters are available for that column and then picks a filter from that list. They shouldn't know how we generate that list.

[mathemancer]

The main reasons to choose a type are:

• data integrity
• available operations
• formatting

If there are other reasons, I can't think of them (feel free to extend that list, though). I think it's an untested assumption to guess that users would choose a type for data integrity reasons rather than by the available operations. In fact, if I had to guess, I think my Aunt Sally would be more likely to choose a type based on available operations rather than data integrity (e.g., she wants to sum them, so they should be numbers). In that case, it's a UX concern, since filtering is an operation. I wouldn't be surprised at all if a user didn't bother changing to an email type unless they knew that would open up filtering by domain.

[silentninja]

@kgodey It won't be a UX concern as long as the user is able to infer from the data about the missing filter, instead of wondering why a filter is missing though the Mathesar type is the same, but I am not sure that would be the case always

We could have filters based on mathesar type and disable them according to the db type. This could also help users in exploring other types.

[seancolsen]

@kgodey This all looks great to me!

• Do the goals of the Mathesar type system make sense?
  • Yes
• Given the goals of the Mathesar type system, does the criteria to define Mathesar types make sense?
  • Yes
• Does the current mapping of Mathesar types to PostgreSQL types make sense?
  • Yes

The only constructive criticism I have is that term "group" is somewhat overloaded and we're using it here in reference to the fact the several pg types can be "grouped" together into one Mathesar type. Honestly I can't think of a better term though. But just throwing it out there that if someone else can think of a different word, it would be nice to avoid "group" for clarity's sake. Not a big deal though.

[dmos62]

"group" is somewhat overloaded and we're using it here in reference to the fact the several pg types can be "grouped" together into one Mathesar type

Mathesar "type" has a similar problem to some extent, since it's a data type only from certain perspectives. I too don't have a good alternative, but I think it's good to keep this in mind.

[mathemancer]

Do the goals of the Mathesar type system make sense?

I don't actually understand what we're trying to make understood in non-technical terms. Should users understand that choosing Numbers makes sure they don't input '123d'? Or that they'll be able to sum and average them? I.e., it's not clear to me why a generic user would bother with choosing a type at all, other than maybe any nice formatting it results in. I think the precise user-oriented goals should be clarified for us, then expounded upon in the User-facing docs. In the current docs, it has a sentence about data quality, and another about available filters (or other functionality). But, as far as I understand, the goal isn't to define Mathesar types so that they'll know what filters will be available by choosing one. As for reducing cognitive load, I think that's a good goal, and is certainly served by trying to bin things together. Overall, I do agree that we need to avoid users spending a bunch of time thinking about the differences between NUMERIC and FLOAT.

Given the goals of the Mathesar type system, does the criteria to define Mathesar types make sense?

I'm not clear (or maybe convinced) on the goals, so it's hard to answer this. I initially really liked the concept of having a lossless default, but that's not a decision criteria. You can have one type "data", and have VARCHAR be the lossless default. There's no opposing force indicated in the doc that pulls the types apart to separate them. To me, the forces that pull them apart are data integrity and functionality. I want to filter emails by domain, I want to sum numbers, I want to slice strings, etc. Because slicing numbers makes no sense, I need separate types to indicate that numbers sum, whereas strings slice. I need the integrity so that when I do want to sum, I don't accidentally have a non-numeric string in my list of summands. From this perspective, the question of how to group types reduces to a question of what functionality we want to expose to the user, and how fine-grained the types need to be for that functionality to be coherent. On a side note: Our custom types are each in their own separate Mathesar type. To me, that's a signal that we have a specific set of use-cases for each custom type that imply certain functionality that we want to enable for the user, including validation and (so far) filtering/grouping. We don't have specific use cases in mind to differentiate, e.g., NUMERIC from FLOAT (for our intended users).

• Does the current mapping of Mathesar types to PostgreSQL types make sense?

There's a mistake: You can't cast a TIME to a TIMESTAMP unless you choose a date to go with the TIME.

Overall, I don't want to come off as too negative, since I wholeheartedly agree with the main goal: Avoiding users being overwhelmed by choosing between NUMERIC and FLOAT when they just want to store and average grades. This is not an easy problem to solve, though. I think types should be indicated by use-case and functionality, not the other way around. I don't think we need to have separate types whenever we're not exposing functionality to differentiate them. This means, at least, that NUMERIC and FLOAT can be grouped together (for now).

Side note: I really liked what @dmos62 mentioned about "tags" on Matrix: If a user is more interested in doing things and processing than organizing, tags would be a way easier way to get to what they want than types (and maybe more natural).

[silentninja]

it's not clear to me why a generic user would bother with choosing a type at all

Most users think of the data type as a characteristic behaviour, which brings in data integrity as well as the associated filters. But I think they would be choosing it for the data integrity most of the time, this is due to the column type being decided when they enter a name for the column. I can see the db type being changed to accommodate new data, but I doubt if they would move from one Mathesar type to another.

If a user is more interested in doing things and processing than organizing, tags would be a way easier way to get to what they want than types

Aren't tags similar to constraints?

[pavish]

But, as far as I understand, the goal isn't to define Mathesar types so that they'll know what filters will be available by choosing one.

I think this should also be a goal (and it kinda already is) but the highest priority should go towards reducing cognitive overload. Having a general idea of what filters might be available for a type does infact reduce cognitive overload.

Our current definitions already satisfy this to a good extent. An user would see the Number type and know that they can perform numerical operations on the column. They see the email type and know that it would perform email validations for them when they want to create a public form.

When it comes to the user wanting to perform more advanced operations which can only be performed on specific db types, we can safely assume that the user is fairly technical or tech-oriented. We do provide the configurations for them to be able to fine tune types so that they will be able to selected the desired db type. The question that comes up is what if the user does not know that each db type could have different filters or groups, and I think this is an area where we can use "intelligence on data" and provide suggestions/hints, and not something we should optimize the entire UX for.

[silentninja]

There's a mistake: You can't cast a TIME to a TIMESTAMP unless you choose a date to go with the TIME.

Isn't it similar to FLOAT and INTEGER? Sorry I was thinking of a lossy cast, I do agree that TIME should not be a timestamp data type as the cast difference is quite drastic.

[dmos62]

The distinction you drew between data storage (where you care about integrity above all) and data processing (where you care about what processing you can perform on the data you have) is useful. A Mathesar types implementation could suit a data organizer, but not a data processor, and vice-versa. Though there might be a solution that suits them both. A lot of good points overall.

[mathemancer]

Hair-brained alternate easy-type-omatic concept: On import, we infer a type (losslessly). If they input data that doesn't fit that type, we ask "This is different from other data in this column. Are you sure?". If they answer affirmatively, we update the type to encompass the new data without bothering them with specifics. If they want specifics, they see all the types, as we assume they're an advanced user at that point. This would be simple for the user, and result in the ability to use a column that consists entirely of numbers as a number (as they'd probably want). If they add a column, we initially set it as VARCHAR. After they enter a value or two (or ten), we infer the type, and go back to the top of the paragraph. Note that if they specify a type, we don't bother with inferring or nagging unless they try to enter data that doesn't fit. Maybe later we could "lock" the type so that they avoid the nagging step, and just refuse data that doesn't fit.

We already have all the pieces needed to support this UI on the API level.

Later, if we want to give the user the ability to start from a desired filter or other function and navigate to a type, we add some kind of "type chooser" that helps with that task.

[silentninja]

I like this, as this seems to reduce the cognitive load for the user. But I have two concerns

1. Performance - We would have to do a typecast for every data that is being entered.
2. Data integrity introduction - I feel like we should introduce them to data integrity, so that they are not caught off-guard. For example, lets say user creates a column contact_details, the user would be entering a mix of phone number, email id in the same column, now if they want to do any operation on the email data, it would end up in a mess

[dmos62]

I like how in this workflow the platform makes a minimum of assumptions.

One thing we'll probably be able to do is, when a user looks at the list of available filters, he'll see filters that are both applicable and non-applicable in current situation, but they'll be appropriately marked and, what's pretty important, I think, he'll be able to look at some filter that is not "suggested" for this data type and be told what that filter expects its input data to be like.

In other words, the user will be able to tell, by looking at a time-of-day filter in the filtering UI, that it's suggested to use it on data that has time-of-day information, even if it's not suggested that the user apply this filter right now.

[pavish]

• Do the goals of the Mathesar type system make sense?
  Yes, they make sense to me.

• Given the goals of the Mathesar type system, does the criteria to define Mathesar types make sense?
  Yes. But there are some things I think we should consider additionally.

  1. Being able to cast between db types within the same mathesar type.
     Our current UX prototype relies on being able to do this.
  2. Reasonable overlap of operations (filters, groups etc.,).
     While we do not have to prioritize this, having a good overlap of operations among db types of a mathesar type reduces cognitive overload, which is our primary goal.

• Does the current mapping of Mathesar types to PostgreSQL types make sense?
  Yes, but one. I think we should split up Date & Time. I remember bringing this up before and @mathemancer pointed out the issue that we cannot case Time to Timestamp in his previous comment. As mentioned above our UX relies entirely on being able to cast between db types of a mathesar type.

[silentninja]

• Do the goals of the Mathesar type system make sense?

  Yes, I totally agree on using abstraction to reduce cognitive load, throwing terms like VARCHAR, INTERVAL seems to confuse common users but I have a few concerns

  • Mathesar types with only one db type - According to the image in this section, db type seems to be just another constraint used for fine-tuning. But in the case of only one db type that is related to the Mathesar type, it feels like Mathesar type is a group rather than an actual data type

• Given the goals of the Mathesar type system, does the criteria to define Mathesar types make sense?

  • Yes

• Does the current mapping of Mathesar types to PostgreSQL types make sense?

  • Yes

[dmos62]

Do the goals of the Mathesar type system make sense?

Yes.

I'm slightly cautious of the simplicity goal, or more specifically the fine line between undersimplification and oversimplification. To be clear, I absolutely agree that we want to simplify working with types. It's just that if we oversimplify, we might make something simple for a total beginner, but burdensome slightly higher on the learning curve. Types with time-of-day being in the same Mathesar type as types without time-of-day information is an example of this, I think.

Given the goals of the Mathesar type system, does the criteria to define Mathesar types make sense?
This is the section called "Defining Mathesar Types" in the Mathesar Types architecture page

What I said about simplification above applies to the "simple concept" criteria.

Does the current mapping of Mathesar types to PostgreSQL types make sense?

The time-related Mathesar type gives me pause.

---

As others pointed out, what the future users will be is a central question. I see two perpendicular axes of users: there will be beginners and highly-technical users, and there will be users that organize data and those that process data:

                    technical expert
      ┌──────────────────────▲────────────────────────┐
      │                      │                        │
      │                      │                        │
      │  (organizer expert)  │   (processor expert)   │
      │                      │                        │
      │                      │                        │
      │          OE          │           PR           │
data  │                      │                        │ data
orga- ├──────────────────────┼────────────────────────► processor
nizer │                      │                        │
      │                      │                        │
      │                      │                        │
      │   (organizer begi.)  │  (processor beginner)  │
      │                      │                        │
      │                      │                        │
      │         OB           │            PB          │
      │                      │                        │
      │                      │                        │
      │                      │                        │
      └──────────────────────┴────────────────────────┘
                    technical beginner

Of course this is a crude simplification. Might not even be a good one.

But, my main point is that some solutions related to types cater to some of these user categories, but compromise on the others.

A way to address that might be if we consider Mathesar types as mutable groups. Mutable as in user-customizable. The user could switch between different Mathesar type sets and compile his own. That way no-one is left disadvantaged.

The other idea that @mathemancer mentioned in passing above, is using a tag-system. Right now we're using non-intersecting groups of DB types and calling it a Mathesar type. In compliment to that, or as an alternative, we could tag DB types based on their interesting properties. For example, does the DB type have time-of-day information, is it string-like, is it numeric, does it have decimal precision, does it have a decimal. The user could then tell the database in terms of tags what he expects and the database could express the choice of DB types in terms of tags too.

This idea might be too far removed from our current UX specs to be practical. And, it's pretty hand-wavy at this point. But, I think it's an interesting concept to keep in mind.

In summary, outside what I mentioned above, I'm pretty comfortable with current state of the Mathesar type concept.

[kgodey]

@dmos62 @mathemancer @pavish @seancolsen @silentninja Thanks for all your comments. I'll respond to everyone here rather than replying to individual comments.

Here are my takeaways:

• I was thinking about data types only in terms of data integrity and not in terms of database operations available. I do think it's very likely that users will (as @mathemancer suggested) pick an email database type because they want to group by domain. So I think it makes sense to standardize database operations available across Mathesar types.
• @pavish pointed out that our UX depends on being able to cast easily between all data types in a given Mathesar type. Given that, we should add being able to cast between all DB types to the definition criteria for Mathesar types.
• I appreciate the suggestions for alternate type organization and type tags, but I don't think it's a good use of our time to reconsider the overall type system and all its product implications anytime soon. I will file these ideas in my head for future consideration, and I also encourage anyone interested to add them to our Feature Ideas wiki page.

Action I've taken based on feedback:

• Updated the Data Types "Concepts" page to clarify further that data types provide both data integrity and a consistent set of database operations.
• Made the following changes to the Mathesar Types architecture page:
  • Clarified the Goals section to include both data integrity and consistency of database operations.
  • Edited the Criteria section to:
    • Include the assumption that you have selected a set of PostgreSQL types and are now wondering whether they make sense together as a Mathesar type.
    • Add castability and consistent database operations as criteria to define how to group types.
  • Updated the list of Mathesar Types to split the original Date & Time Mathesar type into Date, Time, and Date & Time (based on the new criteria)
• Created a design issue to figure out new UX for the new Mathesar types: Split Date & Time data type into three Mathesar types  #960

---

Does anyone have any remaining unaddressed concerns?