Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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 Feb 17 '12 at 19:33
    
Thanks, this does seem to address a very similar problem. Do you expect many such tables to perform acceptably? –  msolomon Feb 19 '12 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. –  Kirk Broadhurst Feb 21 '12 at 1:19
add comment

3 Answers 3

up vote 3 down vote accepted

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?

share|improve this answer
    
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 Feb 19 '12 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 Feb 19 '12 at 9:00
    
Segregate your users each into his own schema... –  ErikE Feb 19 '12 at 9:02
add comment

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

share|improve this answer
    
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 Feb 19 '12 at 6:58
add comment

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.

share|improve this answer
    
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 Feb 21 '12 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 Feb 21 '12 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. –  Kirk Broadhurst Feb 21 '12 at 23:22
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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