DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=qdSvtCTlDgSgBP+P8Au0JwRa91+Y66V4Afot2hBPrvO7dvITZF/csDhhNf19AGWT/6SZesxZMEOdq6gmPqEMmvrXX+vY49u0hkA97etrawfx720FU1Ee5Ey8x9QM3qVCnd+PIhIsVY8U3zWqJ3XVX7LXXYU67tcB9tb3RB45pTg=
Message-ID: <a55915760706140822w5bac639ax169abca1bcf6e7b9@mail.gmail.com>
Date: Thu, 14 Jun 2007 10:22:08 -0500
From: "=?UTF-8?Q?Rodrigo_De_Le=C3=B3n?=" <rdeleonp@gmail.com>
To: pgsql-sql@postgresql.org, "Salman Tahir" <salmantahir1@gmail.com>
Subject: Re: Organization of tables
In-Reply-To: <2049e51a0706140421u1ef40ab7m7281ca91457396df@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <2049e51a0706140421u1ef40ab7m7281ca91457396df@mail.gmail.com>

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 2
 Q        | y    | Peptide 1
 K        | z    | Peptide 2,Peptide 3