What kind of database or way to implementation in mysql

Question

I have created database where I have table where I´m saving game activity of footbal match (goals, assist, red/yellow card, 11m, ...). I want to make another game f.e. basketball, but also two player sports - table tennis, tennis, ... The problem is that there is a lot of things implemented in this database and it is MySQL.

My question is. What is the best way to do. Create new activity table for every sport (becouse there are different activities) or create column where will be something like XML file and every sport module (JAVA) will be working with that XML or there is another solution for that?

Thank you for response.

Answer 1

You don't want to store non-relative data in the same table.

I would suggest either a separate database per sport, and at a minimum a separate table. You obviously don't want to store a Tennis player with the Baseball players.

I would suggest making new tables for each sport. Name the DB something relative to all to all of them like Sports. Each table could be named the sport.

Answer 2

Do not store junk in your database. normalize the data you put into it, always, for your own good.

Having been a programmer for a long time i've never seen anything good come from storing xml or json blobs in the database a couple of years down the line. Imagine for instance you want to change the structure of the stored xml/json data, or there is an error. Now you need an extra tool to work with it.

I've worked in the sports stats business. You will want to normalize your data for every specific game type. (maybe even give them a separate database). they are all just slightly different, and soon you want to be tracking game,set,and match for tennis and heats for races and halfs for soccer, track rankings within different game types and scoreboards for wildly different game systems.