1

I'm trying to model database for storing dart throw practices.

Data sent from frontend will look something like this

[
  {"target_name":"S13","singles":1,"doubles":1, "triples":1, "misses":1}
  {"target_name":"S14","singles":5,"doubles":3, "triples":3, "misses":4}
  {"target_name":"S15","singles":5,"doubles":3, "triples":3, "misses":4}
   ... \\ from 1 to 100 rows
]

And i want to store in table/tables in SQL database.

On tables I want to make operations (in brackets I placed how this selects would look like) Requirement listed ordered by importance

  1. Get short game summary, (Select all_singles,all_doubles,all_triples,all_misses, points, datetime from SDT_GAME)
  2. Get Game data, so i will send back data as was given (Select gamesheet_JSON from SDT_GAME)
  3. Get Merged all games data for current user, for every target return (SELECT COUNT(singles), COUNT(doubles), COUNT(triples), COUNT(misses) from sdt_row INNER JOIN ... GROUP BY target)

I had 2 ideas like below (SDT_GAME2 vs SDT_GAME)

enter image description here

I like SDT_GAME_2 option more because of db normalization. But I'm worried that storing on average 50 rows for every game will clog db fast and make selecting(requirements 2) much slower.

Are any of these options clearly better considering my requirements?

0

When I data plan for a table or system, I think in terms of how much data do I realistically see being stored in 1 year, 5 years, 10 years and 20 years. I don't know how many games you expect in a given day and over a year's time, but accumulating millions of records in a single table in a year is not uncommon and definitely reasonable to support in most RDBMS. Obviously there's no concrete answer to exactly how many rows a table can hold at a given time and still be performant, because it's depend on the server hardware and database design, but again millions of rows in one table is not something to be scared of by any means. I've worked with tables with 10s of billions of rows that were performant running on servers with pretty standard hardware (8 Core CPU, 32 GB of RAM, SSD).

That being said, I think your database design is pretty good and I would definitely recommend going with SDT_GAME_2 because of the better normalization. The aggregations for all games data by user is something you should easily be able to calculate on the fly as needed (e.g. with a view or procedure) instead of storing the aggregation in a table.

7
  • Thank you for the answer. Just as I thought, it depends. I 'dont have much experience with working with large databases. Is merging old data is a reasonable solution? Let's say in my example, if number of user games will exceed certain limit (let's say 10000 rows is sdt_row). The idea is to sum up earliest sdt_games into one. (3rd requirement is achievable, 2rd and 1st only for latest data). Or is there any other way to solve such problems. – Tasteless Nov 7 '20 at 8:24
  • @Tasteless Sorry I don't understand your follow up question, why do you say exceeding a limit of 10,000 prevents you from achieving the 1st and 2nd requirements (except for the latest data)? You should be able to achieve #1 for whatever amount of data is in the tables (you can select it by date range, or by N number of records, or select everything, etc). #2 just requires you add the gamesheet_JSON as a field to SDT_GAME_2 (if you want to store the whole sheet at the game level). Or if you wanted you could break out each row of the JSON and store it in its own gamerow_JSON field in sdt_row... – J.D. Nov 7 '20 at 14:12
  • ...with its related row in that table. Either way can work, and you can always select the JSON field (again for whatever amount of data you wanted to select from). – J.D. Nov 7 '20 at 14:13
  • I wanted to limit number of sdt_row. My idea was to limit entires to that table for each user. Is storing JSONs in SDT_GAME_2 making solution same or even worse as in SDT_GAME? – Tasteless Nov 7 '20 at 14:22
  • @Tasteless Oh, why do you want to limit number of entries? I'd let it fill up with everything, and then you can query it however you needed later on. I think you need to store your JSON somewhere if you need it later (you could always rebuild it if you're storing all the fields from it in the database already, but for simplicity sake you can also just store the original JSON in its own field.) So I think it's ok to store it in SDT_GAME_2 and then you don't need the STD_GAME table at all. – J.D. Nov 7 '20 at 14:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.