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
