I am setting up a web interface for data management. Users upload CSV or similarly structured files, and I want to store these in the database so they can do manipulations on them -- filtering, sorting, graphing, etc.

I do not know how to model this properly in a database. I have several ideas, but none seem to be the right way to do it.

  1. Make a new table for each uploaded CSV. This would mean each column could be appropriately typed (integers, strings, dates, etc.) and each record would trivially correspond to a line in the CSV file. This seems like a natural conceptualization of the problem -- but would performance become an issue if I had to make a new table for each uploaded file?

  2. Make a table where each record represents a dataset (CSV) and have other tables where data points have the id their dataset in their record. This would mean that all the data from a given dataset is spread across different tables and there would be lots of redundancy (since each data point would store the id of the dataset). However, it would mean tables would not have to be created per-dataset.

  3. Other variations on 2. Most of my other thoughts were variations on number 2 with various amounts of indirection.

My question is essentially "How do I model this properly?", that is, with the ability to scale reasonably.

Most of the data will be scientific, so how do I deal with many data sets of sizes varying from trivial (say, 10 columns and 100 rows) to massive (hundreds of columns and thousands/millions of rows)?

tl;dr: How do I model arbitrary data from an arbitrary number of well-formed CSVs in a database, and would a new table for each CSV perform acceptably?

  • 1
    You might be interested in this question. I was creating a DB for user-defined data, but all that data came in various formats such as CSV or Excel, so my situation was very similar to yours
    – Rachel
    Commented Feb 17, 2012 at 19:33
  • Thanks, this does seem to address a very similar problem. Do you expect many such tables to perform acceptably?
    – msolomon
    Commented Feb 19, 2012 at 7:03
  • How many CSVs are you going to handle? A few dozen tables is reasonable (but hardly worth building a system over), while a few hundred is getting tougher to manage. A few thousand? I wouldn't want to be stuck with that database. Commented Feb 21, 2012 at 1:19

4 Answers 4


The question is so wide open that it is hard to say much.

And why are you using a database at all? Excel does everything you've described so far.

Seriously though, avoid making an inner system in your database that can store any kind of data. You have one already, called a DATABASE. Make code that creates the tables you need. What's so bad about that? I say option 1.

You know, if you want infinite flexibility how about a square-mile white board?

  • Would creating an arbitrary number of tables (as in, a potentially large number of users each uploading a potentially large number of files that would each create a new table) scale better than the other options?
    – msolomon
    Commented Feb 19, 2012 at 7:02
  • Databases are designed to hold large amounts of information in containers called tables. The data you want to store is already tabular (table-like). For you to build an "inner system" that stores metadata about your table-like objects, so they can all be put in one table, is by definition going to perform worse because you're using the same underlying system, but reimplementing many of its features within it. Create each table in the database. Don't make your own "table-storage" system on top of the database. I built an EAV database once that I still maintain... painfully.
    – ErikE
    Commented Feb 19, 2012 at 9:00
  • Segregate your users each into his own schema...
    – ErikE
    Commented Feb 19, 2012 at 9:02

MySQL has a CSV engine that may help you.. I never successfully tested it however.

The CSV engine can treat comma-separated values (CSV) files as table, but it does not support indexes on them. This engine lets you copy files in and out of the database while the server is running. If you export a CSV file from a spreadsheet and save it in the MySQL server's data directory, the server can read it immediately. Similary, if you write data to a CSV table, an external program can read it right away. CSV tables are especially useful as a data interchange format and for certain kinds of logging.

I believe you need to create the table using the CSV engine and give it the same structure and name as the CSV file. Probably automate this process fairly easy.

Then you just copy over the users CSV to the storage directory of the server (essentially replacing the server created .csv file with the users, names must match exactly).

This does create a table for each CSV file, however

  • This appears to be the converse of what I'm looking for. I would like to store data from a CSV in an indexed database, but this seems to store data from a database in an unindexed CSV.
    – msolomon
    Commented Feb 19, 2012 at 6:58

My first thought is to store the CSV as BLOBs or using FILESTREAM. If any 'filtering' or 'graphing' needs to take place, aren't these client-side functions?

I can't imagine anything worse than creating a table per CSV file. A database with tens of thousands of tables is close to my idea of a nightmare.

If you must store the individual data points (as opposed to the entire CSV as a single object) then I'd either store it as well-formed XML, with each record having an associated schema for consistency, or as something akin to your option 2.

I agree with ErikE's answer that you are recreating something that exists, but more pertinently why do you need to put these in a database and what do you need to do with them? Each CSV would ideally be stored as a single table, but thousands of tables is just a headache.

  • I don't see why they would only be client-side functions--I would like the server to be able to pull select data out and shunt it off for graphing (say, on a graphing server). This wouldn't work if the CSVs were BLOBs. Would it be so terrible to have thousands of tables if each was named sanely (prefixes for example)?
    – msolomon
    Commented Feb 21, 2012 at 20:21
  • What's wrong with thousands of tables? The only real difference to the engine is a little bit of overhead in metadata (which metadata you'd have to store IN tables anyway if you didn't). The tables, their names, and their permissions should be managed totally in code and completely automated. I see no reason that a headache can't be avoided with good design.
    – ErikE
    Commented Feb 21, 2012 at 21:39
  • Thousands of tables that store similar types of data is like thousands of stored procs or functions that perform similar tasks - it's often better to make a handful of 'smarter' procs that can perform the work of many near-identifical procs. This is primarily a maintenance issue. I can see the argument for thousands of tables that are completely automated but to me it smells bad. Commented Feb 21, 2012 at 23:22

I would say it really depends on

  • how many files types (different datasets) you are going to process
  • how much you know about the data
  • how generic the further processing is

Say, if there are 1000 different kinds and they are processed in the same way. In general, I would load CSV in generic structure (EAV) and on top of that create specific tables. In this case you parse CSV in one place and probably go further closer to business by creating specific business tables with defined known data types.

If the performance is an issue - EAV Model (one column - one database row) might not simply work (e.g. 4 GB CSV) so you need to load directly into specific tables. If the development time is the most expensive of all and the files are tiny, then create something generic. Once you need more specific table - create additional layer of "normal" tables. Another option is to generate the code based on metadata.

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.