There are two cases here, and they have different answers.
The simple case already works
Several contacts can map to the same organization and the same role. The contacts primary key is contactID on its own; nothing requires organizationID and role to be unique together, and role is free text. You can add a second row.
The more complex case needs a new contactRelationships table
This sounds like your situation.
contactID is a single-valued foreign key in seven tables: sites, samples, measures, measureSets, protocols, protocolSteps and polygons. The datasets table carries custodyCont for the same purpose.
Each gives one slot. Two people jointly responsible for a site cannot both be recorded against that site.
We didn’t originally create this table because we didn’t have a request for it. Responding to requests has been important to the ODM since day one.
Proposal: a new contactRelationships table
I propose we add a contactRelationships table, following what the ODM already uses for three many-to-many relations. All nine possible targets are included rather than a subset.
| Order | partID | Key | Required | dataType | Max | Target table |
|---|---|---|---|---|---|---|
| 1 | contactRelationshipsID |
pK | mandatory | varchar | 50 | — |
| 2 | contactID |
fK | mandatory | varchar | 30 | contacts |
| 3 | relationshipID |
fK | mandatory | varchar | 30 | role, from contactRelSet |
| 4 | organizationID |
fK | mandatoryIf | varchar | 30 | organizations |
| 5 | siteID |
fK | mandatoryIf | varchar | 30 | sites |
| 6 | datasetID |
fK | mandatoryIf | varchar | 30 | datasets |
| 7 | sampleID |
fK | mandatoryIf | varchar | 30 | samples |
| 8 | measureRepID |
fK | mandatoryIf | varchar | 30 | measures |
| 9 | measureSetRepID |
fK | mandatoryIf | varchar | 30 | measureSets |
| 10 | protocolID |
fK | mandatoryIf | varchar | 30 | protocols |
| 11 | stepID |
fK | mandatoryIf | varchar | 30 | protocolSteps |
| 12 | polygonID |
fK | mandatoryIf | varchar | 30 | polygons |
| 13 | lastEdited |
header | optional | datetime | — | |
| 14 | notes |
header | optional | varchar | — |
Fourteen columns. Exactly one of rows 4 to 12 is populated. A row reads as [contactID] is the [relationshipID] for [the populated target], the same subject-relationship-object form the other relationship tables use.
Proposed roles
Eight values for contactRelSet, following how sampleRelSet and polyRelSet work, with relationshipID as the shared column.
| partID | Label |
|---|---|
dataCustodian |
Data custodian |
dataSubmitter |
Data submitter |
labContact |
Laboratory contact |
techContact |
Technical contact |
adminContact |
Administrative contact |
pi |
Principal investigator |
siteOperator |
Site operator |
contactNR |
Contact role not reported |
contactNR follows the existing relNR convention. This is a proposal, and the list is most likely wrong. Feedback on which are missing and which are unnecessary is the specific thing being asked for.
The design questions
Should all nine target tables be included? A partial table would need extending the first time someone needs a target that was left out. Nine nullable foreign keys are not elegant, but they are honest and clear about what the model already links contacts to.
Should the existing contacts columns stay? Yes. role and organizationID remain the simple implementation and will serve almost everyone. The relationship table is for the cases that need more than one slot. Nothing is deprecated and nothing breaks, so a user who never adopts the table is unaffected.
Are these the right roles? The part most likely to be wrong. Which are missing, and which would you never use?
What we decided now, and what is deferred
Decided now: whether to add the table, and its structure — columns, targets, role values. A design decision; deciding on new tables and their structure can happen at any time.
Deferred to the 3.1 staging: validation rules, full documentation, the ERD update, template changes and generator support. Batched with whatever else is queued when 3.1 is formally staged.
That split is the general shape of the maintenance phase, recognizing that Shifting PHES-ODM Activity Levels did not set out what our maintenance phase covers. Design work stays open because it is cheap and community-driven. Implementation batches into release 3.1.
Validator behaviour
Just a reminder: you can implement the table locally in the meantime, and it will not break your validation runs. The validator iterates over the tables present in the data, and Cerberus runs with allow_unknown = True, so a table absent from the schema passes through untouched.
Why create a new relationships table?
The model solves many-to-many this way already, in sampleRelationships, polygonRelationships and protocolRelationships. All three share a synthetic primary key, a subject foreign key, a relationshipID, an object foreign key, then lastEdited and notes. Reusing that existing approach adds a table without adding a concept, and the generators, templates and ERD treat it like any other table.
A polymorphic entityType plus entityID pair would be two columns instead of nine. It was rejected because it cannot be expressed as a real foreign key, and several people in this community load the ODM into relational databases where that matters.
Lists as input values
The ODM has never accepted a delimited list inside a cell, and should not start. That position also answers topic 181, Martin Wellman’s July 2025 question about mapping a multivalued source slot to a single-valued ODM slot, which has been unanswered for 14 months.
Martin offered three options: take the first value, take the last value, or expand into multiple rows. The first two lose data. The third is right, and the objection he raised against it is the argument for this proposal. He noted that when a row is duplicated to hold several values, other tables referencing its primary key will only ever point at the first copy. A relationship table is how that is fixed: the multiplicity moves into rows of a table built to hold it, instead of into duplicated rows of a table that was not.
So the two threads have one answer. More rows, in a table designed for them. Never a list in a cell.