How to store tabular data

Question

I need your help for a functionnal question about data storage.

I'm on a project where the user can save a sort of excel table on the server. The size of the tables isn't fixed.

What is the best solution to store the tables ? As an array stored as a text in my MySql database ? Using MongoDB or other NoSQL solution ?

For information, no search feature will be implemented on the tables.

Thank you a lot!

Edit: I add two examples of data table:

Example 1: sales per quarter per salesman

            Sven    Kenneth
2013.Q1     1200    4500
2013.Q2     1350    900
2013.Q3     2487    784
2013.Q4     4570    4512
2014.Q1     4785    3451

Example 2: weight of a couple and their children per months

       Him    Her    Children
Jan    70.5   52.1   45.1
Feb    71.8   52.7   46.1
Mar    70.8   52.7   46.0
Apr    70.1   51.9   46.1

The number of rows and columns of these two example isn't the same and can vary in time: next quarter, the company can hire a new salesman.

How to store these data? Note that never the user will search on these tabels.

I hope this is clearer.

Can you provide more information about the "sort of excel table"? Is it the same table for all users or are they custom. How much data will be in each table? What do you need to with the data? — Martin, Apr 28 '14 at 11:52
Thank your for your time. Each user can create several new tabels. These tabels are typically data tabels as you can create with excel. About data size, probably no more than hundreds rows and ten columns, approximatively. I use the data to generate charts. — DescampsAu, Apr 28 '14 at 11:59
This is too broad. You should post some sample data and narrow the problem. — joao, Apr 28 '14 at 12:35
Just imagine the sales results of a company per month and per salesman. Or imagine the evolution of the weight of you and your wife per weeks on three years. Etc. Every table which could be used as a datasource for charts. I need to store these tables. Thank you. — DescampsAu, Apr 28 '14 at 12:42

score 2 · Accepted Answer · answered Apr 30 '14 at 13:39

In order to solve

The number of rows and columns of these two example isn't the same and can vary in time: next quarter, the company can hire a new salesman.

back in the old days where we use relational tables we would decompose it in two tables

period | salesman | sales
2013.Q1  Sven       1200    
2013.Q2  Sven       1350    
2013.Q3  Sven       2487    
2013.Q4  Sven       4570    
2014.Q1  Sven       4785   
2013.Q1  Kenneth    4500
2013.Q2  Kenneth    900
2013.Q3  Kenneth    784
2013.Q4  Kenneth    4512
2014.Q1  Kenneth    3451

salesman   | id
Sven L       Sven
Kenneth W    Kenneth

this is not normalized up to 5th normal form but it would do.

you could use Cassandra or Mongo, Cassandra you could use super column families that allows you to create columns on the fly and MongoDb it isn't a rigid structure you are saving only json objects you could be saving only quarters like so:

{
"quarter" : "2013.Q1", 
"salespeople" : [
    {"salesman": "Sven", "sales" : 1200},
    {"salesman": "Kenneth", "sales" : 4500}
 ]
}

and next year

{
"quarter" : "2014.Q1", 
"salespeople" : [
    {"salesman": "Sven", "sales" : 1200},
    {"salesman": "Kenneth", "sales" : 4500},
    {"salesman": "Somebodyelse", "sales" : 100000}
 ]
}

you can do both ways but relational way is so much easier

Thank you! The relationnal model will work but maybe a user will create a table to show sales when another will create table to show weight, etc. I had the idea to use MongoDb but I wasn't sure it was relevant. Thank you for confirming my idea. Extra question: what do you mean by saying: "this is not normalized up to 5th normal form but it would do." — DescampsAu, Apr 30 '14 at 18:27
in the first table next to sales you could add a new column weigh. So in databases exists database normalization, you can read more at https://en.wikipedia.org/wiki/Database_normalization — Luis Ramirez-Monterosa, Apr 30 '14 at 19:38

score 0 · Answer 2 · answered Apr 29 '14 at 19:08

0

I didn't find an explicit answer but I will explain what I'll do.

I will simply store the result as a bidimensional array.

answered Apr 29 '14 at 19:08

DescampsAu

1,056
2
10
29

How to store tabular data

2 Answers2