[GUSDEV] how to handle relationships between external database identifiers and ec numbers

[Jian Lu <jl...@vb...>]

Hi Folks,

We have such a situation that when running our annotation pipeline, we 
need to know the relationships between external database identifiers and 
EC numbers. Here is an example of TIGRAFAM info data: TIGR02496.INFO
ID  NrdJ
AC  TIGR02496
DE  ribonucleoside diphosphate reductase, B12-dependent
AU  Selengut J
TC  855.00 855.00
NC  120.00 120.00
AL  clustalw
IT  equivalog
EN  ribonucleoside diphosphate reductase, B12-dependent
GS  nrdJ
EC  1.17.4.1
TP  TIGRFAMs

We have EC numbers loaded into SRes.EnzymeClass and TIGRFAM ids into 
SRes.ExternalDatabaseEntry, but we couldn't find a place within GUS 
schema to store the relationship of them. There is a table 
DoTS.AASequenceEnzymeClass that could be linked to EC numbers, but it 
requires aa_sequence_id which is not what we need. So we created a table 
called DoTS.ECAssociation to store any id with relation to EC numbers. 
This is very similar to gene ontology association DoTS.GOAssociation. 
Any comment?

create table DoTS.ECAssociation (
    ec_association_id        number(10) not null,
    table_id                number(10) not null,
    row_id                    number(10) not null,
    enzyme_class_id            number(12) not null,
    review_status_id        number(12),
    ec_association_date        date,
    modification_date    date        not null,
    user_read        number(1)    not null,
    user_write        number(1)    not null,
    group_read        number(1)    not null,
    group_write        number(1)    not null,
    other_read        number(1)    not null,
    other_write        number(1)    not null,
    row_user_id        number(12)    not null,
    row_group_id        number(4)    not null,
    row_project_id        number(4)    not null,
    row_alg_invocation_id    number(12)    not null
);