How do I model data from arbitrary CSV's in a database?

Question

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?

Answer 1

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?

Answer 2

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

Answer 3

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.

Answer 4

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.