Re: Organization of tables

On 6/14/07, Salman Tahir <salmantahir1@gmail.com> wrote:
> Any help on how best to structure such data would be mostly appreciated.

See: http://en.wikipedia.org/wiki/Database_normalization

*** Grossly oversimplified example follows ***

CREATE TABLE PEPTIDE( NAME TEXT PRIMARY KEY
);

CREATE TABLE FRAGMENT( NAME TEXT PRIMARY KEY , MASS TEXT
);

CREATE TABLE PEPTIDE_FRAGMENT( FRAGMENT TEXT NOT NULL REFERENCES FRAGMENT(NAME) , PEPTIDE TEXT NOT NULL REFERENCES
PEPTIDE(NAME)
);

INSERT INTO PEPTIDE
VALUES ('Peptide 1'),('Peptide 2'),('Peptide 3');

INSERT INTO FRAGMENT
VALUES ('A','x'),('Q','y'),('K','z');

INSERT INTO PEPTIDE_FRAGMENT
VALUES
('A','Peptide 1'),('A','Peptide 2'),('Q','Peptide 1')
,('K','Peptide 2'),('K','Peptide 3');

SELECT F.NAME AS FRAGMENT, F.MASS
, (SELECT ARRAY_TO_STRING(ARRAY(    SELECT PEPTIDE FROM PEPTIDE_FRAGMENT    WHERE FRAGMENT = F.NAME ORDER BY NAME  ),
','))AS PEPTIDE
 
FROM FRAGMENT F;
fragment | mass |       peptide
----------+------+---------------------A        | x    | Peptide 1,Peptide 2Q        | y    | Peptide 1K        | z
|Peptide 2,Peptide 3