Re: Storing number '001' ?

Chuck,

> One last question before I leave you alone.
>
> I have test data in all 3 tables:
>
> clone <---> clone_contig <----> contig
>
>
>
> In the case where contig '20010822.123.1' was assembled from 2 clones
> (894001A01.x1, 963012H10.x1)
>
> TABLE clone TABLE clone_contig TABLE contig
> clone_id clone clone_id contig_id
> contig_id contig
> 167756 894001A01.x1 167756 37238238
> 37238238 20010822.123.1
> 21389 963012H10.x1 21389 37238238
>
>
> Now, if I want to do a query to recover the sequence of all clones
> which were used to assemble contig 20010822.123.1 is the relational
> table clone_contig invoked automatically?
>
> ie can I do a query:
>
> SELECT clone.seq FROM contig,clone WHERE contig='20010822.123.1';
>
> More generally, how does one make use of the relations set up in a
> relational table?

Well, those relations are really just complex constraints. They only
shorten your queries in systems that support the NATURAL JOIN properly;
however, this JOIN implementation is so unevenly supported in various
databases that it's a better idea not to use it. Thus, in your query:

SELECT clone.seq
FROM clone JOIN contig_clones USING (clone_id)
JOIN contig USING (contig_id)
WHERE contig.contig = '20010822.123.1'*

*= remember that you are actually storing the contig value in 3 fields,
so you really need to compare against the 3 fields.

The REFERENCES constraints you put in the table definition are
*constraints*, meaning that they restrict what can go into the table.
They do not help you join the two tables, except for maybe making query
execution a little faster. What they do do is make sure that every
single record in clone_contigs has corresponding records in clones and
contigs. That way, you don't have to worry about testing for "orphan
records" because there can't be any.

Finally, you need to index all joined fields for performance reasons.
Assuming that you already have Primary Keys on clones.clone_id,
contigs.contig_id, and (contig_clones.clone_id,
contig_clones.contig_id), then the only index you are missing is a
seperate index on contig_clones.contig_id (dual-column indexes are
seldom of much use on JOINing the second column).

CREATE INDEX contig_clones_contig_idx ON contig_clones (contig_id);

-Josh Berkus

______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