Ownership/accessorship dependent on a column value (sample application: HotCRP)

[benkilimnik]

PaperConflict encodes relationships between contactInfo and paperId based on the value of conflictType (e.g. co-author or institutional relationship). The data ownership/accessorship pattern may vary depending on this relationship. For instance, on GDPR GET, we would want to extract all papers that a contactInfo (data subject) has co-authored, but not papers that they might have an institutional conflict with.

CREATE TABLE PaperConflict (
  id int PRIMARY KEY NOT NULL,
  paperId int NOT NULL,
  contactId int NOT NULL,
  conflictType int NOT NULL,
  FOREIGN KEY (paperId) REFERENCES Paper(paperId)
  -- ownership dependent on another column (conflictType) 
  -- FOREIGN KEY (contactId) REFERENCES ContactInfo(contactId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

[KinanBab]

This is good to discuss in a WIKI about normalization / modifying DB schema.

Another relevant scenario is ownCloud pre-normalization: share_type and share_with (which can be a group or user)