Should I use JSON in MySQL here?

Im trying to decide how I should structure my data here. Im trying to move off of Firebase to Planetscale. I know that you can use JSON in MySQL, but I know you should avoid JSON if you can, especially if you wanting to index that data. But I do not need to index this data at all, im not doing anything special at all here. The Action Events is what I am thinking about using JSON for. I would store the actionEvents inside the possession table, and on the right-hand side in the picture there is a pretty typical example of what an actionEvents array would look like. If I didn't use JSON (I have a picture of the schema for this too), I would be JOINing from the games table to the possessions table and then to the actionEvents table whenever I wanted to query this actionEvent data . And this is data that I am querying very often. So this just feels super unnecessary to me.
6 Replies
Cyclops3142
Cyclops3142OP3y ago
I wanna go with my gut and just use JSON bc I feel like it makes sense, but then I hear all my old professors & and my previous boss saying that JSON doesnt belong in SQL 🤷🏻‍♂️
Unknown User
Unknown User3y ago
Message Not Public
Sign In & Join Server To View
Cyclops3142
Cyclops3142OP3y ago
Ive just heard horror stories about extremely long JOIN queries, and each game could have ~100 possessions, which each possession usually has between 2-3+ actionEvents which at ~26 games for a team year that puts a team at around 6500 actionEvents a year. So it would only take 150 teams for one year to get us at 1 Million actionEvent rows in just one basketball season. Which we dont have that many customers yet, but its going to start to get close next year.
This is what scares the hell out of me, doing a double JOIN query on table with 1 M rows while not even being scaled too too much yet. Am I crazy for thinking that these queries could get very very long, once this gets to real scale? I feel like if there is a case for using JSON in sql, my case does fit, esp bc also: 1. I am not using any of this data for indexing 2. the data types in the JSON are nothing like Dates or anything like that, so my typesafety should be totally fine still But, if I really am making a mistake by doing this pls help me understand where Im going wrong im any assumptions/claims Ive made. 😂
Unknown User
Unknown User3y ago
Message Not Public
Sign In & Join Server To View
Cyclops3142
Cyclops3142OP3y ago
Gotcha, I hear you. Thanks for the advice!
Unknown User
Unknown User3y ago
Message Not Public
Sign In & Join Server To View

Did you find this page helpful?