Efficient way to structure user specific hierarchical data in Data Tables?

Anvil Q&A
1

What I’m trying to do:
In DataTables, create the following relationship efficiently to add, search, and delete topics. Topics follow a tree hierarchy, in that, each topic node can have many child nodes, and each topic node can have only one parent topic node.

A User has a collection of Customers.
A Customer can be subscribed to any sort of topics in a Topic Hierarchy, which belongs to only one User.

How this might be represented to the user would be something like this, where topics are visually represented as a hierarchy of checkboxes, so if you want to subscribe to the topic called “Dairy Promotions” as a customer, you are automatically subscribed to the topics “Cheese Promotion” and “Milk” promotions.

What I’m thinking of doing
Method 1, Big table for all users: Create a “Topics” table, with the topic name, payload, child_topics, and parent_topic.
When adding a new topic, a topic hierarchy will be constructed from querying the Topics table, so it’s only looking for the topics for the current user.

Method 2, Table per user: Create a Topics_For_User_XYZ per user, structured the same way ( with the topic name, payload, child_topics, and parent_topic, etc…) and then somehow lookup and create the topic hierarchy that way

I’m sure I’m not the only one who has run into this type of problem, that is, how to represent hierarchical data unique to each user for an app.

2

You may want to consider using a SimpleObject column. A value in this column is, in itself, a hierarchy (tree) of data items. Arrange the structure and content of each value as you see fit.

Edit: There are tradeoffs with this approach. As a single value, stored as JSON, retrieval is very fast. There are no other tables to search.

The flip side is, it can’t store some types of data (e.g., dates, media objects) directly. So in some cases, it’s better as an index to the bulkier iterms.

1 Like
3

I try to use anvil datatables for everything that I can, however if I wanted to do specifically what you are trying to accomplish, I would look into running a docker container with a Redis server.

Redis does literally everything you are asking to do by default, without having to re-invent all the pieces in an anvil datatable.

You can still use redis directly with python as well, and therefore anvil.

https://hub.docker.com/_/redis

Redis Basics: Strings, Hashes, Lists, Pub/Sub

Docker Concepts Introduction

4

I think you should use a ‘link to table’ column. You can assign multiples customers to users that way

5

Good idea on the SimpleObject. Gives me an idea on how to structure this is a relatively structured manner. This isn’t the full table design, just for the topic hierarchy part of it, (@divyeshlakhotia , I’m already using the “link to table” for the customers part, but good suggestion :slight_smile:

One question I have, can a single Simple Object contain other Simple Objects? (For example, a Dictionary with values of Lists and Dictionaries, and other derived types?)

Users Table Will contain the following columns (for the topic hierarchy part):

Simple Object of type List called Topics

Simple Object of types Dictionary, List, and Topics called TopicHierarchy. This is a tree structure, where the values of the nodes can only come from the current user Topics collection

Each Customer for each user will have a column Topics, which is a list of subscribed to topics.

If I want to load and display the hierarchy of topics that customer1 is subscribed to, I load the appropriate existing user TopicHierarchy, loop through each of the Topics, which are a key in a kvp for the TopicHierarchy, checking or unchecking a checkbox to show a users subscription status.

6

A simple object can contain arbitrarily complex data (subject to the restrictions on data types of elements). For example, I have one column that contains a list of dictionaries, and some of those dictionary entries have lists of dictionaries of their own.

2 Likes