[ensembl-dev] Core-Funcgen schema/keys?

[njohnson]

HI WIlliam

See below for comments.

Nathan Johnson

Ensembl Regulation
European Bioinformatics Institute (EMBL-EBI)
European Molecular Biology Laboratory
Wellcome Trust Genome Campus
Hinxton
Cambridge CB10 1SD
United Kingdom

http://www.ensembl.info/
http://twitter.com/#!/ensembl
https://www.facebook.com/Ensembl.org

> On 19 Feb 2015, at 16:29, William Michels <wjm1 at caa.columbia.edu> wrote:
> 
> Hello,
> 
> I've been searching for a schema that delineates key relationships between the top-level databases at Ensembl such as Core, FuncGen, etc. Unfortunately, I have been unable to find a schema graphic, png or otherwise. 

Schema documentation can be found here:

	http://www.ensembl.org/info/docs/api/funcgen/index.html
> 
> (I understand some Core tables are reflected in FuncGen. I furthermore understand I can do these queries using Biomart, but I was hoping to have/use standard MySQL queries as well).
> 

From the ‘Schema documentation’ link on that page you can see the core tables which we re-use and modify(core-like).


> For example, I can't find documentation on the Foreign key relationships between:
> 
> rattus_norvegicus_funcgen_78_5.xref.dbprimary_acc   <-->  rattus_norvegicus_core_78_5
> 
> 
> My guess for a key relationship in 'core'  is:  rattus_norvegicus_core_78_5.transcript.stable_id
> 


Correct,  the funcgen xrefs which are associated with the rattus_norvegicus_core_Transcript external_db record with db_release 78_5. The xref.dbprimary_acc in this case will be the transcript stable ID.

> 
> Thus to connect the two top-level databases this leads to a rather odd-looking WHERE clause:
> 
> "WHERE rattus_norvegicus_funcgen_78_5.xref.dbprimary_acc = rattus_norvegicus_core_78_5.transcript.stable_id"
> 
> 

You’re pretty much spot on there, that is the standard way to connect between two DBs on the same server, although you’re missing the join to external_db as described above.

Nath


> Maybe this is incredibly obvious, but sometimes *incredibly* obvious things sometimes turn out to be *incredibly* wrong.  So I thought I'd check with Ensembl.
> 
> Any help appreciated,
> 
> Thanks, 
> 
> Bill
> 
> William Michels, Ph.D.
> Assistant Research Biochemist, UCSF (former)