One-Row to Many-Items with Data Tables

Anvil Q&A
1

Hello Friends:

I’d normally model this scenario using one-to-many relationships (or vice versa) and foreign-key constraints in a RDBMS, but I’m trying to keep this App under one roof and therefore exclusively use Data Tables (so far, in my personal plan).

So imagining this with Data Tables, for a given Row I need to store a growing collection of dict()s - associated with that Row (and there are about 1700 rows). Let us say that there will be about 60 items per Row, but it could be 100s (hard to tell right now).

One option is to use a Simple Object column and keep appending to it. I was just experimenting with that, and observed the following:

  1. One mistake (like the return of None) or data-error can wipe out an entire collection.
  2. If the collection happens to have a custom class item embedded within it (say a portable class), since the entire package name is stored, refactoring can cause problems.

I’m not so worried about (2) as I am (1) (data loss). But the concept of 1-Row_to_Many-items needs to be fulfilled.

Another option is to use a second Data Table to store each of said items in its own Row there. I believe the Link facility might aid this. I see a post by @p.colbert that I’ll dig into (in addition to circling back with the docs).

In summary, each Row of one table (~1700 of them n total) needs to store 100s of items, possibly in Rows of a second table.

With apologies, I’m finally circling back on Data Tables to see the best and safest way to accomplish this (there’s a lot under the Anvil umbrella to understand). :slight_smile:

You thoughts are welcome. Thank you!

2

For various reasons, I do this the same way you would in a relational DB. The many table has a singular link back to the one table, the same way you’d use a foreign key.

I don’t use simple object columns for this purpose, because of the possible growth of the data, and the inability to have the db filter those linked rows if they’re all in the simple object column.

I also don’t use multiple links (to link from the one table to the rows in the many table), because of historically poor performance on those as the number of links grows. That might have been fixed with the recent introduction of accelerated tables, but I’m happy avoiding large numbers of multiple links anyway.

1 Like
3

HI @jshaffstall Thank you.

That’s another good reason - seachability and filterability.

To echo what you wrote, you use this basic pattern:

RowX = app_tables.table_A.get( ... qualifiers-to-get-RowX ...)  # Followed by...
app_tables.table_B.add_row(table_A_link_colName=RowX, [your_content])

table_B in this ( contrived ) example would be hosting datata (content) for the many Rows in main table_A.
Is that the gist of what you meant? Thanks!

4

Table A would be the one table, and table B would be the many table, in the one to many relationship, yes. That’s basically what I do.

For searching, if I have a row from table B, I get to table A through the link directly. If I have a row from table A, I can use app_tables.table_B.search(table_A_link_colName=row) to get all the matching rows from table B.

1 Like
5

Yes, this approach warms my heart (LoL). On experimentation with Simple Objects, the nested growth, the potential to clobber whole objects (which is not difficult to do), and not first exhausting what’s natively available with app_tables for searching and filtering (as you reiterated), didn’t make sense. I’ll use the two-table approach. Thanks again.