search

specify / specify7

Specify 7
https://www.specifysoftware.org/products/specify-7/
GNU General Public License v2.0
60 stars 36 forks source link

Add Collection Object Groups to Specify 7 #1150

Open grantfitzsimmons opened 2 years ago

grantfitzsimmons commented 2 years ago

This issue captures the major requirements of Collection Object Groups in Specify 7.

Vocabulary

Collection Object Group (COG): Two or more Collection Objects that were physically joined in the field and/or they were physically joined in a collection as the result of a preservation or storage method.

Collection Object Group Type (COGT): The type of COG, can be user-defined but have 3 major 'types' that may dictate business logic or behavior.

Data Model

Initially introduced in https://github.com/specify/specify7/pull/5032, needs to be re-evaluated in case of discrepencies.

Data Model

image 
-- collectionobjectgroup definition

CREATE TABLE `collectionobjectgroup` (
  `collectionobjectgroupid` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) DEFAULT NULL,
  `Description` longtext DEFAULT NULL,
  `IGSN` varchar(255) DEFAULT NULL,
  `GUID` varchar(255) DEFAULT NULL,
  `Integer1` int(11) DEFAULT NULL,
  `Integer2` int(11) DEFAULT NULL,
  `Integer3` int(11) DEFAULT NULL,
  `Decimal1` decimal(22,10) DEFAULT NULL,
  `Decimal2` decimal(22,10) DEFAULT NULL,
  `Decimal3` decimal(22,10) DEFAULT NULL,
  `Text1` longtext DEFAULT NULL,
  `Text2` longtext DEFAULT NULL,
  `Text3` longtext DEFAULT NULL,
  `YesNo1` tinyint(1) DEFAULT NULL,
  `YesNo2` tinyint(1) DEFAULT NULL,
  `YesNo3` tinyint(1) DEFAULT NULL,
  `Version` int(11) DEFAULT NULL,
  `TimestampCreated` datetime(6) NOT NULL,
  `TimestampModified` datetime(6) DEFAULT NULL,
  `CollectionID` int(11) NOT NULL,
  `COGTypeID` int(11) NOT NULL,
  `CreatedByAgentID` int(11) DEFAULT NULL,
  `ModifiedByAgentID` int(11) DEFAULT NULL,
  PRIMARY KEY (`collectionobjectgroupid`),
  KEY `collectionobjectgrou_CollectionID_205bd6ad_fk_collectio` (`CollectionID`),
  KEY `collectionobjectgrou_COGTypeID_63c53767_fk_collectio` (`COGTypeID`),
  KEY `collectionobjectgroup_CreatedByAgentID_ea3694b4_fk_agent_agentid` (`CreatedByAgentID`),
  KEY `collectionobjectgrou_ModifiedByAgentID_3252d64e_fk_agent_age` (`ModifiedByAgentID`),
  CONSTRAINT `collectionobjectgrou_COGTypeID_63c53767_fk_collectio` FOREIGN KEY (`COGTypeID`) REFERENCES `collectionobjectgrouptype` (`COGTypeID`),
  CONSTRAINT `collectionobjectgrou_CollectionID_205bd6ad_fk_collectio` FOREIGN KEY (`CollectionID`) REFERENCES `collection` (`UserGroupScopeId`),
  CONSTRAINT `collectionobjectgrou_ModifiedByAgentID_3252d64e_fk_agent_age` FOREIGN KEY (`ModifiedByAgentID`) REFERENCES `agent` (`AgentID`),
  CONSTRAINT `collectionobjectgroup_CreatedByAgentID_ea3694b4_fk_agent_agentid` FOREIGN KEY (`CreatedByAgentID`) REFERENCES `agent` (`AgentID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- collectionobjectgroupjoin definition

CREATE TABLE `collectionobjectgroupjoin` (
  `collectionobjectgroupjoinid` int(11) NOT NULL AUTO_INCREMENT,
  `IsPrimary` tinyint(1) DEFAULT NULL,
  `IsSubstrate` tinyint(1) DEFAULT NULL,
  `Precedence` smallint(6) DEFAULT NULL,
  `Version` int(11) DEFAULT NULL,
  `TimestampCreated` datetime(6) NOT NULL,
  `TimestampModified` datetime(6) DEFAULT NULL,
  `Text1` longtext DEFAULT NULL,
  `Text2` longtext DEFAULT NULL,
  `Text3` longtext DEFAULT NULL,
  `Integer1` int(11) DEFAULT NULL,
  `Integer2` int(11) DEFAULT NULL,
  `Integer3` int(11) DEFAULT NULL,
  `YesNo1` tinyint(1) DEFAULT NULL,
  `YesNo2` tinyint(1) DEFAULT NULL,
  `YesNo3` tinyint(1) DEFAULT NULL,
  `ParentCOGID` int(11) NOT NULL,
  `ChildCOGID` int(11) DEFAULT NULL,
  `ChildCOID` int(11) DEFAULT NULL,
  PRIMARY KEY (`collectionobjectgroupjoinid`),
  UNIQUE KEY `ChildCOGID` (`ChildCOGID`),
  UNIQUE KEY `ChildCOID` (`ChildCOID`),
  KEY `collectionobjectgrou_ParentCOGID_5cd68082_fk_collectio` (`ParentCOGID`),
  CONSTRAINT `collectionobjectgrou_ChildCOGID_61365f53_fk_collectio` FOREIGN KEY (`ChildCOGID`) REFERENCES `collectionobjectgroup` (`collectionobjectgroupid`),
  CONSTRAINT `collectionobjectgrou_ChildCOID_7fee35cc_fk_collectio` FOREIGN KEY (`ChildCOID`) REFERENCES `collectionobject` (`CollectionObjectID`),
  CONSTRAINT `collectionobjectgrou_ParentCOGID_5cd68082_fk_collectio` FOREIGN KEY (`ParentCOGID`) REFERENCES `collectionobjectgroup` (`collectionobjectgroupid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- collectionobjectgrouptype definition

CREATE TABLE `collectionobjectgrouptype` (
  `COGTypeID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(255) NOT NULL,
  `Type` varchar(255) NOT NULL,
  `Version` int(11) DEFAULT NULL,
  `TimestampCreated` datetime(6) NOT NULL,
  `TimestampModified` datetime(6) DEFAULT NULL,
  `CollectionID` int(11) NOT NULL,
  `CreatedByAgentID` int(11) DEFAULT NULL,
  `ModifiedByAgentID` int(11) DEFAULT NULL,
  PRIMARY KEY (`COGTypeID`),
  KEY `collectionobjectgrou_CollectionID_10280c4e_fk_collectio` (`CollectionID`),
  KEY `collectionobjectgrou_CreatedByAgentID_3cd7d979_fk_agent_age` (`CreatedByAgentID`),
  KEY `collectionobjectgrou_ModifiedByAgentID_bf1afa1b_fk_agent_age` (`ModifiedByAgentID`),
  CONSTRAINT `collectionobjectgrou_CollectionID_10280c4e_fk_collectio` FOREIGN KEY (`CollectionID`) REFERENCES `collection` (`UserGroupScopeId`),
  CONSTRAINT `collectionobjectgrou_CreatedByAgentID_3cd7d979_fk_agent_age` FOREIGN KEY (`CreatedByAgentID`) REFERENCES `agent` (`AgentID`),
  CONSTRAINT `collectionobjectgrou_ModifiedByAgentID_bf1afa1b_fk_agent_age` FOREIGN KEY (`ModifiedByAgentID`) REFERENCES `agent` (`AgentID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

COG Functional Requirements

COG User Interface Requirements

maxpatiiuk commented 1 year ago

Relevant document: https://discourse.specifysoftware.org/t/containers-in-specify-7/578

grantfitzsimmons commented 1 year ago

The University of Michigan is looking for something similar now.

grantfitzsimmons commented 1 year ago

This comes up all the time. I can't believe there are not many comments here. This should be resolved as soon as we have the bandwidth.