问题描述:

A Patient Care Report PCR must capture procedures performed on a given patient. In the system this relates to there are 60 procedures types. Each procedure has the following data elements in common (though not all procedures have all of these elements rather most have a subset):

  • E19_01 Date/Time Procedure Performed Successfully
  • E19_02 Procedure Performed Prior to this Units EMS Care
  • E19_03 Procedure Code
  • E19_04 Size of Procedure Equipment
  • E19_05 Number of Procedure Attempts
  • E19_06 Procedure Successful
  • E19_07 Procedure Complication
  • E19_08 Response to Procedure
  • E19_09 Procedure Crew Members ID
  • E19_SBC_09 Procedure Performed By
  • E19_10 Procedure Authorization
  • E19_11 Procedure Authorizing Physician
  • E19_12 Successful IV Site
  • E19_13 Tube Confirmation
  • E19_14 Destination Confirmation of Tube Placement

Each of the above listed elements may hold different data depending on the type of procedure. In addition each procedure type may have additional elements unique to that procedure, or unique to a subset of procedures. Note: Elements E19_07, E19_12, and E19_13 are multiple choice data entry so will break out into separate tables. Some of the unique elements may also be multiple choice data entry, so will need separate tables or allowed as NULL.

The system will receive about 100 PCRs per day with each PCR having data entered on several to a dozen or more procedures that were performed.

It seems to make sense to have a table that holds PCR ID and procedure Code/table (call it ProceduresPerPCR, and a table for each procedure (and additional tables for elements that are multiple choice data entry). A typical query would then get the procedures performed given a particular PCR ID in two steps. Step 1, retrieve from ProceduresPerPCR the procedures per a given PCR ID, and step 2, query each table for the procedure specific element data. This seems preferable to having one large table holding all the procedure data and having lots of columns allowing NULL.

Interested in getting some feedback, before proceeding.

相关阅读:
Top