Re: Storing number '001' ? - Mailing list pgsql-novice

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