One spreadsheet table per โentityโ (a set of things in the real world) is the norm in AppSheet:
Every entity that you define (Job, Employee, Building) should have its own table (or sheet) within the document that you are connecting to AppSheet.
Put more simply, it is often the case that a โviewโ (UX) will have itโs own table. I think this is good standard practice for beginners but there are potential drawbacks. When, for example, I built a flashcard app with a separate table for each โsideโ of the card, I found that I sometimes had to wait quite a while for the two tables to sync with each other before I could continue using the app. And, there were other problems that stemmed from separating the data into two separate tables.
Since then, I have rebuilt the app with a single table that contains all of the key data for every record and the multiple views that are needed are associated with separate slices. Restructuring the app in this way took some time but the result is good: I no longer need to wait for syncing (the app continues to function while data is synced in the background).
The slices function like tables (allowing me to limit certain actions to certain views, etc.) and their functionality goes way beyond merely โslicingโ (selecting a subset of ) data. Apparently, Iโm not alone in preferring to associate slices with views instead of directly attaching views to tables:
There are many others on the forum who seem to be following this approach. I thought Iโd write this little โtipโ because I think the โMultiple tables vs. Single table + multiple slicesโ question is a key one that needs to be addressed fairly early in the app design process but doesnโt seem to be addressed yet in the official AppSheet documentation.
Thanks!
Today I learned about another advantage of using slices โ they make it possible to reorder forms:
Am I right in thinking that if the app needs parent/child relationships, this canโt be done using one table?
A table can reference itself, so you can have a parent/child relationship using only one table. However, if I recall correctly, AppSheet doesnโt maintain the Related columns (with the REF_ROWS() app formula), so youโd have to add them yourself if you want them.
Thx, thatโs interesting. I think iโll do a dummy app to get to grips with it.
I have an app with many-to-many relationships, between two tables, via a link table. This, Iโm thinking, is a bridge too far for a single table?
Iโd need more specifics to form a qualified opinion, though just on the surface, youโre probably pushing the boundaries of what AppSheet is suitable for.
Agreed. Appsheet can handle a fairly large app I have that has two sets of many-to-many link tables, but querying through them can start to cause speed problems. Thatโs why I was interested in using one table. thx
Good luck! Reducing the number of tables in my app significantly improved the performance. Users no longer have to wait for 20 seconds or so for a sync to complete.
Thx. Iโm definitely going to explore this.
I tried to figure out this and manage to add refs slice to itself (one table) in this case I need only one ID. But how to add in one table more than one IDs, different ID for each slice?
Table 1
Column 1 ID for table
Column 2
Column 3
Column 4 Slice 1
Column 5 Slice 1
Column 6 Slice 2
Column 7 Slice 2
When I tried to add record for Slice 1 which is Column 4 and 5, I got error message โthere is already a row with the keyโฆโ Column 1
I think I misunderstood the conceptโฆ any explanation and help would be greatly appreciatedโฆ
In my app, I donโt use different keys for different slices. In fact, having the same keys is useful. However, if you add a new row, then you need a new key. Are you familiar with uniqueID()?
Yes I am using UNIQUEID() for initial value of Table ID. For example how to get new row of slice of the same User ID (Table ID) if its unique?
Not possible. A table can have only one key column. A slice exists atop a table and will always only use the tableโs key column.
Thatโs what I should have said.
Thank you @Steve and @Kirk_Masden, I should have another table thenโฆ