I'm trying to design a database for something I've not done before. I am having users upload a cvs file in my webapp which I'm storing in a MySql database (and retaining the user's id to the CVS file). The problem is that the csv file columns can vary. The only way I can think of to do this is to design a table in the db to look like this
* note: cvsLineNumber is an integer column there to preserve the order of the rows in the CVS file
There are two serious problems with this design:
First, I don't know how many columns a user can enter(unknown number of columns in the CVS file uploaded). I guess I can limit the user to a number of columns by creating X number of columns but not sure if I really want to limit the user.
Second, my major concern, is that once users start to upload a bunch of CSV files, my table will reach it's filesize limit very fast. This is because every users CVS file will be stored in one table. I'm using mysql but anticipate that this table is going to grow fast as it represents the primary function for this application. Additionally this table is frequently going to be queried, and I'm not sure how this approach will work.
I'm not constrained to using one table but don't know any better. I am sure google faces this problem with Google Spreadsheets too. They have all these users who create spreadsheets which I would think they store a db. How to they store all the spreadsheet data without knowing the column size or running out of room on one table(assuming they use one table for all spreadsheets).
Any ideas how this should work? I am guessing I'm going to have to break table2 apart but not sure how that should be designed.