Ontological term broker/DB Schema

CREATE TABLE ONTOLOGYS (  ontology_id      int not null primary key generated by default as identity,  name             varchar(200) not null unique  );

CREATE TABLE USERS ( user_id         int not null primary key generated by default as identity,  user_name       varchar(100) not null unique  );

CREATE TABLE REQUESTS ( request_id       int not null primary key generated by default as identity,  ontology_term    varchar(100),  search_text      varchar(1000) not null,  context          clob,  user_id          int not null references USERS(user_id) ON DELETE CASCADE,  provenance       varchar(1000) not null,  date_submitted   varchar(100) not null,  status    int not null,  ontology_id      int not null references ONTOLOGYS(ontology_id) ON DELETE CASCADE  );

CREATE TABLE METADATAS ( metadata_id      int not null primary key generated by default as identity,  request_id       int not null references REQUESTS(request_id) ON DELETE CASCADE,  metadata_key     varchar(100) not null,  metadata_value   varchar(1000) not null,  created_on       date not null,  created_by       int not null references USERS(user_id) ON DELETE CASCADE  );

CREATE TABLE STATUSUPDATES ( request_id       int not null references REQUESTS(request_id) ON DELETE CASCADE,  updated_on       date not null,  updated_by       int not null references USERS(user_id) ON DELETE CASCADE,  old_status       int not null,  new_status       int not null,  comment          clob  );