StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POStoring arrays of integers in database
text
Body
copied!<p>I am creating a database that will store 100.000 (and probably more in the future) users. While this obviously happens in a table with 1 row per user, every user can (and will) store hundreds of items. In programming language this would mean the user has 2 arrays (or one 2-dimensional array) of integers: a column for the itemid's and a column for the amounts.</p> <p>My instincts tell me to create a table to hold all these items, with rows like (userid, itemid, amount). However this would result in a huge table. 200.000 users with 250 items each... that's 50 million entries in one table. This, plus the fact that the table will undergo continuous and rapid change, frightens me. (How rapid? I estimate up to 100 modifications per second.)</p> <p>Typically there will be anywhere between 100 and 2000 users, all adding and removing items, and modifying amounts. These actions can and will happen in programming code. It would go as follows:</p> <ul> <li>User starts session, program loads all the users items from the database</li> <li>User modifies the item list</li> <li>Every few minutes, the changes are saved into the database</li> <li>When the user ends the session, it is also saved into the database</li> </ul> <p>It is worth noting that there is a maximum to the number of items a user can store.</p> <p>Are there any alternatives to using a separate table? Perhaps save the values in a formatted text string? Or is this one of the instances where using a MySQL database is actually a Bad Idea™?</p> <p>Thank you for your time and insights.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload