Friends:
I’d like to announce the OMOP Common Data Model (CDM) Entity-Relationship Diagram (ERD) Challenge! Starting today, all members of the OHDSI community are welcome and encouraged to submit their entries of the best ERD for the OMOP CDM to this forum post (or to the CDM Workgroup teams site) by Tuesday, Dec 13. One winner will be selected by a committee from the CDM workgroup, and announced on OHDSI’s last community call of the year on Dec20, with their award-winning ERD being a gift to our entire community, posted on the official OMOP CDM git page, but also the winner receiving a special gift from the OHDSI community! This should be a fun activity for our community, particularly those of you helping drive our open community data standards, to learn and collaborate and contribute to a community resource that all of us can benefit in. So, please accept the OMOP CDM ERD Challenge and get diagraming!
Background:
The OMOP Common Data Model v5.4 serves our community well as an open community data standard to enable standardized analytics and large-scale evidence generation. The CDM Workgroup, under the amazing leadership of @clairblacketer , has done a tremendous job of stewarding this standard, improving our documentations and conventions, and providing reference implementations, all available here at: index.knit. Our primary schematic that we use to describe the OMOP CDM is below:
However, there’s one thing we DON’T have on our CDM Git repo, and that’s one standard entity-relationship diagram. So, if someone wants to learn about the tables, fields, data types, and PK/FK relationships, they have to read the specification one table at a time, or roll their own ER diagram after instantiating the CDM in their local environment.
Off and on, this topic has come up on the forums for discussion. For example, see these threads here and here. I’m sure that @DTorok , @MPhilofsky , @MaximMoinat , @roger.carlson @rtmill @gregk @lee_evans @cukarthik @mdewilde @Chungsoo_Kim @mvanzandt , and many others have been thinking hard on this problem in their travels through many ETLs.
@Overhage and I were chatting, and thought it would be really great if our community had one kickass ER diagram that could be printed out in large-scale (think like a 4-foot by 5-foot poster) so that someone could both take in CDM as a whole (even as a fun promotional banner) but could also ‘zoom in’ to learn about the details in a meaningful way.
Now, there are several tools (including free and open-source versions) that can create an ER diagram, I’m guessing in our community, several folks have a personal favorite, but most can get you started quite easily. A great ER diagram requires a little science and a lot of art. An ERD conveys information about the structure and content, not just by presenting the tables and fields in the database schema, but also by organizing the tables and relations in an aesthetically pleasing way so that the diagram can be easily followed and similar entities are logically arranged around each other (while also reducing the number of criss-crossing relations and avoiding entity-relationship overlaps). For example, in our cartoon schematic above, we put the PERSON table in the top left because all clinical event data flows logically from that patient, the vocabulary tables in the middle because that’s the heart of the OMOP CDM, and tried to partition out clinical tables, from health system and health economic entities, derived elements, and meta-data. This cartoon was designed to provide a readable presentation on a Powerpoint slide to give an audience of gist of the OMOP CDM. But our ERD diagram will provide a much greater level of detail, and is not bounded by the Powerpoint slide constraint. It should be able to show some/all fields in the tables, possibly data types for each field, fields that serve as keys, relations to other table/fields with cardinality, etc. There can be some artistic license taken, for example, it is probably not absolutely essential that every _CONCEPT_ID field in every table have a line to the CONCEPT table, otherwise, you’ll probably end up with an intractable spaghetti bowl:)
Instructions
We encourage any and all OHDSI collaborators to submit an entry to the OMOP CDM ERD challenge. All you have to do is create an ERD, and post the file either here on the Forums as a reply to this discussion thread or within the OHDSI Teams environment under the CDM Workgroup team (we provide this flexibility because not all filetypes can be posted here on the forums, but any file can be uploaded into MSTeams). At this point, we won’t specify a particular dimension requirement, but just require that the diagram can be viewable in some common format (e.g. can I open it as pdf and zoom in/out or pan around it).
In your post, it’d be helpful if you share how you created your ER diagram (e.g. what tools did you use?) and if applicable share the editable version so others can build off your work. We encourage people to share and borrow good ideas, and build off of each other’s submissions, but please be inclusive in attributions. Our ultimate goal is to land on one ERD that we can all benefit from as a community.
After Dec13, the submissions will be judged by a special CDM Review Committee that @clairblacketer will chair. Submissions will be reviewed for accuracy (do they correctly convey the content of OMOP CDM v5.4?) and aesthetics. The winner will be announced on the Tuesday, Dec20 OHDSI Community Call (a special gift for our holiday-themed event!).
Any further questions, just reply to this thread and I’ll be happy to answer.
Let the OMOP CDM ER Diagram Challenge Begin! 
. I am all ears to your feedback and thoughts
Key icon beside the Table column Field indicates Primary key.