DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	b=qVKskJYU9IPnKj2F0S8wv2BROvK+AdPPeeeitIrIk90S3K1o6lqCXiiS6ge+L6bjbullSAr0op6/rXTqBV8IrcTHAlu2+FPJEMjE/RylahsKcEPidKeQh2Hm/Cja5AZUqdhPD07FfBbfSDUPgbrn1UfipRjO+dbEGhrxrDJAyzQ=
Message-ID: <2049e51a0706140421u1ef40ab7m7281ca91457396df@mail.gmail.com>
Date: Thu, 14 Jun 2007 12:21:06 +0100
From: "Salman Tahir" <salmantahir1@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Organization of tables
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hi,

I have a question regarding the organization of a table I want to
create in my database: I have the following setup:

Table Fragments (simplified example):
Primary key = {mass}

Approach (1)
fragment   |   mass (of fragment)   |   peptide
---------------+--------------------------------+--------------
A             |      x                          | Peptide1, Peptide2
Q             |      y                          | Peptide1
K             |      z                          | Peptide 2, Peptide3

The idea here is that a peptide can be composed of many fragments e.g.
Peptide 2 is made up of fragments A and K; Peptide1 is made up of A
and Q and so on.

My idea is to create an index on the mass column and be able to
retrieve all Peptides that contain a certain fragment mass e.g

SELECT peptide
FROM Fragments
WHERE mass = x;

Should give me: Peptide1, Peptide2

The alternative way I have thought of to organize this table is to
have something as follows:
Approach (2)
Primary Key = {fragment, mass, peptide}
fragment   |   mass (of fragment)   |   peptide
---------------+--------------------------------+--------------
A             |      x                          | Peptide1
A             |      x                          | Peptide2
Q             |      y                          | Peptide1
K             |      z                          | Peptide 2
K             |      z                          | Peptide 3

If I consider 2500 unique fragments then, using approach (1), table
Fragments will hold 2,500 tuples. If I consider the same number of
fragments then table Fragments using approach 2 holds 15,000 tuples.

I have considered using approach (1) whereby I would have less tuples
to search but if I wanted to access the peptides they belong to I
would retrieve the list of corresponding peptides e.g the string
"Peptide1, Peptide2" and process it in my program. However this seems
like a hack around the way a database table should be organised. The
problem increases further when I have to scale up and consider more
unique fragments (>2500).

Any help on how best to structure such data would be mostly appreciated.

- Salman Tahir