[ensembl-dev] Suggestions re: Ensembl core schema

[Youens-Clark, Ken]

Following up on my suggestion yesterday to add primary keys to those tables missing them and to change all INTEGER types to be the same, I attach herewith a diff to do just that.  Committing these changes should have no adverse effect on the schema or code as this is only adding functionality.  

Also, I found this in the "table.sql" file:

> CREATE TABLE mapping_set (
> 
>         mapping_set_id  INTEGER UNSIGNED NOT NULL,
>         schema_build    VARCHAR(20) NOT NULL,
> 
>         PRIMARY KEY(schema_build)
> 
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I believe that must be wrong -- the PK should be the "mapping_set_id," right?

Also, the "foreign_keys.sql" file has this:

> ALTER table intron_supporting_evidence ADD FOREIGN KEY (analysis_id) REFERENCES analysis(analysis_id);
> ALTER table intron_supporting_evidence ADD FOREIGN KEY (seq_region_id) REFERENCES seq_region(seq_region_id);


But the "intron_supporting_evidence" table doesn't have those fields:

> CREATE TABLE intron_supporting_evidence (
>   intron_supporting_evidence_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
>   previous_exon_id INTEGER UNSIGNED NOT NULL,
>   next_exon_id INTEGER UNSIGNED NOT NULL,
>   hit_name VARCHAR(100) NOT NULL,
>   score DECIMAL(10,3),
>   score_type ENUM('NONE', 'DEPTH') DEFAULT 'NONE',
>   
>   PRIMARY KEY (intron_supporting_evidence_id),
> 
>   UNIQUE KEY (previous_exon_id, next_exon_id)
> ) COLLATE=latin1_swedish_ci ENGINE=MyISAM;

I will also attach a cleaned up version of the entire "table.sql" that has everything consistently formatted as it might prove easier to work with.  Lastly, I will attach an InnoDB version that MySQL can actually use to create a version with referential integrity.  I'd like to ask at this point why InnoDB isn't used?

--
Ken Youens-Clark
kclark at cshl.edu
Ware Lab/CSHL/USDA-ARS

-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: fk-diff.txt
URL: <http://mail.ensembl.org/pipermail/dev_ensembl.org/attachments/20121003/23cc01c4/attachment.txt>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: table-innodb.sql
Type: application/octet-stream
Size: 55851 bytes
Desc: table-innodb.sql
URL: <http://mail.ensembl.org/pipermail/dev_ensembl.org/attachments/20121003/23cc01c4/attachment.obj>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: table.sql.format
Type: application/octet-stream
Size: 43443 bytes
Desc: table.sql.format
URL: <http://mail.ensembl.org/pipermail/dev_ensembl.org/attachments/20121003/23cc01c4/attachment-0001.obj>
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: table-diff.txt
URL: <http://mail.ensembl.org/pipermail/dev_ensembl.org/attachments/20121003/23cc01c4/attachment-0001.txt>