Re: Storing number '001' ?

Chuck,

> I am considering one last relational table to relate contig::blast.
>
> These two tables are related as:
> a contig has 0,1 or more blast hits (0 only if contig not yet run
> thru blast)
> a blast hit exists for 0, 1 or more contigs (0 only when no data
> loaded)
>
> As I have it currently, TABLE blast contains the FK contig_id to
> track which contig the blast record refers to.

Yes, but it doesn't fit your statements above, which clearly imply a
many-to-many relationship. Drop the FK.
>
> Is it appropriate in this case to make a TABLE contig_blast, to me it
> appears so....?

This is correct. However, your SQL is wrong. See my corrections below.

> Now that I think about it, it may also be useful to make a table
> clone_blast. If one wanted to do a query for which blast results
> pertained to a given clone.
>
> clone --- (assembled into) ---> contig ---> blast result
> | |
> -------------------------------------------------

This, on the other hand, is unnecessary and even problematic. We
already have the table clone_contig. If we add the relationship table
contig_blast, then we can select all clones in a blast by selecting:

blast JOIN contig_blast JOIN contigs JOIN clone_contigs JOIN clones

Adding a seperate relationship table to maintain this information would
be redundant, and also troublesome because eventually clone_blast would
be bound to get out of synch with the relationship above.

> CREATE TABLE contig (
> contig_id SERIAL PRIMARY KEY,
> assembly_date date NOT NULL,
> contig_no integer NOT NULL,
> ver integer NOT NULL,
> length INTEGER NOT NULL,
> seq TEXT NOT NULL,
> UNIQUE (assembly_date,contig_no,ver)
> );
>
>
> CREATE TABLE blast (
> blast_id SERIAL PRIMARY KEY,
###> contig_id INTEGER REFERENCES contig(contig_id) ON DELETE
CASCADE,###
Delete this column. It does not fit the data model.
> score integer,
> homolog text
> );
>
> CREATE TABLE contig_blast (
> blast_id INTEGER REFERENCES blast(blast_id) ON DELETE CASCADE,
> contig_id INTEGER REFERENCES contig(contig_id) ON DELETE CASCADE,
> UNIQUE(clone_id,contig_id)
uh-uh: UNIQUE (blast_id, contig_id)
> );

> CREATE TABLE clone_blast (
> blast_id INTEGER REFERENCES blast(blast_id) ON DELETE CASCADE,
> clone_id INTEGER REFERENCES clone(clone_id) ON DELETE CASCADE,
> UNIQUE(clone_id,contig_id)
> );
Drop this table.

-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