Organization of tables

From: "Salman Tahir" <salmantahir1(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Organization of tables
Date: 2007-06-14 11:21:06
Message-ID: 2049e51a0706140421u1ef40ab7m7281ca91457396df@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Pavel Stehule 2007-06-14 11:54:09 Re: [SQL] function to find difference between in days between two dates
Previous Message Martijn van Oosterhout 2007-06-14 11:14:03 Re: [SQL] function to find difference between in days between two dates