Add role to instrumentScientist

[antolinos]

An instrument Scientist associates a scientist with an instrument. However, in the real life, scientists might play a different role in a beamline: beamline manager, staff, Phd, technician, collaborator, etc...
From the software point of view these different cases might need to be be managed differently.

Currently, there are the fields of InstrumentScientists:

Field	Type	Description
modId	String
createId	String
createTime	Date
modTime	Date

I would propose to add "role":

Field	Type	Description
modId	String
createId	String
createTime	Date
role	String	A role showing the position of the members of the group with respect to the instrument

[RKrahl]

Do you envision that the same scientist may have multiple roles with respect to an instrument? In that case, you'd also need to add role to the uniqueness constraint. The downside of that would be that role would need to be NOT NULL, which is inconvenient for the facilities that don't want to use that attribute.

[antolinos]

It is a fair remark. I don't like the downside and I would not like to force the use of role.
I don't have any case in mind where a person has to roles but I am pretty sure it might exist. I wonder if for these cases, that might never exist, we can make a composition of roles.
It means that if person P has the role "BeamlineManager" and "Safety" then a new role can be created that is called "BeamlineManager Safety"

In any case, my opinion is that having a single role per instrument already improves the current situation.

[RKrahl]

Technically speaking, such a hypothetical combination of roles by a (space or comma or whatever separated) list is obviously possible as the role attribute is free text. The only question is if the clients that want to make use of the attribute are able to deal with that. They would also need to take this into account on searching, e.g. search for SELECT sc FROM InstrumentScientist sc WHERE sc.role LIKE '%Safety%' rather then SELECT sc FROM InstrumentScientist sc WHERE sc.role = 'Safety'

Anyway, if you believe that such multiple roles are not likely to occur in practice and are happy to accept the restriction to exclude them, it's probably better to leave role as an optional field.

[antolinos]

You are right, but my example had a different meaning. "BeamlineManager Safety" is a role itself that the client will need to manage somehow.
Let's say that person P has role R1 and R2. As I can not have two roles then I will create the role R3 that in my business layer might be treated as it is R1 + R2.
By other hand, I don't like that the other roles used in ICAT are treated differently, e.g: (Not NULL)

[dfq16044]

If your role is a "fixed" string, then I will suggest to use a separate role table for normalization. For example, if a bug is introduced in your software then the role will not be correct as this is a free text field. You can define the values in a role table and then linked it to InstrumentScientist.
I think the same about the role column in InvestigationUser table.