May I ask you how do you deal with actions and views for this multiple slices? Is there any naming convention you have been using? For example I slice my table and name the Slices the same I would have named each entity if it where different tables on my database.
Naming your slices based on how you would name a real table seems like good practice, and would make it a bit easier to transition to a real table if you ended up normalizing the data in the future. Assuming you’re preventing users interacting with the base table, you can just assign slice-specific actions on the slice itself.
Second, it is actually helpful this one-table-multiple-slices out of AppSheet? How easy it is to work with?
Outside of AppSheet, in an environment where we can control when and how the data is updated/loaded? Nowhere near as useful, and I would probably err on the side of a normalized data structure. There are several better methods to handle 1:1 relationships with AppSheet out of the way.
With AppSheet specifically, its probably the best strategy for dealing with 1:1 relational datatypes… any other method for 1:1 relationships will have at least one of these drawbacks:
- require 2 or more data sync events when adding/deleting records
- create annoying delays when adding/deleting rows (e.g. I add a user record, now I need to wait for an automation to complete before I see the related 1:1 record)
- potentially slow your app down (re: data parallelism bottlenecks)
- prevent you from editing data from both tables side by side (e.g. in a single form)
You can avoid all of the above issues by forgoing a bit of database normalization. If you need to normalize the data in the future, normalizing 1:1 data is about the easiest thing you can do. You can simulate normalized 1:1 data from a single large table by using SQL views.
When would I not use this strategy?
- When I need independent row-level security (e.g. in the case of medical information on user records - we would want to be able to load a list of patients without loading their 1:1 medical details in the same query).
- If it would result in a wide table where most of the columns are NULL
I’d consider the EnumList basetype Ref something just practical inside AppSheet for many to many, while on other systems I think a join table is needed.
Best practice when modelling many-many relationships on a RDB would be to use a junction table. SQL operators like joins, unions, intersects are all table/row-based, and tabular data gets indexed so it can be queried more efficiently. Junction tables also let us store information about the relationship, like a timestamp when the relationship was established, or who the user was that created the relationship.
With that said, there are times we might want to pair unstructured data with relational data. One example would be JSONB columns in postgres. When we say EnumList basetype Ref we are really just talking about a comma separated list of foreign keys, or, an array. One example where you might encounter database arrays in the wild is with tags. In practice, an EnumList basetype Ref is similar to a database array. I don’t think AppSheet supports either of those datatypes, but the comparison is still worth making if only to destigmatize things.
There are many situations where, within the constraints of AppSheet, an EnumList will provide a better user experience than a junction table. And, since AppSheet decomposes relationships into comma separated lists, an enumlist is probably more performant than a junction table as the decomposition is already handled.
EnumList vs Junction tables have pros/cons in terms of user experience, so there is a place for both in any one AppSheet app. I would recommend focusing more on providing the best user experience and less on curating a pristine normalized relational data model.
, thanks again