On 6/14/07, Salman Tahir <[EMAIL PROTECTED]> 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 2 Q | y | Peptide 1 K | z | Peptide 2,Peptide 3 ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
