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:

  1. Create a collections table with a BLOB data field and store the data as a JSON object.
  2. 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?

  • Are you going to run statistics on a particular table/collection or combine several?
    – JeffO
    Commented Jul 16, 2015 at 8:08

1 Answer 1


You want to store an undefinied number of items in a list, for this you need a new table or cleaner two tables.

you already have the first table called items, each has an item_id.

Then one table holds all the references to the collections, so best case only a new collection_id for every of the collection.

And the important one is the MxN relation table between items and collections, let's call it collection_items, holding in each row one item_id and one collection_id.

If you want to have multiple same items in one collection, and only want to know how many, add a count field.

But if the relations need to be distinguishable, add a row for every relation and an extra collection_item_id.

  • Would this join table be more efficient than the multiple collection tables? The join table would likely have tens of thousands of records.
    – Applecorc
    Commented Jul 16, 2015 at 2:10
  • Not sure how to make a variable number of multiple tables, but you would at leat need a new name for each and so on. The join tables are standard and if you have a good index on the fields, it's performance wise the same, because you only access the index of the collection you are interested in.
    – guest
    Commented Jul 16, 2015 at 2:15
  • That's at least the idea of relational database, you have the items, the collections and relations between them. In a document oriented database like MongoDB you might be tempted to pack the list as one field "items" in one table, but there you will run in problems and it is better to use the same relational approach and create the hierarchical structure on the fly.
    – guest
    Commented Jul 16, 2015 at 2:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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