field nametyperequireddescriptionexamplesnotes
idinteger(tick)primary key, internal to DB only (not a foreign key)1auto-incrementing
source_systemstring(tick)name of source system in which biomaterial's primary data is stored"Labmatrix", "Lab Samples", "Staudt"we will then need to come up with a mapping for all the non-central systems, and enforce them using that name when uploading
source_idstring(tick)ID of biomaterial within the source system(UUID), "AZD3-PL-0024-002" 
patient_idstring ID of source patient from whom biomaterial was collected"1234567", "SS08-145" 
patient_id_sourcestring source of the patient ID"CRIS", "SoftPath" 
collected_datetimedatetime(tick)date & time the specimen was collected any vagaries in data (e.g., estimate, or date-only) are handled in source system, rather than here
sample_typestring(tick)the type of sample"blood", "serum", "prostate", "skin"provide a catch-all "unknown" option?
statusstring(tick)the current status of the sample"in inventory", "transferred", "exhausted", "destroyed"if required, we will need a generic "unknown" option
status_datetimedatetime(tick)date & time of the currently-recorded status  
createddatetime(tick)date/time the record was created  
created_bystring(tick)username of creator  
last_modifieddatetime date/time the record was last modified  
last_modified_bystring username of modifier  


  1. Do we need to track specific dates/times for specific status changes? (for example, track the date/time a specimen changed from "in inventory" to "exhausted", so that a report can be generated which specifically counts the number of specimens that were exhausted in a given time period)
  2. Are we OK that any automated pushes of data into the system will then have the service-level username as the created_by/last_modified_by username?
  3. Do we want to maintain a separate table of all the systems that can be represented by "source_system"? The table might contain a bit of metadata about the system (owner, tech lead, etc.), for purposes of reporting...
  4. We might consider making it absolutely clear that this system isn't intended to be the primary system for any lab or group — they can't expect that the primary record of whether a sample is in inventory or destroyed would be in this system. (Or would we ever consider that, vs. having these groups use a very simplified interface to Labmatrix?)