From: Rodrigo De León <rdeleonp@gmail.com>
To: pgsql-sql@postgresql.org, "Salman Tahir" <salmantahir1@gmail.com>
Subject: Re: Organization of tables
Date: Thu, 14 Jun 2007 10:22:08 -0500
Message-ID: <a55915760706140822w5bac639ax169abca1bcf6e7b9@mail.gmail.com> (raw)
In-Reply-To: <2049e51a0706140421u1ef40ab7m7281ca91457396df@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rdeleonp@gmail.com, salmantahir1@gmail.com
Subject: Re: Organization of tables
In-Reply-To: <a55915760706140822w5bac639ax169abca1bcf6e7b9@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox