DNA sequence data - requirement for a long and wide table of experimental data I need to build a database to hold data obtained from DNA sequencing experiments.

1) 5 million columns of data in one sample, and hundreds of samples in total

2) A few data points are null

3) Calculate the median, standard deviation etc. of each column every week

What would be an optimal design for this data and help to shorten the time consumed in analysis?

Any suggestion(s) would be appreciated.



Data type: DNA sequencing data. Break the whole chromosome into 1kb windows and calculate the mean depths of sequencing etc.

Columns: bed1, bed2...bed10000...

Rows: sample1, sample2...



Even if the focus is only on genes, there would be several thousand columns.

I wonder if there is any tool or solution to hand.
Is what you suggest a standard method of storing DNA sequencing results? Apart from the technical difficulties - good luck finding a database that will allow that many columns (SciDB?) - it will make querying tricky. I would look into ways of having your tables be tall and skinny rather than short and fat.

I would do something like the following: 

CREATE TABLE sample
(
  bed INTEGER NOT NULL,
  sample_no INTEGER NOT NULL,
  ..
  .. other fields? 
  .. chromosomal location? sequence? phenotype? 
  .. dna_type (gene | promoter | mRNA | tRNA... &amp;c.)?
  ..
  CONSTRAINT sample_pk PRIMARY KEY (bed, sample_no)
);


I believe that such a schema will facilitate querying and be far more technically feasible - it will conform far better to the relational model and will ultimately save you a lot of grief! I asked above Is this a standard method of storing DNA sequencing results? - this work has been done before so why are you reinventing the wheel? 

Don't get me wrong - I'm all in favour of experimenting and trying to get a new perspective on biological phenomena, that's what science/molecular biology is all about, but others must have done this before - you may want to look at their work? For the record, I have a Masters in Cellular and Molecular Genetics from the Univeristé de Paris. HTH...