Changes to feature_stock, featuremap_stock, featureinterval and linkers

[spficklin]

This issue imported from the Chado v1.4 requested changes google doc:

Requests by Ethy Cannon / Naama Menda / Steve Cannon

http://gmod.827538.n3.nabble.com/Opening-up-comments-on-changes-to-Chado-tc4036098.html#a4039144

• feature_stock with a type_id is used to associate a QTL feature with the parent with the favorable allele. (v1.3)
• featuremap_stock with a type_id is used to associate a genetic map with a stock record describing its mapping population.
• Do other linker tables have types? If so, should we make this a standard for all linker tables to have a type, but let it be non-required? (v1.4, get some more use cases)

http://gmod.827538.n3.nabble.com/Proposed-changes-to-map-module-tc4031971.html

• Change featureloc.fmin and featureloc.fmax to a float. Something to do with featuremap but I don’t understand….
• Add a type field to the featurepos table to indicate the “type” of the position (e.g. start or end).
• Add a new ‘featureinterval’ table
  • with these fields:
  • featureinterval_id
  • featuremap_id (map set, to get coordinate units)
  • feature_id (object feature being placed)
  • srcfeature_id (target feature)
  • startpos (double precision)
  • endpos (double precision)
  • Stephen Ficklin (1/14/15): In the email it says this would help newbies but duplicates some information already in the featurepos table. Not sure if we should add tables that duplicate info. I got slapped around once for suggesting such a thing.
  • Scott Cain (1/14/15): As well you should! Perhaps featureinterval should be a view?

[spficklin]

Sample code from the Google Doc

CREATE TABLE featuremap_stock (
    featuremap_stock_id SERIAL NOT NULL,
      PRIMARY KEY (featuremap_stock_id),
    featuremap_id INT NOT NULL,
      FOREIGN KEY (featuremap_id) REFERENCES chado.featuremap (featuremap_id) ON DELETE CASCADE INITIALLY DEFERRED,
    stock_id INT NOT NULL,
      FOREIGN KEY (stock_id) REFERENCES chado.stock (stock_id)  ON DELETE CASCADE INITIALLY DEFERRED
);

CREATE TABLE feature_stock (
  feature_stock_id serial NOT NULL,
    PRIMARY KEY (feature_stock_id),
  feature_id INT NOT NULL,
    FOREIGN KEY (feature_id) REFERENCES chado.feature (feature_id) ON DELETE CASCADE INITIALLY DEFERRED,
  stock_id INT NOT NULL,
    FOREIGN KEY (stock_id) REFERENCES chado.stock (stock_id) ON DELETE CASCADE INITIALLY DEFERRED,
  type_id INT NOT NULL,
    FOREIGN KEY (type_id) REFERENCES chado.cvterm (cvterm_id) ON DELETE CASCADE INITIALLY DEFERRED,
  rank INT NOT NULL DEFAULT 0,
  CONSTRAINT feature_stock_c1 UNIQUE (feature_id, stock_id, type_id, rank)  
);
TODO: add comments, indexes

[ekcannon]

Update to the original request:

• feature_stock with a type_id is used to associate a QTL feature with the parent with the favorable allele. (v1.3) This working nicely at PeanutBase and LegumeInfo, but could more generically be handled with a feature_stockprop table.

• featuremap_stock with a type_id is used to associate a genetic map with a stock record describing its mapping population. Ditto

• Do other linker tables have types? If so, should we make this a standard for all linker tables to have a type, but let it be non-required? (v1.4, get some more use cases) Again, would create X_Xprop tables be better than adding type_ids?

• Change featureloc.fmin and featureloc.fmax to a float. I've become convinced that this is indeed a bad idea.

• Add a type field to the featurepos table to indicate the “type” of the position Already discussed in https://github.com/GMOD/Chado/issues/56

• Add a new ‘featureinterval’ table I think this table is not needed, particularly if featurepos gets a type_id. Still tricky to query for within-ranges, but a view could help.

[laceysanderson]

feature_stock with a type_id is used to associate a QTL feature with the parent with the favorable allele. (v1.3) This working nicely at PeanutBase and LegumeInfo, but could more generically be handled with a feature_stockprop table. I assume the feature_stock record is something like: feature_id => QTL feature, stock_id => parent with favourable allele, type_id => cvterm like "is parent with favorable allele"? If so, I like the solution as it is and think it's perfectly generic.

I like the idea of adding optional type_ids to all linker tables and consider this a beautiful, generic solution that allows more meta-data for what these relationships actually are. I don't feel X_Xprop tables would be better since it becomes more convoluted and is not as performant.