New database schema design

[ecarrenolozano]

Description

As part of the new strategy to enrich the Omnipath database with more data and enhance design and performance, we are going to discuss about the new database schema.

Current status:

Omnipath is composed by five different (but related) tables:

• interactions
• enzymePTM
• annotations
• complexes
• intercell

The detail of each table can be found in the data dictionary. This document describe the columns in each table.

ER Diagram:

Important links:

• A detailed document has been started by @jonsch1. Here you can find the document.
• Repository: https://github.com/saezlab/omnipath-new-schema-wip
• Logic and Physical diagrams:
  • Check omnipath channel slack;
  • [draw.io as sandbox] (https://drive.google.com/file/d/1nMs3VRK8JolHwOS92GP1WQJjB1963Ve6/view?usp=sharing)

[jonsch1]

Here you can see the latest schema iteration: https://drive.google.com/file/d/1KFqoGHXFGwLENJ-0X7Z1Gv775U7ngUTH/view?usp=sharing

[ecarrenolozano]

It is currently not possible to open the file. In the meantime, I would like to suggest the following:
Convention for naming fields:

• Primary Keys: Always use the word id.
• Foreign Keys: Based on an example — if the table to reference is entity and the foreign key refers to source — the naming convention should be entity_source_id. This way, by simply examining the field name, it is clear that it is a foreign key pointing to the entity table.
• Please feel free to edit this diagram as much as needed. I am currently building the initial model for the Omnipath database, but since you are more familiar with the data, your input can help us significantly improve it.
• Once we finalize a robust model, we as a team can move forward using pgModeler. This tool will allow us to generate the diagrams, data dictionaries, and the SQL code to create the tables.

@deeenes @npalacioescat Does it make sense for your and your users have separate tables for the enzyme PTM and Protein Protein Interactions?

[ecarrenolozano]

Take a look to this initiative for handling metadata for datasets: https://github.com/mlcommons/croissant

We can check if using this makes sense to our goal of creating a single loader using yaml files for each source.