Help with structuring my data

Ask for Help
1

Hi all

Loving Glide so far - amazingly fast to get started. My issue is that in achieving early objectives, I’m thinking of more and more things to do, and while I think I know how I would need to structure it in a traditional relational databse, I can’t figure out how to do it in Glide. Hence, I’m asking you lovely people for help!

The app is for my son’s and friends’ Rubik’s Cube hobby. I want the to be able to record times for solving different puzzles, and then to calculate stats (such as PBs, various averages - over 5 solves, over 12 solves, over all solves, etc) and leaderboards, by puzzle type (eg 3x3x3 cube, 4x4x4 cube etc).

The recent addition is puzzle type, which I am struggling to figure out how to do in a flexible way that will enable me to add puzzles without huge amounts of work.

I currently have a table for users, and one for solves, which are joined. The users table has a lookup column which holds all of a given user’s solve times, and uses a load of columns to calculate the various stats I want to do, by person. I then combine those in to an array and sort them to find leaderboard positions. All in the User table. But this uses about 15 columns per statistic. And that’s just for a single puzzle type.

I have created a new table for puzzle type, and will log a puzzle type against each solve. But I can’t then work out how to calculate the statistics by puzzle type. If I do it the way I have now, it will be an additional 15 columns per puzzle type per statistic, and that can’t be the best way. In fact, I’m fairly sure that there’s a better way to do what I have already done using clever queries/filters/calculations etc.

I have ideas for other dimensions I’d like to add also, so this is something I really do need to make work before I can take it further!

Any help very greatly appreciated!

2

If ultimately what you need is averages for each person, by each puzzle type, then I fear there’s not a good workaround. Rollups will still be needed for each average, on top of a query for each user’s record for a specific puzzle type.

1 Like
3

I built a very similar App for my little boy a couple of years ago.
Mine has 4 main tables:

  • Users
  • Cubes
  • Solves
  • Algorithms

Algorithms is really just a collection of learning resources and references. Youtube tutorials and walk-throughs, etc. My boy at the time was going through a phase of collecting cubes and learning to solve them, so the App was built primarily as a place to keep everything together and use as a learning tool.

In terms of stats, all I really have is a leaderboard of best times per cube, which is shown on each Cube’s details screen:

CleanShot 2023-11-03 at 11.34.57@2x
CleanShot 2023-11-03 at 11.34.57@2x658×1344 215 KB

Sounds like you should be moving some of that to your Puzzles table.
The other thing you might want to consider is a general purpose Helper Table. Helper Tables are great, because they essentially allow you to re-use logic without having to duplicate it.

For example, if we take your 15 columns and move them to a Helper Table, then add a couple of User Specific/Single Value column pairs - one for a UserID and one for a PuzzleID, then you could have all those stats dynamically generated depending on which combination of User and Puzzle you are viewing.

For leaderboards, you probably want to keep that logic in your Users table, but rather than duplicating the logic 15 times, instead add a column to hold PuzzleID, and refer everything back to that. Then allow your users to switch between Puzzles to view the stats for each.

I’d say in general keep any logic for User-related stats in your Users table, and Puzzle-related stats in your Puzzles table, and look at leveraging Helper Tables for dynamic, switchable views.

3 Likes
4

Thank you so much Darren - amazing that there’s such a knowledgable member of the community who has tackled exactly these problems before!! I’ll have a go at this for sure. I started reading up on helper tables yesterday and they felt like the answer, but I have to say, without trying it, it is all a bit hard to get my head around the concept!

Looks like you have a timer built into your version - can you tell me how to do that? Are you using an action to add a row to the Solves table with start time, and then on the next press, record the stop time, and calculate the duration? I don’t have that built in, but it’s a great idea!

5

No, mine is so old it’s actually a Classic App. So I’m able to use the Stopwatch component (which isn’t available with the new Apps). I use a single row helper table to record the start/stop times, and then a “Save” button with an add row action to add a row to the Solves table.

But you could do something similar with just a single button. The only issue is you won’t be able to get super accurate times, because the Glide internal timer only updates every 10 seconds. Which could be a problem if your boy is doing sub 10 second solves. Certainly not a problem for me :rofl:

2 Likes
6

He isn’t, he’s around the 30s mark, which is where it looks like yours is too! But for now, he can just use the external timer, I think!

So grateful for your help!

7

add a couple of User Specific/Single Value column pairs - one for a UserID and one for a PuzzleID

Can you help me with this? I can’t set a single value column as user specific. I have a working helper table, but ideally would like to be able to pass it a user and puzzle, no matter which user is looking at it, so that they can see other peoples stats (subject to that person’s privacy settings).

8

There are a couple of different ways you might use Single Value columns with a Helper Table, depending on the actual scenario. But in (almost) all cases they should be referring to User Specific columns, which makes them in turn user specific.

  • a good practice is to add a Single Value → First → Whole Row column that references your Helper Table to your User Profile table. You can use this column to both set values in the table from anywhere in your App, and also to navigate to a Details screen that sits on the Helper Table from anywhere in the App.
  • if your Helper Table has multiple rows, then you might use a Single Value column to apply a User Specific value to all rows. This could be sourced from the Helper Table itself, or from some other table, depending on use case.
4 Likes