Re: Storing number '001' ?

Chuck,

> Thanks.  I will look into the books, any specific recomendation?  I
> am learning on the fly as they say.  I am a molecular biologist who
> now needs to learn Perl and DBA related....

Bleah!  Next they'll be asking me to learn molecular biology.
As for books, I'm responsible for the book page at
http://techdocs.postgresql.org/bokreviews.php .   Anything I recommend
is already there.  You might want to start (and end) with Database
Design for Mere Mortals, which is like a "for Dummies(tm)" book, but
with accurate information.

Since you're at a university, in theory you could get a CS student to
help you as a work-study project.  However, I have yet to meet the CS
major who was a good DBA; a real understanding of database integrity and
design seems to only come from real-world experience.  So you may be
better off doing the DBA stuff yourself.    What about getting a CS
student to help with the Perl and HTML, though?

> If interested, my working version of the database is at:
<address snipped in case you didn't want to post it to the world>
>
> If you enter a term such as 'kinase', you will find all examples of
> such in the DB and click through the various links for more detail.

Cool!  I have a friend at Berkeley MCB.  Can I show this to him?

> Close, Perl. Most of the genomics work is written in Perl, because it
> is quite powerful when it come to handling text (GCACTAGCAGGCGA, DNA
> sequence).

Yeah, I'd agree there.  It's hard to beat Perl for text parsing.  Also,
when you get more advanced, Perl::DBI supports full middleware
functionality.  Perl's got a steep learning curve, though.

> I wholeheartedly agree that doing joins w/6 columns is unmanageable.
> I had thought about using a SERIAL column in each TABLE, but did not
> see how to use them in joins.  My understanding is that the number
> generated is specific for each table row.  So, if I want to join 2
> tables(clone_fasta and clone_qual) and find the 'seq' and 'qual'
> values for clone '894001A01.x1', how can one use the SERIAL clone_id
> in the join?
>
> >
> >>  CREATE TABLE clone_fasta(
> >clone_id SERIAL NOT NULL, 'xxx'
> >>  project INTEGER NOT NULL, 894
> >  > plate INTEGER NOT NULL,    1
> >  > p_row CHAR(1) NOT NULL, A
> >  > p_column INTEGER NOT NULL, 1
> >  > read CHAR(1) NOT NULL, x
> >>  ver INTEGER NOT NULL, 1
> >>  length INTEGER NOT NULL, 373
> >>  seq TEXT NOT NULL,  GAGCTAGXCAGGATC...
> >>  PRIMARY KEY (project,plate,p_row,p_column,read,ver),
> >CONSTRAINT clone_id_cs UNIQUE (clone_id)
> >  > );
>
> >
> >  > CREATE TABLE clone_qual(
> >clone_id SERIAL NOT NULL, 'yyy'
> >>  project INTEGER NOT NULL, 894
> >  > plate INTEGER NOT NULL,   1
> >  > p_row CHAR(1) NOT NULL, A
> >  > p_column INTEGER NOT NULL, 1
> >  > read CHAR(1) NOT NULL, x
> >  > ver INTEGER NOT NULL, 1
> >  > qual INTEGER[] NOT NULL, {10,1,12,...}
> >  > PRIMARY KEY (project,plate,p_row,p_column,read,ver),
> >CONSTRAINT clone_id_cs UNIQUE (clone_id)
> >  > );
>
>
> SELECT clone_fasta.seq,clone_qual.qual
> WHERE clone_fasta.clone_id = clone_qual.clone_id   <-- These are
> never the same, correct?
> AND .....?

Ah!  I understand the confusion.  Before I try to sort this one out, can
you explain to me the relationship between the four tables?  What kind
of information is stored in clone-fasta?  What kind of data is stored in
clone_qual, and what is the relationship between the two tables?  What
about the other tables?

And I'll want to post our whole interaction to the pgsql-novice list as
education for the masses.  With your permission, I might compile our
e-mails as a lesson for techdocs.

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco