mardi 24 avril 2018

Optimal pattern to store dynamic tables from csv files in MySQL database

I want to import the table structure and the given data from uploaded CSV files into my database for later procession.

The files may vary in their number of columns and rows.

At the moment this is my approach using 3 tables:

  • csv_tables
    • id (PK)
    • name
  • csv_columns
    • id (PK)
    • csv_table_id (FK)
    • position
    • name
  • csv_datas
    • id (PK)
    • csv_column_id (FK)
    • row
    • value

It works ok, but I wonder if there is an better pattern known for this. I also do not like to iterate over all those DB entries to reconstruct the table and send it to my frontend wich expects the data in this format:

{
"table":
  {
    "name": "My table",
    "headings": ["col1", "col2"],
    "dataRows": 
      [
       {"col1": "my data 1", "col2": "my data 2"},
       {"col1": "my data 3", "col2": "my data 4"}, 
      ]
  }
}

Aucun commentaire:

Enregistrer un commentaire