Pretty much a backend question... but I don't do m...

# random

[Colton Idle]

Pretty much a backend question... but I don't do much backend so i wanna do a sanity check.

I'm working on an app that's essentially a pomodoro timer as well as todo list. I want to store two pieces of data that will be attributed to each user.
"pomodoro_sessions" and "todo_items". It seems like there are two main ways I can setup my db.

Option 1: Create a "todo_items" table and "pomodoro_sessions" table where there is a foreign key relationship to the auth'd user.
Option 2: Create a "users" table and each user has an array of "pomodoro_sessions" and array of "todo_items".

Is that pretty much the two options I have? Am I missing anything obvious here? Is one route glaringly better than the other? I think I'm leaning towards the users table route with two columns. Thoughts?

[Youssef Shoaib [MOD]]

Option 1 is definitely the "right" approach based on popular wisdom. The point is that updates to individual items would be very easy, and you'd have all the SQL goodness to be able to filter etc, while just storing an array in a column doesn't provide that much advantage, and forces most processing to happen on the Kotlin side

[Colton Idle]

Interesting. I figured if something I want to show often is "how many sessions has my [e-mail] had" it'd be easier/faster to have a user table, and a column for sessions (array).

And also... if I have like 50k users (thats my current number 🎉 ) and all of them create like 1 session per day. then in a month that's 1.5 million rows. 10 million rows in a year just to store the fact that a session occurred + the length of the session.

[Youssef Shoaib [MOD]]

How much data is that going to consume vs an array though? I'm assuming you're storing the length of the session as well as the date/time it happened. That's already 2 ints/longs or whatever. The foreign key to the user is only an extra int/long. No need even for a unique id, instead you could use the user and datetime as a composite primary key
You could store a redundant column on the user, and increment it on each session. Then, once in a while, recalculate the session counts based on the source of truth (maybe there's an elegant SQL way to do this, not sure).

[Colton Idle]

thanks for teaching! i definitely learned something new

[thanksforallthefish]

Though (playing advocate's devil, I'd probably go for solution one too), modern SQL engine are supporting JSON objects more and more, especially Postgres; you might be a bit on your own building queries since I don't think those capabilities are yet wildly used/supported by libraries

[thanksforallthefish]

but definitely, option 1 is more popular and I think still more flexible, especially if you pair a read model with it to take care of common read use cases (an aggregation table, populated reacting to events modifying your "core tables")

[Colton Idle]

thanks. yeah I think I might be able to get away with a small number of items in my users table for todo items since I'm literally capping it at 10 items per user. but this entire thread was really insightful. im glad i asked.