Protein table: new columns for description, required containment level, hazard group

[KarlLevik]

We need a free-text field for users to describe the protein, at least 1000 characters. Additionally, we need columns for the required containment level (CL1, CL2, CL3, ...) and the hazard group. This info can then be displayed in the application so that users are properly warned when they're working with dangerous proteins/viruses/substances.

There is already a safetyLevel column in the Protein table. The safetyLevel column is an enum with three possible values: GREEN, YELLOW and RED. At Diamond, we plan to use safetyLevel to hold the Diamond risk rating.

I'll get back with the ALTER TABLE statement later.

[KarlLevik]

Here we go - does the column comments look right?

ALTER TABLE Protein
    ADD `description` TEXT NULL DEFAULT NULL 
        COMMENT 'A description/summary using words and sentences' AFTER `acronym`,
    ADD `hazardGroup` tinyint unsigned DEFAULT 1 NOT NULL 
        COMMENT 'A.k.a. risk group' AFTER `description`,
    ADD `containmentLevel` tinyint unsigned DEFAULT 1 NOT NULL
        COMMENT 'A.k.a. biosafety level, which indicates the level of containment required' AFTER `hazardGroup`;

Can anyone explain the difference between hazard group and containment level? I know that containment level describes the level of containment required whereas hazard group indicates how dangerous the material is. My understanding is that there's usually a 1-to-1 mapping between the two, but that this isn't always the case. I'm just not sure under what circumstances it's different.

Edit: OK, I've been told (thank you, Katherine!) that after risk assessment the SHE group might decide that a higher containment level is required than the one indicated by the hazard group. This is rare, but possible.

[KarlLevik]

ISPyB dev. collaboration meeting 2020-09-09: We agreed that I could go ahead and make a pull request for this. I should probably clarify that we're using tinyint instead of enum because CL1, CL2, CL3 and hazard group names are not universal across the globe.

[KarlLevik]

PR accepted.