I'm in the beginning phases of a complete redesign of my existing youth sports league website (www.parochialathleticleague.org), using Vue.js for the frontend, and need to make a final decision on the backend structure, so I can move forward with the project. For the past 5 years, we've used a MySQL database with custom PHP scripts as the intermediary to send, receive and edit data in the league schedules and standings, etc.
I'm confident I could use MySQL effectively again, perhaps with smarter table structure, but very intrigued about the possibility of utilizing MongoDB for this project instead as a NoSQL experiment. I know, I know.. there's a lot of hate out there for Mongo and it's best suited for very specific use cases, but I think it would be an interesting thing to become acquainted with for future reference.
That said, if I decide to go with Mongo, I would really appreciate some insight from those with far more experience about what can be expected from performance of the database in this proposed use case. Specifically, there are three potential options that I would consider for organizing the schedule data. Please see below (I've included an example of one game's data for each option):
Option 1 - Collection: "2018 Boys Basketball - Coastal 2A"
{ "date": "September 7", "time": "3:30 PM" "home": "Blessed Sacrament" "h_score": 0, "visitor": "St. Columban", "v_score": 0, "location": "Blessed Sacrament", "game_id": 260 } In the above proposed solution, I would make a separate collection for each divison. This past season, there were 34 total divisions in the sport of basketball, so that would equal 34 collections in this example. That means more organizational overhead and more time to configure, but logic would dictate it's the fastest possible solution for the end user because any one division holds no more than about 50 games. That's also how I've had it set up in our existing website's MySQL solution: one table per division.
Option 2 - Collection: "2018 Boys Basketball"
{ "date": "September 7", "time": "3:30 PM" "home": "Blessed Sacrament" "h_score": 0, "visitor": "St. Columban", "v_score": 0, "location": "Blessed Sacrament", "game_id": 260, "division": "Coastal 2A" } In the above proposed solution, I would make a separate collection for each gender, resulting in only two collections - one for all of the boys' basketball games and one for the girls'. In terms of organization and setup, this seems far more desirable than having to create 34 collections as in the above example. However, I'm unsure of how much slower this will be for the end user because now the collection may hold up to 500 games, rather than 50. So, parsing through 500-ish games to locate the 50 or so in one particular division before rendering that division's schedule in the browser could be an issue - or, hopefully, not at all!
Option 3 - Collection: "2018 Basketball"
{ "date": "September 7", "time": "3:30 PM" "home": "Blessed Sacrament" "h_score": 0, "visitor": "St. Columban", "v_score": 0, "location": "Blessed Sacrament", "game_id": 260, "division": "Coastal 2A", "gender": "boys" } In the above proposed solution, I would make a single collection for all of the games in the particular season. This could mean up to 1000 games total and I'm thinking that speed for the end user might become a real concern.
Your thoughts and suggestions are much appreciated. Would really value some real world feedback before making a final decision. Also, if you think I'm a complete idiot (I don't necessarily disagree!) and should definitely stick with SQL for this project, I would pose the same question about the above three options, just in table/row structure. Many thanks!
