Detailed budget and sum – 1, 2 or 3 tables?

Ask for Help
1

Use case:

  • A table of items, each with a name and amount.
  • Let’s say I want a screen to display a collection or table of these items and below it the sum of the amounts.
  • This use case would be useful for inventories, budgets, accounting, finances, etc.
Name Amount
Item1 $1
Item2 $2
Item3 $3

SUM: $6

How would you set this up?

Layout editor

Let’s use the following components: a New Table and a Big Numbers (New Table and Text components would be fine too).

Data editor

Here is where my question lies. How would you set up your tables? I’m curious to see how others do it and why.

Ideally, here are elements I’m looking for:

  1. Modularity / Adaptability: what data structure will be the most solid long term.
  2. Cleanliness: what data structure would be considered clean (respecting normal form)
  3. Simplicity: what data structure reduces complexity
Here are a few options I see

Option A (1 table)

A single Items table only. The items and rollup sum are in the table. The table is the data source of the screen.

Option B (2 tables)

A table of items. An item helper table for the rollup sum. The item helper table is the data source of the screen.

Option C (2 tables)

A table of items. An “All tabs” table as the data source for all screens. The rollup sum in this “All tabs” table.

Option D (3 tables)

A table of items. An “All tabs” table as the data source for all screens. An item helper table for the rollup sum. A single value column in this “All tabs” table to pull the rollup sum.

Option E (using the Users table)

Put the rollup sum in the Users table.

2

The case may change depending on what seems appropriate at the time, but I typically gravitate towards Option B. Seems cleaner to have a helper table to drive the screen, especially if you have other computed columns that you don’t want slowing down your Items table because it’s doing the same calculations over and over for each item row. Calculate once in a single row helper table and be done.

This is especially true if the screen takes user input. For example, a screen for a custom form or custom filters. If the first row of the Item table can be deleted at any time, then you probably don’t want to be using it to summarize data or accept user input for custom filters or custom forms as it could wipe out another user’s entered values when that row is deleted. You also have the case of possibly not having any item rows, in which case your screen is all of a sudden not linked to a row, so there is no way to accept user input. For that reason Option A is out for me.

Also, Option B may not always involve a helper table per se. For example, I have a table with several rows for invoices. Each invoice has a date range and also has computed columns to summarize totals for all lessons that occurred within that date range. When I display one of those invoice rows, I display the totals in a Big Number as well as a collection of all lessons. So, a traditional parent child relationship that doesn’t rely on a helper table.

If the table is small or I’m just lazy, I may do Option A, but not very often. At scale, it doesn’t make sense.

I don’t think I would consider Options C or D. I feel that a helper table she be purpose driven instead of driving multiple tabs. It may work if it’s only used at the tab level (since you are guaranteed independent design), but if you are using it for multiple other things it may bite you later on. For example, if you get to that table using a Show Detail Screen action, then you may run into conflicts if you want to use a Show Detail Screen from somewhere else for different reasons. Sure you can always use Show New Screen, but it may not be practical if you need that screen layout to be linked to the table.

As for Option E, it depends. I’ll only put stuff in the user table if it’s needed globally. I don’t like clogging up my user table with data that doesn’t pertain to users, and for the same reasons I don’t like Options C and D, the user table is not purpose driven if you start throwing a bunch of random things into it. It’s convenient because it’s the only table that provides global access, but I’d probably use a single value column to bring a value from a helper table into other tables before using the user profile as a go between. I can go back and forth on that one. Just depends on the situation.

Overall it comes down the the specific use case. These aren’t hard set rules that I follow. Sometimes I’ll set things up once way, realize it’s not efficient for the use case, and switch to a different method. But most likely, Is go the Option B route in most cases.

4 Likes
3

If the table needs to be displayed on the top level of the tab, I would usually do the rollup on the Users table, since I build most tabs on top of the Users table.

If you need the sum to be a part of the table itself, like a bold row or with a different background, I would create a JSON for each row in the Items table, join them together as a JSON array in the Users table, and use JavaScript to construct a HTML table with the last row being the sum.

1 Like
4

Thanks for sharing your thoughts. I find it particularly interesting how you are open to changing your approach depending on the specific use case.

I too change approaches, not based on the use case, but rather on my methods evolving. Ideally, I would develop habits, see that they tick all of my boxes (scalable, maintainable, modular, etc.) and I would never change.

For a while, anytime I created a high level tab, I would create a new table that would be its data source (option B), even though that source tab might have little to no data (the minimum being one column and one row). I got tired of having so many “useless” source tables so decided to consolidate them in a single “All tabs” source table, but I dislike that table as well, it’s clearly an concatenation of totally unrelated columns. So currently if I were to rigorously follow my process, I would opt for option D, which feels silly.

It’s interesting to me to see how others build. I believe Mark and Oscar might go for options C or D.

1 Like
5

I’m torn when it comes to the Users table. On the one hand it’s the only global table we have, so it is very practical. On the other hand, I consider it a collection table like any other, it is to house attributes that describe the items of the table (users), and so I dislike dumping random data in there.

Thanks for sharing your approach, your insights are making me question and rethink my own.

(Your second paragraph has a lot of acronyms in it, the only one missing was CSS :wink:)

2 Likes
6

Should have said “HTML table with inline CSS styling”.

1 Like
7

If there’s one thing I learned during my career, it’s that there is more than one way to do everything. We have some general guidelines and standards where I work, but every developer has there own style to some degree. At the end of the day, if the software compiles and runs efficiently without errors, then that’s all that matters.

There’s probably hundreds or thousands of different programming languages and you can use them all to write a simple program that says “Hello World”, using several different methods within each language itself. One language or method may be better than the other, but ultimately the result is the same. Some people just feel more comfortable using one language or method over another. Doesn’t really make it wrong it right.

Do what feels comfortable for you, but be willing to adapt as you learn what does and doesn’t work well. Technology is always evolving and I think it’s good to personally evolve as well. I’ve reworked some logic in my app several times over the years as I’ve learned new and better ways to do things, and as new features have been released. It’s good to have a general methodology for how you develop apps, but if something doesn’t make sense for a particular use case, then I’d say, don’t be hesitant to approach it differently. Don’t feel obligated to follow one set of rules if it doesn’t apply for a particular use case.

Some people feel that they have to have every aspect of their app perfectly worked out before they even start building it. That’s personally not how I work. I start with very basic core functionally in my apps and then build up each piece from there. I almost always come across an unexpected situation where I have rethink the flow and design of certain parts of my app. Usually due to something I never even thought about, or new a feature request by a user.

Do what feels right. You can always change something later on in the development process.

4 Likes