A website I somehow got roped into building requires a database backend to function. The problem basically is that users are able to enter a variable amount of data in a form that has to be stored on the server. New information is expected to be updated fairly regularly.

The best way I can figure to store all of that data (the amount of data per user is unknown and could be large) is to give each user their own table in the database, then store the data in each table. However, there are multiple pieces of data that need to be recalled separately and it seems like that would get out of control fairly rapidly with any sizable number of users. Storing everything in one big table would conversely be slow because of the amount of information.

Does anyone have any recommendations on how to solve this? I'm fairly certain it's a well known problem in web design that I just don't have the experience to know the answer to an googling doesn't turn up much that's relevant.

For the record, I'm currently using mySQL to do the database.

I think the multiple tables is out, as you can't create database tables from the website, *I think.* If you need to add more info just make a new column, and each and every user will automatically get that column to store information on. If you make separate tables, you'd need to update each table. You'd save a lot of time by making it one table or a few. But having one table per user is absurd.

Cemetech is stored no more than on two to three mySQL Databases, perhaps one but I doubt that.

I'm being a bit doubtful on my advice, so take it as you will until educated advice comes along.

Multiple tables in the quantity of one per user are not the way to go. Let me get a better idea of the requirements for this, because it should almost definitely be in one table, or perhaps a small handful of tables. (1) Is the adding of information discretized? As in, to users add sizeable chunks at one time, then come back at a later time and add more? Or continually edit one huge chunk? (2) How much and how many different types of data does each entry involve, if it's discretized? Is it predefined and static? If not, are there any guidelines?

The amount that's entered at any one time is discrete. However, there are multiple types of data including text, timestamps, and numbers. The text and numbers can be maintained separately with no loss if it's more efficient, though.

Well, tables are of course designed to have lots and lots of columns, so if you always have the same assortment of numbers, timestamps, and a variable-sized chunk of text per item, then you're golden. You'd probably at least want one table with user_id | item_id | text | timestamp | other stuff as the columns, then a second table with something like user_id | username | password_hash | joined_date | preference1 | etc | etc.

phpBB only uses one database. Unless Kerm has added a second database for something, cemetech runs off of a single mysql db.

@Qwerty: One table per user is a bad design. Ignore "scalability" for a minute and focus on a good, clean design. That almost always leads to a scalable design as well. Multiple tables vs. one giant table is irrelevant in terms of scalability, neither scales better than the other. Actually, tables with tons and tons of columns is what doesn't scale - don't do that (which again goes back to clean design typically leads to a scalable one )

Once you have your design, you can always add memcache to help iron out the performance end of things.

Kllrnohj, the out-of-band discussion in IRC revealed that Qwerty was a little vague on rows and columns, and didn't think that having a lot of rows in tables scaled. He of course realized his error with a little prodding: if that was the case, then MySQL (or most DBMSes, for that matter) would be more or less useless, as their whole purported point is to store large quantities of data in a structured fashion for fast lookup and insertion.