One-table-multiple-slices vs multiple tables with auto creating children via actions on parent row save.
Currently I use the one-table-multiple-slices approach when it comes to 1-to-1
The thing is that the "child" record (it's weird to call it like that since it's the same row currently) needs to be added at the same time that the parent, and same applies to a grandchild because I have two 1-to-1 on the same table, 3 slices basically. So the one-table-multiple-slices sound like the right way for this case.
My problem with this current scenario is that actions are attached to the table, not the slice, so I have a looooot of actions on the same table and also managing views is quite tedious.
My problem:
I'm thinking of the usage of this one-table-multiple-slices outside of AppSheet. How does it works on other systems? How standard is this thing? Would you advice this or the second way?
Also, what's your take on managing actions when there are many slices?
Thanks!
@MultiTech @1minManager @Suvrutt_Gurjar @Marc_Dillon @Aurelien @Joseph_Seddik @Steve @Grant_Stead @WillowMobileSys (no particular order)
I'm a little confused by what exactly you're asking, but I'll try to respond to a couple points.
So, you're saying that to simulate a 1-1 relationship, you're just creating a single record, but separating the columns via slices? Why? What benefit does that bring? That's not a 1-1, that's just one record being sliced. I can't say I've ever done that. Or maybe I have, I just didn't try to call it a 1-1 relationship, because it's not. A record in a table should always represent a single "entity". If you need to relate 2 entities together, create 2 different tables, where one or both has a foreign key reference to the other. Nothing about your method sounds "standard" to me, and I don't think that I would advise it, but I may just be completely misunderstanding it.
As for managing actions on slices, the only thing in Appsheet that would make me set particular actions to particular slices, would be if I'm wanting to display 2 sub-views of the same table within a dashboard, but need different actions to show on each. And this is only due to the restriction of using CONTEXT("View") with a dashboard, that it only returns the view name of the dashboard, not the sub-views. This is not something that I've done with any frequency whatsoever.
Does any of this help you, or answer your questions in any way?
Sure, it helps. You got it right.
The fact that I use the same row is a workaround to the need for a one-to-one relationship inside AppSheet. I'm not the only one that does that, I think @WillowMobileSys use it as well.
It's benefitial because I can have the same row as a child of itself, and I do it twice.
My main usecase for this is when I need different entities where each one on table B should always exists and be related to the one on table A.
The thing is that this method helped me a lot as a workaround but I think that you also answer my question about how standard it is.
Lately I have been using actions on more advance ways to the point I'm feeling more confident with them and I think that I have a better workaround for the one-to-one paradigm which is instead of creating just one row on a single table separated by slices to represent parent, child and grandchild, I could use an action to create the child on parent creation and then when selecting the child on the inline another action would create the grandchild if it doesn't exist and go to the detail view. This way each one would be a separate table (one table per entity) and respect(?) the standard.
That's the idea and your comments helped to convince me about my thoughs on a second workaround ๐, thanks again
Both patterns (one large chopped up table / several related smaller tables) can be situationally correct.
Chopping up a single large table offers several benefits, specifically:
In contrast, starting with several discrete tables:
Hi @Jonathon, thanks for passing by!
I'm glad you took your time to give us your opinion since I forgot to tag you (I'm sorry).
Well, that makes me feel better, knowing that both options are not just an invention inside my mind.
My needs are actually covered with my current setup, it won't be one to many ever and I don't need to have rows from one entity without the rows from another one, so having a single table makes sense.
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.
Second, it is actually helpful this one-table-multiple-slices out of AppSheet? How easy it is to work with? For example, 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. Is this one-table-multiple-slices on the same league or I'd just need to make different SQL Views for example mimicking the slices on AppSheet?
Thanks again!
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:
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?
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.
That's a reply worth bookmarking
Thanks for all of the insights
User | Count |
---|---|
32 | |
31 | |
30 | |
18 | |
17 |