[BioSQL-l] Tables without a (composite) primary key

[mark.schreiber at novartis.com]

Hi -

In the Java JPA it is possible to use an embedded object as a primary key. 
This gets you around the situations where the primary key is composite. It 
also effectively gets you around those tables where there is no key but it 
is implicit as all the fields are unique (as in taxon_name).

What you end up with is an object that holds taxon_id, name, name_class, 
and inside that object you have an embedded key object that contains the 
same three fields. In this way any changes that are made to the object are 
still associated with the original row via the unchanged embedded PK 
object and are updated accordingly.

While I agree that an explicit PK's for all BioSQL tables would be nicer 
for ORM frameworks many frameworks have ways to get around this, possibly 
those in Ruby or Python do as well.

- Mark


biosql-l-bounces at lists.open-bio.org wrote on 11/06/2008 08:23:43 PM:

> Dear Peter, 
> I'm writing the wrapper for BioRuby using DataMapper an ORM (Active 
Record is 
> similar).
> 
> I think we can cosider to move or branch BioSQL' schema to the approach 
> suggested by this kind of ORMs, with a pk for every table named "id" and 
a 
> table name in plural. Fk names are quite correct.
> 
> PS: DataMapper handles very well composite PK, much better tha 
ActiveRecord.
> 
> Il giovedì 06 novembre 2008 12:53:13 Peter ha scritto:
> > I've recently been looking into some object-relational mappers which
> > caused me to look more closely at the BioSQL schema.  Many of these
> > packages require a primary key, but not all can cope with a composite
> > primary key.  However, some BioSQL tables don't have any primary key
> > at all.
> >
> > Several BioSQL tables have composite primary keys, for example the
> > term_dbxref table has a composite key of (term_id, dbxref_id), and
> > also an index on dbxref_id as well.
> >
> > However, some BioSQL tables do not have a primary key, for example:
> >
> > -- corresponds to the names table of the NCBI taxonomy databaase
> > CREATE TABLE taxon_name (
> >        taxon_id      INT(10) UNSIGNED NOT NULL,
> >        name      VARCHAR(255) BINARY NOT NULL,
> >        name_class   VARCHAR(32) BINARY NOT NULL,
> >        UNIQUE (taxon_id,name,name_class)
> > ) TYPE=INNODB;
> >
> > CREATE INDEX taxnametaxonid ON taxon_name(taxon_id);
> > CREATE INDEX taxnamename    ON taxon_name(name);
> >
> > Why don't taxon_name, bioentry_path, term_relationship,
> > bioentry_qualifier_value, seqfeature_path have a primary key (just a
> > uniqueness criteria)?
> >
> > Thanks,
> >
> > Peter
> > _______________________________________________
> > BioSQL-l mailing list
> > BioSQL-l at lists.open-bio.org
> > http://lists.open-bio.org/mailman/listinfo/biosql-l
> 
> 
> _______________________________________________
> BioSQL-l mailing list
> BioSQL-l at lists.open-bio.org
> http://lists.open-bio.org/mailman/listinfo/biosql-l

_________________________

CONFIDENTIALITY NOTICE

The information contained in this e-mail message is intended only for the 
exclusive use of the individual or entity named above and may contain 
information that is privileged, confidential or exempt from disclosure 
under applicable law. If the reader of this message is not the intended 
recipient, or the employee or agent responsible for delivery of the 
message to the intended recipient, you are hereby notified that any 
dissemination, distribution or copying of this communication is strictly 
prohibited. If you have received this communication in error, please 
notify the sender immediately by e-mail and delete the material from any 
computer.  Thank you.