I searched but could find a similar situation, if I missed it please let me know. In this game there are approx 14,000 items which have their own table. The players will want to build multiple collections of these items. The collections would generally range from 20 to 100 different items in varying quantities, but could have hundreds of different items. The problem comes with the storage of these collections. I can't create a collections tables with each row a collection because the number of fields varies so much. I only see two solutions:
- Create a collections table with a BLOB data field and store the data as a JSON object.
- Give each collection its own table with an item ID field and a quantity field. Create a collection list table with the user id and the corresponding table name.
Is there a better option? If not, I am leaning toward option two so I can easily run statistic queries. Due to the number of tables that could exist would it benefit me to keep the collections in a database separate from my other tables?