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