From: operationsengineer1@yahoo.com
To: Salman Tahir <salmantahir1@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Organization of tables
Date: Fri, 15 Jun 2007 09:42:56 -0700 (PDT)
Message-ID: <186195.35881.qm@web33309.mail.mud.yahoo.com> (raw)
Hi Salman,
will this achieve your needs?
peptide_table
primary_key
name (ie, Peptide1,
Peptide2)
mass
fragment_table
primary_key
name (ie, A, Q,
K)
link_table
primary_key
peptide_id
fragment_id
it gives you easy access to peptide mass and it allows you to link multiple fragments to individual peptides.
if the peptide mass ultimately comes form the fragments, you could enter the mass value there and then have the db do a calculation to sum up the masses for a given peptide.
sorry if i've misunderstood you problem.
best of luck,
oe1
----- Original Message ----
From: Salman Tahir <salmantahir1@gmail.com>
To: pgsql-sql@postgresql.org
Sent: Thursday, June 14, 2007 4:21:06 AM
Subject: [SQL] Organization of tables
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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
____________________________________________________________________________________
TV dinner still cooling?
Check out "Tonight's Picks" on Yahoo! TV.
http://tv.yahoo.com/
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: operationsengineer1@yahoo.com, salmantahir1@gmail.com
Subject: Re: Organization of tables
In-Reply-To: <186195.35881.qm@web33309.mail.mud.yahoo.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