Proposal: *cvprop and *dbxprop tables

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Proposal: *cvprop and *dbxprop tables

Mara Kim-2
Hello gmod-ers,

The current *prop tables allow for tag-value pairs of an object (eg. organism), to have a free text value of a type given by a cvterm.  However, in some cases, these values should be controlled vocabulary terms themselves.

For example, lets say we have a vocabulary that lets us describe the test and control of an expression analysis.  Instead of adding an analysisprop that has the free text value "coronary artery", one could define the control as UBERON:0001621, which lets you hit the result when someone searches for "heart".  In addition, if you wanted to add the GEO dbxrefs of the control samples as properties, you could associate those accessions while keeping it clear that these were auxiliary accessions associated with the analysis.  (On a side note, there is no analysis_dbxref table in vanilla Chado).

Here are examples for the organismcvprop and organismdbxprop tables, adapted from the SQL for the organismprop table:

-- ================================================
-- TABLE: organismcvprop
-- ================================================

create table organismcvprop (
    organismcvprop_id serial not null,
    primary key (organismcvprop_id),
    organism_id int not null,
    foreign key (organism_id) references organism (organism_id) on delete cascade INITIALLY DEFERRED,
    type_id int not null,
    foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
    value int null,
    foreign key (value) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
    rank int not null default 0,
    constraint organismcvprop_c1 unique (organism_id,type_id,rank)
);
create index organismcvprop_idx1 on organismcvprop (organism_id);
create index organismcvprop_idx2 on organismcvprop (type_id);

COMMENT ON TABLE organismcvprop IS 'Tag-value properties, where values are cvterms - follows standard chado model.';

-- ================================================
-- TABLE: organismdbxprop
-- ================================================

create table organismdbxprop (
    organismdbxprop_id serial not null,
    primary key (organismdbxprop_id),
    organism_id int not null,
    foreign key (organism_id) references organism (organism_id) on delete cascade INITIALLY DEFERRED,
    type_id int not null,
    foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
    value int null,
    foreign key (value) references dbxref (dbxref_id) on delete cascade INITIALLY DEFERRED,
    rank int not null default 0,
    constraint organismdbxprop_c1 unique (organism_id,type_id,rank)
);
create index organismdbxprop_idx1 on organismdbxprop (organism_id);
create index organismdbxprop_idx2 on organismdbxprop (type_id);

COMMENT ON TABLE organismdbxprop IS 'Tag-value properties, where values are dbxrefs - follows standard chado model.';


PS. these might need to be bigserial and bigint

--
Mara Kim

Biological Sciences
Vanderbilt University
Nashville, TN

------------------------------------------------------------------------------

_______________________________________________
Gmod-schema mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/gmod-schema