-
Notifications
You must be signed in to change notification settings - Fork 446
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Fact_relationship changes #230
Comments
Proposal 2: relationship are generally valid for a certain interval of time, e.g. start_date and end_date. We propose adding two optional fields relationship_start_date_time and relationship_end_date_time to fact_relationship table. Examples of relationships:
|
There are some fact_relationship like tables in OMOP CDM, that may be subsumed by this proposal. e.g. Location_history. Location_history may be replaced by this new fact_relationship, because location_history is similar to fact_relationship table. See below mapping between location_history to fact_relationship.
|
Like it. If I knew how I'd put this thumb up emoticon in. But we need a default if the relationship is eternal. Something like in the vocabs: 1-Jan-1970 to 31-Dec-2099 |
should datetime's be optional or required? if required, i agree with default. Alternatively, we can keep them optional and assume during analytic time that if the datetimes are null then the value for the interval is from earliest observation_period_start_date to latest observation_period_end_date (computed during analysis time - similar to how we build cohorts in Atlas, where observation_period_end_date is the default cohort_end_date). |
Proposal 3: when we are making changes, why not add _type_concept_id
Example:
|
Although the proposal 2 itself sounds good and allows to remove |
It makes sense and its a clean design. To me its a trade off between neat and compact vs. efficient and segregated. I think there's potential for this table becoming massive but it seems like a universal means to get at these one-to-many issues that need to be addressed (e.g. multiple care sites for a provider, multiple providers for a patient, etc). |
I would propose to treat
So, once a POC has been validated and common use-case was figured out, a person can propose to create permanent tables so that we:
So, following the idea, |
@pavgra: Point taken. The FACT_RELATIONSHIP_TABLE should really be the last resort and contain the relationships for which creating a correctly modeled concrete RDBMS solution with FKs is just not worth it, because we will need it like 21 times for the 1.9B patients. All others should be modeled properly. |
@cgreich @pavgra like everyone else, what i like about the fact_relationship table is that it is flexible. what i dont like is that it is flexible. Flexibility allows us to use one structure for an infinite number of use cases, but these uses are hard to solve in a fact_relationship table (see points that @pavgra made). Also, I like fact_realtionship table because ETL can be done in a generic way - and we dont have to revise our ETL every time for POC'ing a new use cases. I like @pavgra comment of using fact_relationship table as the source for a POC table. Right now, like in location_history, we recommend new tables in CDM WG based on expert opinion - and not a POC. If we take a POC approach, then one requirement before approving a new table (especially that related two facts together) is a POC. using the generic fact_relationship table, we could POC three new tables that follow proper RDBMS referential integrity - all derived from fact_relationship table. i.e. instead of location_history (that has location_id with referential integrity, but entity_id has no referential integrity), we could create person_location_history, provider_location_history, care_site_location_history each with RDBMS referential integrity - test it out in POC, build software in dev brach, validate it: and then ask the community to ratify validated tables person_location_history, provider_location_history etc. Plus these tables will be smaller in size and not go to billions of rows (i.e. performance) In short - i like @pavgra comment of using fact_relationship as the POC. |
Gowtham, that would be so great! |
@gowthamrao @pavgra Point being, if we keep going down this path we're going to end up with an excessive number of tables. And if we want to be consistent then we need to remove similar approaches in other tables. OBSERVATION_EVENT_ID and OBS_EVENT_FIELD_ID for example, would then require additional tables: Is it worth it? |
@rtmill , for me it sounds that you go to extremum, why don't you want to end up then with a single table with three field - "subject", "relationship", "object" (which is RDF)? It would cover the whole CDM. Although I'd like to have a relations table per each domain pair, I have to admit that it will expand CDM too much and too fast for regular business users. So, I just stated that the hybrid approach, where subject is typified (e.g. |
I think this is such an important structural issue that it deserves a wider community conversation. I like the balance @pavgra proposes and agree we don't want fact_relationship to ingest and annotate the rest of the CDM. But I also don't know the answer to the question implicit in @rtmill 's comments: how much expansion can the CDM's primary relational structure support? And that seems to be an increasingly pressing issue. |
Welcome to OHDSI! Maybe you already have, if not, could you please introduce yourself here |
AEW0330 is my (Andrew Williams) github ID. You know me :) |
I like @pavgra description of FACT_RELATIONSHIP as a POC for adding foreign key relationships. In that vein, what is the recommend/conventional relationship to use in FACT_RELATIONSHIP to represent a 'has many/belongs to' relationship? |
Friends: Two things:
|
Where are with this? |
Agree with @dimshitc - @gowthamrao, @cgreich is this proposal ready to discuss? |
Yes, Ma'am. |
We decided to introduce concept_id's for each field in OMOP cdm. e.g. we will be assigning a concept_id for person.person_id, visit_occurrence.visit_occurrence_id etc. To be released in future version of vocabulary. e.g. cost.cost_event_field_concept_id needs concept_id for each identify of the field.
Proposal 1: This proposal is to change FACT_RELATIONSHIP table's domain_concept_id to field_concept_id, where field_concept_id is the identity of the field in omop cdm.
domain_concept_id_1YesintegerThe concept representing the domain of fact one, from which the corresponding table can be inferred.domain_concept_id_2YesintegerThe concept representing the domain of fact two, from which the corresponding table can be inferred.Proposal 1: will help overcome domain ambiguity such as does the domain_id = 'Visit' represent visit_occurrence or visit_detail.
The text was updated successfully, but these errors were encountered: