Cycle Count of Physical Assets with Many-to-Many Table Relationships

Support, Troubleshooting, & Help
1

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”

For example:

  • 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.

2

Hey James. Thanks for breaking this down so neatly.

Tulip analytics has a pretty significant limitation currently-- reporting can only be done on one Tulip table. Linked records will not do anything to mitigate this. Depending on your reporting needs, you may need to find a way to have all of these entities tracked in one table with many columns. Or to have specific values stored in multiple tables to be used in analyses on either artifact. There is actually some precedent here, in the data analytics space people call this the “One Big Table” approach. Since it seems like all your N to 1 relationships have a fixed quantity for N, this should be possible.

Alternatively, if you will eventually extract these tables out of Tulip into a data warehouse to be used by central BI teams, then this note becomes irrelevant and you can think of a more normalized approach. Just inside of Tulip, this is important to keep in mind for table architecture.

Curious about what others think, though. Just my 2 cents!