Context:
I have a Game table which is a master table and it will hold pre-defined game rows, say:
Now, I could create a Game_Detail table, that will contain game played state/history/data specific to games like this:
Dilemma:
Individually these games will have heterogeneous data, and since I'm not using a NoSQL/Document database, this can't be stored efficiently in a single table. Thus I'm thinking of using the following approach:
Suffix the table name with the foreign key/id of the game. i.e.:
Some thoughts:
- Since code will access only the design-time/config-defined games, no dynamic Table name concatenation will be required, and thus no ugly code or performance concerns
- Again, since this all is at design time, ORM will not have a problem. I am using Sequalize with Node.js
- This may perform better, as keeping data in same table will help i/o
Extra:
- Don't want to use JSON, key/property lookup table for obvious reasons
- I believe this is a common scenario and I've tackled this in the past, but I'm not able to think clearly and thus need some help. Specially, If this is an anti-pattern, and will have any problems down the line?




I think you could do the following: