Re: Storing number '001' ?

Charles,

> Attached is my draft of a schema.

Hey, I like this!  What software did you use to generate it?  I
currently lack a diagramming tool that produces JPGs.

> I believe I was in error yesterday, when I stated that the
> relationship between the clone tables was one to one.  It is true
> that a clone has 1 and only 1 sequence, but a sequence exists for 1
> or more clones(TABLES clone_fasta and clone_qual).

See below.  This changes our schema.

> I am working my way thru Database Design for Mere Mortals, and
> another book, Relational Database Design Clearly Explained by J.L.
> Harrington.

I would appreciate a 1-paragraph review of the latter book for the
bookreviews page when you're done with it.

> In practical terms I don't understand how to deal with the FKs.  For
> example:
>
> CREATE TABLE clone_fasta(
> clone_fasta_id SERIAL PRIMARY KEY,
> clone_id INTEGER NOT NULL REFERENCES clone(clone_id) ON DELETE
> CASCADE,
> seq TEXT NOT NULL,
> length INTEGER NOT NULL
> );

The problem is that, according to your new diagram, our parent-child
relationship is backwards.  The clones table should contain FKs to
clone-fasta and clone-qual instead of what we have now.  If you can give
me a better description of where the fasta and qual information comes
from, and what order it's entered in, we can tweak the schema to match.

-Josh

P.S. List readers, I apologize for omitting the diagram but it's 150K.

______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