Trying to figure out how to structure an application that will associate a Surgical Kit “reservation ID” (these are one ID to one physical asset) with a corresponding financial audit “asset ID” (which can actually be one-to-many or many-to-one with the physical assets. The inventory counting will leverage a processing datetime associated with the “reservation ID”, but needs to be reported to Finance using the “asset ID”
- Reservation ID “RID000” consists of one surgical tray with one Asset ID “AID000” - the datetime associated with counting RID000 should be attributed to AID000, as they are one-to-one
- Reservation ID “RID001” consists of one surgical tray with Asset ID “AID001” and contains a subcomponent screw caddy that has its own Asset ID “AID002” - the datetime associated with counting RID001 should be attributed to both AID001 and AID002 as they are counted together.
- Reservation IDs “RID003” and “RID004” each consist of one surgical tray (they complement each other), but both are associated with one Asset ID “AID003” - both RID003 and RID004 would need to be counted (with datetime recorded) in order for AID003 to be considered counted
Any suggestions on table structure/architecture and linking are appreciated.