Constraints for table `concept_relationship` (primary key and maybe coherence of start and end dates)

Open
  • Is something wrong?
#771 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty
5/5
Estimated time
Over a week
Newbie friendliness
25/100
Issue type
Feature
Clarity
Needs clarification
Activity status
Stale
Domain
database

Research direction

Start by reviewing the CSV definitions for the concept_relationship table and the related documentation, focusing on primary-key and valid_end_date requirements. Resolve the listed relationship, date-range, and invalidation questions with the CDM rules; done means the constraints and documentation clearly match the agreed semantics.

Written by the indexing model from the issue text.

Description

The CDM definitions of fields for the table concept_relationship (in the CSV files) are likely missing useful constraints:

There is no primary key, which is otherwise recommended to help with data integrity. A composite primary key could be the easiest and least disruptive way to address this, even though if would not completely ensure the consistency/coherence of data in that table (more on this in points after this paragraph). I did not find the answer to the following questions in the documentation:

  • Can different relationship_ids exist between a given pair of concept IDs in concept_id_1 and concept_id_2?
  • Can a given pair of concept IDs in concept_id_1 and concept_id_2 exist during different periods of time defined by valid_start_date and valid_end_date?
  • Can a given pair of concept IDs in concept_id_1 and concept_id_2 (and with given relationship_id, valid_start_date, and valid_end_date depending on the answers to the question above) be invalidated for different reasons?

If the answer to all three questions is "No", a composite primary key could be concept_id_1 and concept_id_2. If some answers are "Yes", the composite key would need to add columns accordingly.

Also, valid_end_date is required (to have a value), and is meant to capture "the date when the relationship is invalidated.". Shouldn't valid_end_date be non-required (nullable?). Otherwise how are concept_relationship that are still active (not invalidated) recorded?

Dominant language
HTML
Stars
1.1k
Forks
513
Avg merge
8d 3h
Merged PRs (30d)
4

Getting set up

This project ships no dev container, Dockerfile or contributing guide, so setting up is up to you: start from its README, and see our first-contribution guide for the general steps.

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from OHDSI/CommonDataModel

All issues in OHDSI/CommonDataModel

Similar issues

More Databases issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.