create table model (
    model_id number
  , model_attribute_1 number
  -- ...
  , model_attribute_n number
  , constraint pk_model primary key (model_id)
);

create table method (
    method_id number
  , method_attribute_1 number
  -- ...
  , method_attribute_n number
  , constraint pk_method primary key (method_id)
);

create table global_method (
    method_id number
  , db_id number not null
  , method_ac varchar2(15) not null
  , model_id number not null
  , mm_id number not null
  , short_name varchar2(1000)
  , long_name varchar2(4000)
);

create unique index pk_global_method on global_method (method_id);

create unique index ix_global_method_01 on global_method (db_id, method_ac);

alter table global_method add constraint pk_global_method primary key (method_id)
using index pk_global_method;

alter table global_method add constraint uc_global_method_01 unique (db_id, method_ac)
using index ix_global_method_01;

create table hmm_model (
    model_id number
  , model_type varchar2(30) not null
  , hmm varchar2(64)
  , hmmer varchar2(20)
  , mu float(126)
  , lambda float(126)
  , status char(1) not null
);

create unique index pk_hmm_model on hmm_model (model_id);

alter table hmm_model add constraint pk_hmm_model primary key (model_id)
using index pk_hmm_model;


drop table method_model;
create table method_model (
    mm_id number
  , model_id number not null
  , method_id number not null
);

create unique index pk_method_model on method_model (mm_id)
tablespace dev_idx;

create unique index ix_method_model_01 on method_model (model_id, method_id)
tablespace dev_idx;

alter table method_model add constraint pk_method_model primary key (mm_id)
using index pk_method_model;

alter table method_model add constraint cu_method_model_01 unique (model_id, method_id)
using index ix_method_model_01;

alter table global_method add constraint fk_global_method_01 foreign key (db_id)
references dbversion (db_id);

alter table method_model add constraint fk_method_model_01 foreign key (model_id)
references hmm_model (model_id);

alter table method_model add constraint fk_method_model_02 foreign key (method_id)
references global_method (method_id);

alter table global_method add constraint fk_global_method_02 foreign key (mm_id)
references method_model (mm_id) deferrable initially deferred;

METHOD >0---|< MODEL

METHOD ||---|< MODELMETHOD >0---|| MODEL