Re: Advice for using integer arrays?

On 1/6/2015 2:19 PM, Jeff Janes wrote:
> On Tue, Jan 6, 2015 at 9:09 AM, Michael Heaney <mheaney@jcvi.org 
> <mailto:mheaney@jcvi.org>> wrote:
>
>     I'm fairly new to Postgres, and have a design issue for which an
>     array of integers might be a good solution.  But I'd like to hear
>     from the experts before proceeding down this path.
>
>     Essentially, I'm trying to model the relationship between a group
>     of biological samples and their genes. Each sample (there are ~10K
>     of them now, with more coming) will have about 30,000 genes.
>     Conversely, a particular gene may be present in almost all samples.
>
>     So I've created the following table to handle the many-to-many
>     relationship:
>
>      create table sample_gene (id serial, sample_id int, gene_id int);
>
>
> What is the value of having this table at all?  It doesn't seem to 
> contain anything informative, like an allele identifier, a resequence, 
> or a copy number variation.  If you are just trying to record the fact 
> that a gene was present in that sample, perhaps it would be better to 
> instead record the genes have been deleted, rather than the ones that 
> have not been deleted?  That would probably be a much smaller list.
>

I suppose there could be a gene table which would contain data about 
each gene_id.
But I'm an IT guy, not a biologist, and my sample_gene table doesn't 
actually
exist.  I'm more concerned with how to deal with many-to-many relationships
when each parent could have tens of thousands of children. Collapsing all
the children into an array for each parent looked intriguing - but maybe 
it's
not a good idea.  I just don't know, so I thought I'd ask you guys.



>      create table sample_gene_array (id serial, sample_id int, gene_id
>     int [] );
>
>     So now the table data looks like this:
>
>     sample_id    |    gene_id []
>     ---------------------------------------
>     1                 |      [1:30475]
>     2                 |      [1:29973]
>     etc.
>
>
> I'm not familiar with the square bracket and colon as a syntax for 
> expressing int arrays.  Are you taking liberties with the psql output, 
> or using a different client program?  Does that represent the range 
> from 1 to 30475, or the two values 1 and 30475?

Yes, it's shorthand for the full range of values from 1 to 30475. Wasn't 
sure how
to represent it, and almost went with (1,2,3...30475).  Apologies...


Michael Heaney
JCVI