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(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco