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(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