Re: Non-Unique intems - Mailing list pgsql-novice

Srinivas Iyyer wrote:
> Hi Sean and group,
>  thank you for your help. It worked.
> However, I guess I stepped on a land mine of unique
> and non-unique items.
>
> Here is the problem:
> (Example data) I have table A:
>
> seq_id   seq_name
> 123        ITAM3
> 234        ITAR
>
>
> Table B:
>
> spot_id  seq_id   image_name
> --------------------------------------------
> 849343    123       IMAGE: 12335
> 1348238   234       IMAGE: 12335
>
>
>
>
> Table C:
>
> exp_id |  spot_id  | spot_value
> -------|-----------|-----------
>
> Data to insert into Table C
> IMAGE: 12335   98.03344
>
>
>
>
>
>
> Here the log of query:
>
> arraydb=# SELECT spotanno_id from spotanno
> arraydb-# where spotanno_imageid = 'IMAGE:755402';
>  spotanno_id
> -------------
>       849343
>      1348238
> (2 rows)
>
> arraydb=# select * from spotanno where spotanno_id =
> 849343;
>  spotanno_id | seq_id | spotanno_imageid
> -------------+--------+------------------
>       849343 |  75343 | IMAGE:755402
> (1 row)
>
> arraydb=# select * from spotanno where spotanno_id =
> 1348238;
>  spotanno_id | seq_id | spotanno_imageid
> -------------+--------+------------------
>      1348238 |  50475 | IMAGE:755402
> (1 row)
>
> arraydb=# select * from seqdump where seq_id = 50475;
>  seq_id |  seq_acc  | seq_name
> --------+-----------+----------
>   50475 | NM_005501 | ITGA3
> (1 row)
>
> arraydb=# select * from seqdump where seq_id = 75343;
>  seq_id |  seq_acc  | seq_name
> --------+-----------+----------
>   75343 | NM_002204 | ITGA3
> (1 row)
>
>
> An instance of row of the data file that to be
> uploaded:
>
>
> IMAGE:755402    0.299781845119261
> 12.3638881597060
>
>
>
> The question:
> when I have a non-unique item (viz. IMAGE:755402 )
> what is the approach one should generally take.
>
> Do you have any suggestions/solution. Please help me.
>
> Thanks again.
>
> -sri

Sri,

Unfortunately, the biological data that you are working with has
one-to-many and many-to-many relationships.  While one would like to
believe that there should not be such relationships, there are.
Therefore, you need to store the data in a manner that respects those
manifold relationships.  In other words, store the data in a table with
whatever is the primary key (in this case, it looks like an IMAGE ID)
and store the annotation separately, allowing for a one-to-many
relationship between IMAGE ID and gene.  There is no way around this and
to try to eliminate these "non-unique" situations in this particular
case won't be possible; instead, you have to understand where the data
are coming from and design your database to match, not the other way
around.

Sean