Re: Organization of tables

On 6/14/07, Salman Tahir <salmantahir1(at)gmail(dot)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 2
Q | y | Peptide 1
K | z | Peptide 2,Peptide 3