Intervention entity/field

[roll]

Background As a part of the #2 issue we have to discuss with @danfowler and the team some parts of the ideal data model. It's a subtask named "Analyze the data we have, in comparison to the ideal model".

Topic

Intervention - interposition or interference, drug, therapy etc

Cochrane Database have an entity Intervention with m2m relation with Trial entity. For example in one Trial can be used Health care team + care as usual and Anshen-jianpi-syrup (TCM).

Proposed solutions

• we can have entity Intervention with m2m relation with Trial (as in Cochrane database)
• we can have field Trial.intervention implemented as an array of strings

See also - https://github.com/okfn/opentrials-cochrane-schizophrenia-data/wiki

[pwalsh]

@roll @danfowler are interventions always kind of free text/snippets like that? In that case, I'd be inclined to have them as arrays on trial object. If they were more structured (like a "tag" or a "category"), then yeah, an m2m would be useful as it can be used to query by intervention in meaningful ways.

[roll]

@pwalsh Intervention and Outcome are free text. It looks like an array on Trial.

And Design/Method are more like tags. So m2m makes sense.

I think i've got what your want from the app side. Also we can index arrays anyway if app will be in need of some free text search on those array fields.

Related to #6 #8 #9

[pwalsh]

@roll yes. We'd want to do some search on those fields, and yes, I'm interested in "flattening objects" where reasonable with array and jsonb fields because:

1. SQL is great but we don't always need it, and Postgres is great because it goes beyond SQL with great support for these data types
2. It makes certain queries simpler
3. This is a prototype for what will eventually be huge amounts of mixed structured/unstructured data, so our goal is not a properly normalized SQL backend anyway, in the long term.

[roll]

@pwalsh, @danfowler So we're going to start with intervention as array of free texts on Trial entiry?

https://github.com/okfn/opentrials-cochrane-schizophrenia-data/blob/master/archive/clean/sql/schema.sql#L27

If it's correct we can close this issue.

[pwalsh]

For you and @danfowler to decide.

[roll]

@danfowler Application doesn't need to have a tag-like search on free text fields (Cochrane has 3000+ different interventions). So it looks like as an array not m2m as we've discussed with Paul yesterday. Please take a look.

[danfowler]

@roll @pwalsh yes, let's go with an array of free text for Intervention on Trial.