Please provide ER diagram for database

[georgemarselis-nvi]

Describe your idea for a new feature

We need to automate some things for integration with our LIMS. Could we ask you to please provide an ER diagram?

Describe how you would access this feature?

Integration with our LIMS system.

[apetkau]

So we do have some information on our data model at https://phac-nml.github.io/irida-documentation/developer/data-model/#background but that's many years out-of-date right now.

The best way to get an up-to-date ER diagram (that shows all tables) is to generate it from the relational database schema. I've done this with IRIDA 21.09.2 and you can view it below.

• irida-21.09.2.png
• irida-21.09.2.svg.zip

This is quite large though (I had to export as a PNG and SVG since the PDF file was getting truncated).

If you want to export it yourself I used https://github.com/Alexis-benoist/eralchemy:

# Install eralchemy
conda create --name eralchemy eralchemy pymysql
conda activate eralchemy

# Export ER diagram
eralchemy -i 'mysql+pymysql://username:password@localhost/irida_test' -o irida-21.09.2.png

Where mysql+pymysql is the SQLAlchemy connection string to your database.

I think you can also do this in MySQL workbench too.

[apetkau]

For a bit more information:

• The DATABASECHANGELOG* tables are used by liquibase for version management of our database (it will keep track of which version of the IRIDA database schema is being used/handle database migrations)
• The *_AUD tables are used by Hibernate envers for auditing database changes. That is the *_AUD tables look like tables in the main IRIDA data model (e.g., sample and sample_AUD) and have a new row inserted whenever a change is made to any object in IRIDA which records what the change was (creation, update, deletion), who made the change, and when.
• The rest of the tables are all part of the main IRIDA data model and store information like users, samples, paths to files, remote connections, etc.